Hidden/Visible Sheets In Listbox

Sep 22, 2009

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 Replies


Format All Cells In All Sheets To Protection Hidden On Visible And Hidden Tabs

Feb 28, 2014

I 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 Related

Visible Sheets To Be Hidden With The Exception Of The Sheet Called "open". Then Is Saves The File

Sep 9, 2008

i am using the macro below to get excel to hide all sheets but the one called "open"...

Sub savemini()

ActiveWorkbook.Unprotect "letmein"

  For Each sh In ThisWorkbook.Worksheets
       If Not sh.Name = "open" Then sh.Visible = xlVeryHidden
   Next sh

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.

View 9 Replies View Related

LastRow Needs To Select Next VISIBLE Row (not Hidden Row)

Jul 19, 2007

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

View 9 Replies View Related

Spreadsheet Open But Not Visible. Hidden

Nov 8, 2006

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 Related

Toggle 2 Subs (between Either Hidden Or Visible Menus)

Dec 11, 2013

I 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.

View 2 Replies View Related

Change Textbox Border Color - Visible Or Hidden

Mar 8, 2012

What command I would use to change the text box border color, and set text border to visible or hidden?

View 3 Replies View Related

Listbox Visible Cells Only?

Sep 5, 2012

I was using the following scripts to fill a listbox in a userform.

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.

ListBox1.List = Sheets("working").Range("A1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)

View 2 Replies View Related

Listbox Referring To Hidden Range

Apr 11, 2014

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?

View 4 Replies View Related

Hiding Sheets But Still Letting Macros Run When Printing Hidden Sheets

Nov 10, 2008

i have a workbook that has the following sheets

working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)

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

View 14 Replies View Related

Populate Listbox With Hidden Sheet Names

Dec 21, 2006

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
End With
With ListBox1
.List = strarray
End With
End Sub

View 7 Replies View Related

Very Hidden Sheets Are Very Hidden

Jan 2, 2007

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!

View 9 Replies View Related

Delete Adjacent (hidden) Data When Deleting Visible Data

Dec 10, 2009

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 Related

Keep Hidden Sheet Made Visible As Active Sheet

Mar 2, 2007

I 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

View 7 Replies View Related

Hidden Sheets

Oct 3, 2008

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?

View 4 Replies View Related

VBA With Hidden Sheets

Dec 5, 2007

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
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
End Sub

In other words, can we perform the above without hiding and unhiding the Sheet called "Historical1"

View 3 Replies View Related

Print Visible Sheets Only

Dec 5, 2007

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.

View 4 Replies View Related

Hyperlinks To Hidden Sheets?

Jun 15, 2013

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 Related

Hidden Sheets Within An Workbook

Nov 18, 2009

I 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?

View 4 Replies View Related

Selecting Hidden Sheets

Sep 2, 2009

I have the following code which works fine when all of the sheets are not hidden:

View 3 Replies View Related

Hyperlink To Hidden Sheets

Apr 5, 2013

I 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?

View 2 Replies View Related

VBA To Delete All Hidden Sheets?

Aug 21, 2013

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.

View 4 Replies View Related

Macros And Hidden Sheets

Jul 31, 2007

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 Related

View Hidden Sheets

Dec 4, 2006

how can i see hidden sheets in a workbook?

View 3 Replies View Related

Copy Hidden Sheets

Oct 17, 2007

I 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.Copy After:=Sheets(Worksheets.Count)
Sheet8.Visible = False

Sheet9.Visible = True
Sheet9.Copy After:=Sheets(Worksheets.Count)
Sheet9.Visible = False

Sheet10.Visible = True
Sheet10.Copy After:=Sheets(Worksheets.Count)
Sheet10.Visible = False
End Sub

View 9 Replies View Related

Print Only Visible Sheets To PDF Code?

Feb 27, 2012

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


View 6 Replies View Related

Hide All Sheets Except Those Previously Visible

Mar 23, 2014

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?

View 2 Replies View Related

Different Password To Unhide The Hidden Sheets.

Nov 22, 2008

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 Related

Maximum Number Of Hidden Sheets?

Feb 25, 2009

I 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 Related

Advanced Filter On Hidden Sheets

Jul 6, 2009

I 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

Copyrights 2005-15 www.BigResource.com, All rights reserved