Code to select active workbook worksheet(s) in Excel 2007

[RickLM]

Code to select active workbook worksheet(s) in Excel 2007

Need help I have limited experience in VBA and have been looking online and
in the Excel 2007 books I have but can not seem to locate/grasp what I need.
I require:

Module to run automatically when workbook is started.

Dropdown to provide selections: (1) workbook book to be used normally or (2)
a dropdown that will allow opening, editing, and closing of any/all of active
workbook worksheets.

Option (2) to remain available while workbook is open.

Workbook will have up to 30 sheets, maybe more.

Thanks in advance to the “groupâ€
Rick

 

[Ryan H]

First you will need to setup a Userform with a ComboBox in it. Then put this
in the workbooks Open_Event:

' this will call the userform to show
Private Sub Workbook_Open()
UserForm1.Show
End Sub

Then put this in the Userforms Intialize Event:

' this will load all the userforms in the workbook into the combobox
Private Sub UserForm_Initialize()

Dim wks As Worksheet

' load worksheet names into combobox
For Each wks In Worksheets
ComboBox1.AddItem wks.Name
Next wks

End Sub


Then put this in the ComboBox1 Change Event:

' when you change the combobox it will hide/unhide worksheets
Private Sub ComboBox1_Change()

Dim wks As Worksheet

Sheets(ComboBox1.Value).Visible = xlSheetVisible

For Each wks In Worksheets
If wks.Name <> ComboBox1.Value Then
wks.Visible = xlSheetVeryHidden
End If
Next wks

End Sub


Hope this helps! If so, let me know, click "YES" below.

 

[RickLM]

Ryan
Thx for help but as I said I have very little background in VBA (just enough
to get me into trouble, I’m 62 so this stuff is REAL new to me). I got
dropdown to open and select any single worksheet. Have following questions,
if you have time.
1) Select “All†so workbook runs normally
2) Select and show more than one worksheet but not all
2) Can’t edit selected worksheet without closing combobox
3) After selecting a worksheet can ComboBox be hidden and then unhidden for
selection of different worksheet?
3) If ComboBox is closed how to run it again without closing/reopening
workbook?

Ryan see by your profile you are in training… so hope this is not too
mundane for you
Thx again and Happy Holidays
Rick