PDA

View Full Version : Solved: Listbox Populated by Veryhidden Worksheets



ameritecc
03-01-2008, 01:51 PM
The code shown below is taken from your vbax member DRJ. It is for drag and drop printing and I must add that with the other code provided with it, it works wonderful. However I need to know how to include code within this block of code that prevents "veryhidden sheets" from showing up in List Box 1. I have used several code lines but my syntax within this block of code is not working. I have many worksheets that are designated by code to be very hidden within the worksheet modules themselves, but they show up in this listbox on this form when I open it. Please advise.

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ListBox1.AddItem ws.Name
Next

End Sub

Andy Pope
03-02-2008, 06:34 AM
Hi,

You need to test the Visible property of the worksheet
This will print the worksheet name and visible status to the immediate window.


Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetHidden Then
Debug.Print ws.Name, "Hidden"
ElseIf ws.Visible = xlSheetVeryHidden Then
Debug.Print ws.Name, "Very Hidden"
Else
Debug.Print ws.Name, "visible"
End If
Next

lucas
03-02-2008, 07:50 PM
Moved to the Excel help forum....

Ameritecc, Pleas post in the appropriate forum.....ie Announcements is not the appropriate forum to post a question about Excel....

Looks Like Andy has already answered your question.

ameritecc
03-02-2008, 09:58 PM
I'm new at this and thought I was in the right place. Sorry

Andy Pope
03-05-2008, 02:15 AM
In reply to a PM

You need add the conditional test to your code that populates the listbox.
This will include sheets that are visible or hidden but not very hidden.


Private Sub UserForm_Initialize()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Visible
Case xlSheetVisible, xlSheetHidden
ListBox1.AddItem ws.Name
End Select
Next

End Sub

ameritecc
03-05-2008, 06:43 AM
Andy,
Thanks! This worked like a charm. It shows only the visible sheets and the drag and drop are in tact. I can now apply what you showed me here to another scenario for a vavigator form with just a few modifications. I appreciate the help you gave me.

lucas
03-05-2008, 06:22 PM
ameritecc, If you got an answer then please mark your thread solved using the thread tools at the top of the page. Also please take a minute to read our FAQ also available at the top of the page. Please read the part about PM's for information......we would really rather you share your questions and answers here on the forum so others can learn from your process......thanks. Glad you got the help you needed.