Attaching Macros To Items In a List Box
 

I have a list box ListBox1 on a worksheet called Data. The list box fill
range is List!A1:A12 where I have a list of months. I have 12 worksheets
with each month for the worksheet name. When a user selects a month from the
list box I would like to call the corresponding worksheet and hide the other
eleven. Is there an easy way to do this. I am not experienced with writing
macros...only recording them.

Attaching Macros To Items In a List Box
 

I use a combobox on a sheet to navigate to sheets.
The below code will list the sheets int he workbook, and navigate to the one selected if that helps:

Private Sub ComboBox1_DropButtonClick()
Dim SH As Worksheet
With ComboBox1
.Clear
For Each SH In ActiveWorkbook.Sheets
If SH.Name < "Inputs" Then
.AddItem SH.Name
End If
Next
End With
End Sub


Please the code(copy/paste) into the sheets code and then place a combobox on the sheet and attach
the macro pasted to it.


Corey....
"juliejg1" wrote in message
...
I have a list box ListBox1 on a worksheet called Data. The list box fill
range is List!A1:A12 where I have a list of months. I have 12 worksheets
with each month for the worksheet name. When a user selects a month from the
list box I would like to call the corresponding worksheet and hide the other
eleven. Is there an easy way to do this. I am not experienced with writing
macros...only recording them.

Attaching Macros To Items In a List Box
 

There must be one active sheet at all times. This code goes in the listbox
code module. Right click the listbox, then click ViewCode. Copy this code
into the module. The code should hide all but the active sheet and then then
find the selected item and make it visible while hiding what was the active
sheet.
I have not tested it.

Private Sub ListBox1_Click()
Dim sh As Worksheet
fName = ActiveSheet.Name
For i = 1 to Worksheets.Count
If Worksheets(i).Name < ActiveSheet.Name Then
Worksheets(i).Visible = False
End If
Next
If ActiveSheet.Name < me.Value Then
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = me.Value Then
sh.Visible = True
Worksheets(fName).Visible = False
End If
Next
End If
End Sub


"juliejg1" wrote:

I have a list box ListBox1 on a worksheet called Data. The list box fill
range is List!A1:A12 where I have a list of months. I have 12 worksheets
with each month for the worksheet name. When a user selects a month from the
list box I would like to call the corresponding worksheet and hide the other
eleven. Is there an easy way to do this. I am not experienced with writing
macros...only recording them.

Attaching Macros To Items In a List Box
 

Discard the other macro. It will give you an error message. Use the one
below, but put your list box on a UserForm and call the UserForm from the
standard code module with the following code:

Sub callUF()
UserForm1.Show
End Sub

You access the standard code module with Alt + F11. If the window is dark,
Select InsertModule on the menu bar to initiate the code module.

To set up the UserForm, while in the VB editor, select InsertUserForm from
the menu. Then from the controls toolbox, click on the listbox, then click
the location on the userform where you want the list box. Then right click
the list box and copy the code below into the code window.

Private Sub ListBox1_Click()
Dim sh As Worksheet
fName = ActiveSheet.Name
For i = 1 To Worksheets.Count
If Worksheets(i).Name < ActiveSheet.Name Then
Worksheets(i).Visible = False
End If
Next
If ActiveSheet.Name < ListBox1.Value Then
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = ListBox1.Value Then
sh.Visible = True
Worksheets(fName).Visible = False
End If
Next
End If
Unload UserForm1
End Sub


"JLGWhiz" wrote:

There must be one active sheet at all times. This code goes in the listbox
code module. Right click the listbox, then click ViewCode. Copy this code
into the module. The code should hide all but the active sheet and then then
find the selected item and make it visible while hiding what was the active
sheet.
I have not tested it.

Private Sub ListBox1_Click()
Dim sh As Worksheet
fName = ActiveSheet.Name
For i = 1 to Worksheets.Count
If Worksheets(i).Name < ActiveSheet.Name Then
Worksheets(i).Visible = False
End If
Next
If ActiveSheet.Name < me.Value Then
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = me.Value Then
sh.Visible = True
Worksheets(fName).Visible = False
End If
Next
End If
End Sub


"juliejg1" wrote:

I have a list box ListBox1 on a worksheet called Data. The list box fill
range is List!A1:A12 where I have a list of months. I have 12 worksheets
with each month for the worksheet name. When a user selects a month from the
list box I would like to call the corresponding worksheet and hide the other
eleven. Is there an easy way to do this. I am not experienced with writing
macros...only recording them.

Attaching Macros To Items In a List Box
 

The reason I suggest putting the listbox on the UserForm instead of the sheet
is that when you hide the sheet with the listbox on it, you have no way to
change the selection. The listbox is also hidden. With the listbox on a
UserForm, it can be called for any sheet. You might want to set up a butoon
or keyboard shortcut to run the macro that calls the userform, otherwise you
will have to call it from the ToolsMacromacro.

"juliejg1" wrote:

I have a list box ListBox1 on a worksheet called Data. The list box fill
range is List!A1:A12 where I have a list of months. I have 12 worksheets
with each month for the worksheet name. When a user selects a month from the
list box I would like to call the corresponding worksheet and hide the other
eleven. Is there an easy way to do this. I am not experienced with writing
macros...only recording them.