display sheet choices in combo box

Is it possible to have a combo box give the user a choice of sheets within a
workbook, and to then open the selected sheet?

Wazooli

Yes. In the examples below, I have a ComboBox on Sheet1. Every time that Sheet1 is selected it refills the ComboBos with the names of all worksheets in the workbook. When a selection is made from the ComboBox, that worksheet is selected.


Private Sub Worksheet_Activate()
Dim ws As Worksheet

Sheet1.ComboBox1.Clear
For Each ws In ThisWorkbook.Worksheets
Sheet1.ComboBox1.AddItem ws.Name
Next ws
End Sub


Private Sub ComboBox1_Change()
myWS = Sheet1.ComboBox1.Value
If myWS <> "" Then Sheets(myWS).Select
Sheet1.ComboBox1.Value = ""
End Sub

ooops, I forgot to say ...

All of this code needs to go on the Code Window for the Sheet that contains the ComboBox. In this case, Sheet1.

- Pete

Yes, but why bother? You can do the same thing by right-clicking on the
VCR-type buttons at the bottom left of the workbook window.

--

Vasant



"Wazooli" <[email protected]> wrote in message
news:[email protected]...
> Is it possible to have a combo box give the user a choice of sheets within
a
> workbook, and to then open the selected sheet?
>
> Wazooli

Sub Drpdwn_Click()
Dim sSheet as String, sName as String
sname = Application.Caller
With Activesheet.DropDowns(sName)
sSheet = .List(.ListIndex)
End With
Worksheets(sSheet).Activate
End Sub

To load you forms Dropdown/Combobox

sub LoadBox()
Dim sh as Worksheet
with Activesheet.DropDowns("Drop Down 1")
.RemoveAllItems
for each sh in ThisWorkbook.Worksheets
.AddItem sh.Name
Next
End With
End Sub

--
Regards,
Tom Ogilvy


"Wazooli" <[email protected]> wrote in message
news:[email protected]...
> Is it possible to have a combo box give the user a choice of sheets within
a
> workbook, and to then open the selected sheet?
>
> Wazooli

Thanks Tom. I'm glad you can appreciate what I am trying to accomplish. I
will try this the next chance I get.

Wazooli

"Tom Ogilvy" wrote:

> Sub Drpdwn_Click()
> Dim sSheet as String, sName as String
> sname = Application.Caller
> With Activesheet.DropDowns(sName)
> sSheet = .List(.ListIndex)
> End With
> Worksheets(sSheet).Activate
> End Sub
>
> To load you forms Dropdown/Combobox
>
> sub LoadBox()
> Dim sh as Worksheet
> with Activesheet.DropDowns("Drop Down 1")
> .RemoveAllItems
> for each sh in ThisWorkbook.Worksheets
> .AddItem sh.Name
> Next
> End With
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Wazooli" <[email protected]> wrote in message
> news:[email protected]...
> > Is it possible to have a combo box give the user a choice of sheets within
> a
> > workbook, and to then open the selected sheet?
> >
> > Wazooli
>
>
>

Again, that is a combobox from the Forms toolbar. Not from the Control
Toolbox Toolbar. If you want to use the latter, post back.

--
Regards,
Tom Ogilvy

"Wazooli" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom. I'm glad you can appreciate what I am trying to accomplish.
I
> will try this the next chance I get.
>
> Wazooli
>
> "Tom Ogilvy" wrote:
>
> > Sub Drpdwn_Click()
> > Dim sSheet as String, sName as String
> > sname = Application.Caller
> > With Activesheet.DropDowns(sName)
> > sSheet = .List(.ListIndex)
> > End With
> > Worksheets(sSheet).Activate
> > End Sub
> >
> > To load you forms Dropdown/Combobox
> >
> > sub LoadBox()
> > Dim sh as Worksheet
> > with Activesheet.DropDowns("Drop Down 1")
> > .RemoveAllItems
> > for each sh in ThisWorkbook.Worksheets
> > .AddItem sh.Name
> > Next
> > End With
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Wazooli" <[email protected]> wrote in message
> > news:[email protected]...
> > > Is it possible to have a combo box give the user a choice of sheets
within
> > a
> > > workbook, and to then open the selected sheet?
> > >
> > > Wazooli
> >
> >
> >