VBA code to Hide Sheets from the List-Box in User Form

Hi,

Could someone please help on hiding sheets from the list box in the User Form, I am using this code, I have tried the one highlighted, somehow it doesn’t work and the sheets I would like to hide do show up in the list box:-

Private Sub CommandButton1_Click()
'Variable Declaration
Dim iCnt As Integer
Dim count As Integer, lastRow As Integer, destCol As Integer
count = 0: destCol = 1
For iCnt = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCnt) = True Then
ShtName = ListBox1.List(iCnt)
Sheets(ShtName).Visible = False
End If
Next iCnt
'Unload userform
Unload Me
End Sub

Private Sub CommandButton2_Click()
'Variable Declaration
Dim iCnt As Integer
Dim count As Integer, lastRow As Integer, destCol As Integer
count = 0: destCol = 1
For iCnt = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCnt) = True Then
ShtName = ListBox1.List(iCnt)
Sheets(ShtName).Visible = True
End If
Next iCnt
'Unload userform
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
ListBox1.AddItem Sht.Name
Next
End Sub
Sub Sheets_VeryHidden()
Dim Sht As Worksheet
'Specify the Worksheet names which you dont want to display in the listbox
MySheets = Array("Input_Auto", "Input_Manual")
For i = LBound(MySheets) To UBound(MySheets)
Sheets(MySheets(i)).Visible = xlSheetVeryHidden
Next
End Sub
Top Contributor Asked on February 2, 2017 in VBA: Programming.
Add Comment
2 Answer(s)

HI,

Please run ‘Sheets_VeryHidden ‘ procedure before clicking on show userform. So that you will not see specified worksheets in list box.

If you are still facing problem after done the above specified instructions, Please send your Excel file at info@analysistabs.com

Regards!

Expert Answered on February 2, 2017.
Add Comment

Thank you for your help as also, much appreciated.

Top Contributor Answered on February 2, 2017.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.