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

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 requi

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

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

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.

--
Cheers,
Ryan


"RickLM" wrote:

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 requi

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

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

Ryan
Thx for help but as I said I have very little background in VBA (just enough
to get me into trouble, Im 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) Cant 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

--
RickLM


"Ryan H" wrote:

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.

--
Cheers,
Ryan


"RickLM" wrote:

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 requi

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