I'm trying to create a userform that allows the user to swap sheets from visible to hidden status. I've made it to the point where all the sheet names are populated based on their current status in their respective listbox, but I am stuck on using the results of any swaps to newly set their visible property. the code I have in the userform is as follows:
View 2 RepliesI am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.
View 7 Replies View Relatedi am using the macro below to get excel to hide all sheets but the one called "open"...
PHP
Sub savemini()
ActiveWorkbook.Unprotect "letmein"
For Each sh In ThisWorkbook.Worksheets
If Not sh.Name = "open" Then sh.Visible = xlVeryHidden
Next sh
ActiveWorkbook.Save
End Sub
So the workbook is protected with the password "letmein", and the VB code above in theory should unlock the workbook, allowing all visible sheets to be hidden with the exception of the sheet called "open". Then is saves the file.
Now heres the odd bit... it works for certain people, but not for others. I have made sure those it wont work for are not doing anything weird and they are not.
The workbook i am using has a code that only opens certain sheets for certain users. I as a master user have access to all sheets. I can go into the users sheets and click the button that activates the macro above myself and it works fine, but for some users it wont work.
I'm having a problem moving the cell cursor 2 rows down once I find my LastRow (see LastRow code below). My problem is I need to move down 2 VISIBLE Rows down and I'm running into it moving 2 Rows down whether they are Visible or Hidden? If Row 50 is my LastRow, and I have it move 2 rows down, if Row 52 is Hidden, it still moves to Row 52 which can't be seen by the user after I do my stuff to that row? I can't unhide any hidden rows for other reasons. Is there a way to have the cursor move down 2 VISIBLE Rows instead of 2 Rows regardless?
The code I use to select the LastRow is:
LastRow = Cells(4000, 8).End(xlUp).Row
I move down 2 more rows using:
Cells(LastRow + 2, 8).Select 'Then I do some stuff here for the user, so it can't be a hidden row
I was working on a multi-tab spreadsheet (7 tabs to be exact) and I went to hide one of the tabs and all of a sudden the worksheet went away and I was left with my empty personal.xls spreadsheet. If I go into tools and visual basic I see my spreadsheet listed as VBAProject (Durex Sales Oct 1 to Oct 28 06.xls) with the 7 sheets listed below it within a folder called Microsoft Excel Objects, but I can't get it back in spreadsheet form.
View 5 Replies View RelatedI would like to toggle (button) between either hidden menus or visible menus. I have the following code:
Private Sub ToggleButton1_Click()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[Code] .......
Will not work. either one state or the other is visible.
What command I would use to change the text box border color, and set text border to visible or hidden?
View 3 Replies View RelatedI was using the following scripts to fill a listbox in a userform.
Code:
ListBox1.List = Sheets("Working").Range("A1:C20").Value
Now I have a filtered list and have tried to use the following but I can't get it to work.
I can use a more indepth script to get it to work but would rather use a one line script.
Code:
ListBox1.List = Sheets("working").Range("A1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
I have this code for a texbox to search into a listbox
It's working properly but sometimes it doesn't look at all the names in the list.. probably because some of the rows are hidden?
i have a workbook that has the following sheets
working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf
i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro
my macros involve printing certain pages dependng on what button is pressed
i get an error whatever
how do i stop this
iam trying to populate a listbox with only the workbooks hidden sheets. iam trying to adapt this code which i found in the forums
Private Sub UserForm_Initialize()
Dim wsSheet As Worksheet
Dim lngIndex As Long
With ThisWorkbook
Redim strarray(.Worksheets.Count - 1, 1) As String
lngIndex = 0
For Each wsSheet In .Worksheets
strarray(lngIndex, 0) = wsSheet. Name
lngIndex = lngIndex + 1
Next
End With
With ListBox1
.List = strarray
End With
End Sub
i have an old spreadsheet that i created about a year or so ago.
i managed to make some sheets "very hidden" as opposed to just "hidden" or "visible".
the problem is i can't find them ! i have tried "format, sheet, unhide" but that is greyed out.
i do have passwords for them, but i can't find them to put these in!
I have 3 columns of data that are being copied from a .csv file. The data is pasted into Columns B, C, & D. Columns C and D are hidden. So that the user does not have to unhide the columns in order to delete the data, I would like to make it so that when the data in column B is deleted, the data in columns C and D is also deleted.
View 14 Replies View RelatedI have a button on a sheet that runs a macro to unhide another sheet. That works, but I want the sheet made visible to remain forward. Instead, the button unhides the sheet and the sheet the button is on comes forward again. I am unable to figure how to keep the sheet made visible forward. Here is the macro 'as recorded'.
Sub UnhideSheet1()
Sheet2.Visible = True
End Sub
I have a 15 sheet spreadsheet that has many formula and results that are obtained from a "Master Sheet." I want to hide the "Master Sheet" from users view.
When I do so the formulae can't find the info they require. How do I hide a sheet so that users can't see it, but the formula from other sheets can still access it?
i've an excel file where a final presentable sheet is linked to some hidden sheets which i dont want the user to see. Thru VBA, i'm performing some format related stuff in those hidden sheets, using: Sheets("Historical1").Visible = True. later, VBA sets it back to FALSE. But, i want to get rid of this hiding/unhiding, if its possible. My code has the following structure:
Sub tame_blph1()
Sheets("Historical1").Visible = True
Sheets("Historical1").Activate
Dim coll As Integer
Dim startcoll As Integer
Dim endcoll As Integer
startcoll = 25
endcoll = 124
For coll = startcoll To endcoll Step 2
If Len(selection) > 0 Then Call Macro1
Next coll
Sheets("Historical1").Visible = False
Sheets("Table").Select
End Sub
In other words, can we perform the above without hiding and unhiding the Sheet called "Historical1"
I use templates (.xlt) to generate reports for different organizations. In some cases certain pages in the reports are hidden.
When printing I get blank pages where pages are hidden. How do I not get these pages printed at all?
I do use page breaks to format the reoprts.
I have a worksheet with 28 sheets. I want the first sheet to be the only sheet displayed when opening the file and then want people to have the ability to click a hyperlink to unhide a particular sheet. Then when they go back to the main page (via another hyperlink), the sheet that was unhidden will be hidden again. I was able to put together some code from [URL]. When I click on a hyperlink on the main page, I get a "Subscript out of range" error and it highlights Sheets(strSheet).Visible=True from the Worksheet_FollowHyperlink code. I made a basic spreadsheet with just 3 sheets and plugged this code in and it worked fine so I'm not quite sure what the error is. I attached the spreadsheet I'm using.
View 3 Replies View RelatedI have been sent a workbook which contains a number of formulae that reference hidden worksheets (see attached JPEG).
I cannot work out how to display these sheets? The workbook does not seem to be protected, I can edit and save the visible sheet. Can anyone shed some light on how to view the referenced sheets?
I have the following code which works fine when all of the sheets are not hidden:
View 3 Replies View RelatedI have a total of 15 sheets on my excel documents. I've hidden 10 (5 sheets will be shown) of those sheets so i can hyperlink to them from the sheet called "The plan". The name of the hidden sheets are called:
"Week 1"
"Week 2"
"Week 3"
"Week 4"
"Week 5"
"Week 6"
"Week 7"
"Week 8"
"Week 9"
"Week 10"
On "The plan" I have hyperlinked the words to the corresponding sheets then I hid the sheets, now the hyperlinks don't work. What should I do?
I just came across file that needs to be cleaned up. Problem is, the main summary references more than 50 hidden sheets for pay periods dating back to 2011.
I want to make everything values from previous pay periods then get rid of the hidden sheets.
ive got a macro which works with data on hidden sheets within the workbook - when i hide the sheets the macro gives errors and will not run. when i unhide the sheets everything runs fine!
View 9 Replies View Relatedhow can i see hidden sheets in a workbook?
View 3 Replies View RelatedI have 5 sheets in a workbook. Three are hidden. My code allows user to make as many copies of the hidden sheets as needed and place them at the end by means of a macro button. This works fine, but after multiple copies (4 to 15) it crashes! Don't know what I did wrong.
Sub CopyWs()
Sheet8.Visible = True
Sheet8.Select
Sheet8.Copy After:=Sheets(Worksheets.Count)
Sheet8.Visible = False
Sheet9.Visible = True
Sheet9.Select
Sheet9.Copy After:=Sheets(Worksheets.Count)
Sheet9.Visible = False
Sheet10.Visible = True
Sheet10.Select
Sheet10.Copy After:=Sheets(Worksheets.Count)
Sheet10.Visible = False
End Sub
I have a workbook with many worksheets. All I want to do is print to PDF those that are visible (I can use just the code name number can't I?) and ignore those that are hidden. It seems simple enough but it will run and do nothing.
Dim sMsg As String, FName As Variant
Dim myArray() As Integer
Dim i As Integer
Dim j As Integer
[Code]....
Here is what I think I want to do (however there may be a better way to get to the end result).
Macro 1:
Unhide all worksheets however, first list only the worksheets that are visible.
Macro 2:
I want to be able to hide all worksheets except those in the above list.
My intent would be to have these in the personal file so I could run either process on any file I have open so I think I would need to insert a tab for the list when unhiding & remove the tab after hiding.
I have found many strings unhiding all & hiding all except a named sheet... but can't find anything on the above?
I have 5 sheets, 3 of them are hidden, how can i put a 3 different password to unhide the hidden sheets.
View 6 Replies View RelatedI tried to Google this question but cannot find the answer. What is the maximum number of worksheets that Excel can hide within a workbook? I have a macro which hides all worksheets with the exception of the active worksheet and it has never failed. But this morning I had started working on a workbook containing many sheets and when I ran my HideSheets macro a maximum of 14 sheets hides every time. Is it my code (see below)?
View 2 Replies View RelatedI want this macro to run when I select sheet1 so I have dropped the code into sheet1. The macro runs fine as a stand alone but I get a 1004 protection error when placed into the worksheet module. Sheet "data2" is unprotected. I have tried "calling" the macro from the sheet1 module but it loops. I know it's simple but it's kicking my ***. data2 is hidden and workbook is unprotected as of now.
View 3 Replies View Related