Hello,

I'm interested in using a combo box and command button from the control
toolbar. The combo box will contain the names of all the worksheets in this
file. This is done with a dynamic named range. The command button will act
like a "submit" button to take the user to the desired worksheet selected
from the combo box.

The names of the worksheets are like: Aetna, Blue Shield, Pacificare, etc.
Is there a way to do this?

Thanks in advance,

MJ
--
Thank You!

I'm not sure where you are running your controls from (a userform or the
spreadsheet directly), so where you put this is up in the air. Here, I have
assumed that you have a userform, so I placed it in the Activate event for
that userform...

Private Sub UserForm_Activate()
Dim SH As Worksheet
If ComboBox1.ListCount = 0 Then
For Each SH In Worksheets
ComboBox1.AddItem SH.Name
Next
End If
End Sub

If the ComboBox has nothing in it, the above code will populate it with the
worksheet names. Put this in the CommandButton's Click event to select the
worksheet name in the ComboBox...

Private Sub CommandButton1_Click()
If Len(ComboBox1.Text) Then
Worksheets(ComboBox1.Text).Activate
Else
MsgBox "Please select a worksheet to go to."
End If
End Sub

Rick

"maijiuli" <(E-Mail Removed)> wrote in message
news:756E1837-3058-43DB-8BF4-(E-Mail Removed)...
> Hello,
>
> I'm interested in using a combo box and command button from the control
> toolbar. The combo box will contain the names of all the worksheets in
> this
> file. This is done with a dynamic named range. The command button will
> act
> like a "submit" button to take the user to the desired worksheet selected
> from the combo box.
>
> The names of the worksheets are like: Aetna, Blue Shield, Pacificare, etc.
> Is there a way to do this?
>
> Thanks in advance,
>
> MJ
> --
> Thank You!

Thank you Mr. Rothstein. My controls are placed directly on the spreadsheet
so I put your codes into the Combo Box change and Command button click areas
of the VB editor. Thank you so much!
--
Thank You!


"Rick Rothstein (MVP - VB)" wrote:

> I'm not sure where you are running your controls from (a userform or the
> spreadsheet directly), so where you put this is up in the air. Here, I have
> assumed that you have a userform, so I placed it in the Activate event for
> that userform...
>
> Private Sub UserForm_Activate()
> Dim SH As Worksheet
> If ComboBox1.ListCount = 0 Then
> For Each SH In Worksheets
> ComboBox1.AddItem SH.Name
> Next
> End If
> End Sub
>
> If the ComboBox has nothing in it, the above code will populate it with the
> worksheet names. Put this in the CommandButton's Click event to select the
> worksheet name in the ComboBox...
>
> Private Sub CommandButton1_Click()
> If Len(ComboBox1.Text) Then
> Worksheets(ComboBox1.Text).Activate
> Else
> MsgBox "Please select a worksheet to go to."
> End If
> End Sub
>
> Rick
>
> "maijiuli" <(E-Mail Removed)> wrote in message
> news:756E1837-3058-43DB-8BF4-(E-Mail Removed)...
> > Hello,
> >
> > I'm interested in using a combo box and command button from the control
> > toolbar. The combo box will contain the names of all the worksheets in
> > this
> > file. This is done with a dynamic named range. The command button will
> > act
> > like a "submit" button to take the user to the desired worksheet selected
> > from the combo box.
> >
> > The names of the worksheets are like: Aetna, Blue Shield, Pacificare, etc.
> > Is there a way to do this?
> >
> > Thanks in advance,
> >
> > MJ
> > --
> > Thank You!
>
>

Maybe you could use something like:
http://contextures.com/xlToolbar01.html

maijiuli wrote:
>
> Hello,
>
> I'm interested in using a combo box and command button from the control
> toolbar. The combo box will contain the names of all the worksheets in this
> file. This is done with a dynamic named range. The command button will act
> like a "submit" button to take the user to the desired worksheet selected
> from the combo box.
>
> The names of the worksheets are like: Aetna, Blue Shield, Pacificare, etc.
> Is there a way to do this?
>
> Thanks in advance,
>
> MJ
> --
> Thank You!

--

Dave Peterson

Dave or should I say Sir Peterson, you are a genious. I can't thank you
enough for this one.

MJ
--
Thank You!


"Dave Peterson" wrote:

> Maybe you could use something like:
> http://contextures.com/xlToolbar01.html
>
> maijiuli wrote:
> >
> > Hello,
> >
> > I'm interested in using a combo box and command button from the control
> > toolbar. The combo box will contain the names of all the worksheets in this
> > file. This is done with a dynamic named range. The command button will act
> > like a "submit" button to take the user to the desired worksheet selected
> > from the combo box.
> >
> > The names of the worksheets are like: Aetna, Blue Shield, Pacificare, etc.
> > Is there a way to do this?
> >
> > Thanks in advance,
> >
> > MJ
> > --
> > Thank You!
>
> --
>
> Dave Peterson
>

Sire, is there a way to change your Navigation toolbar to live only in a
specific file.

I've been playing around with it for a couple hours now and it works great
but when I open other files the Navigation Toolbar always pops up in the
floating method.

To share this with my company I need the toolbar to either live in only one
file so it does not disrupt others files.

OR

If we can find a way to change the macro so it is not in the float position
on open. Maybe if the macro can be changed to live with all the other
toolbars on top when a file is open then this would probably be ok. At least
this way it is not in the way to the other users who want to use this addin.

Sorry if I'm asking for too much but it's too good to let go,


--
Thank You!


"Dave Peterson" wrote:

> Maybe you could use something like:
> http://contextures.com/xlToolbar01.html
>
> maijiuli wrote:
> >
> > Hello,
> >
> > I'm interested in using a combo box and command button from the control
> > toolbar. The combo box will contain the names of all the worksheets in this
> > file. This is done with a dynamic named range. The command button will act
> > like a "submit" button to take the user to the desired worksheet selected
> > from the combo box.
> >
> > The names of the worksheets are like: Aetna, Blue Shield, Pacificare, etc.
> > Is there a way to do this?
> >
> > Thanks in advance,
> >
> > MJ
> > --
> > Thank You!
>
> --
>
> Dave Peterson
>

I wouldn't put it a specific workbook. The point of the refresh button is so
that you can change workbooks and click that button and use it there. (Or
add/delete worksheets and refresh the list.)

Personally, I liked to make that toolbar appear in the worksheet. By floating
it over the worksheet, the user has a better chance of noticing it. They can
always reposition it where they want--either by dragging it or by double
clicking on the title bar.

But you can dock it if you add a couple of lines of code:

With cb
.Visible = True
.RowIndex = msoBarRowLast '<-- Added
.Position = msoBarTop '<-- Added

========
I'd keep this code separate from every other workbook, too. I'd put it in a
dedicated workbook and save it as an addin. Then just load it on demand, or put
it in my XLStart folder, or use Tools|Addins to install it.



maijiuli wrote:
>
> Sire, is there a way to change your Navigation toolbar to live only in a
> specific file.
>
> I've been playing around with it for a couple hours now and it works great
> but when I open other files the Navigation Toolbar always pops up in the
> floating method.
>
> To share this with my company I need the toolbar to either live in only one
> file so it does not disrupt others files.
>
> OR
>
> If we can find a way to change the macro so it is not in the float position
> on open. Maybe if the macro can be changed to live with all the other
> toolbars on top when a file is open then this would probably be ok. At least
> this way it is not in the way to the other users who want to use this addin.
>
> Sorry if I'm asking for too much but it's too good to let go,
>
> --
> Thank You!
>
> "Dave Peterson" wrote:
>
> > Maybe you could use something like:
> > http://contextures.com/xlToolbar01.html
> >
> > maijiuli wrote:
> > >
> > > Hello,
> > >
> > > I'm interested in using a combo box and command button from the control
> > > toolbar. The combo box will contain the names of all the worksheets in this
> > > file. This is done with a dynamic named range. The command button will act
> > > like a "submit" button to take the user to the desired worksheet selected
> > > from the combo box.
> > >
> > > The names of the worksheets are like: Aetna, Blue Shield, Pacificare, etc.
> > > Is there a way to do this?
> > >
> > > Thanks in advance,
> > >
> > > MJ
> > > --
> > > Thank You!
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Brilliant! Thanks again Sir.
--
Thank You!


"Dave Peterson" wrote:

> I wouldn't put it a specific workbook. The point of the refresh button is so
> that you can change workbooks and click that button and use it there. (Or
> add/delete worksheets and refresh the list.)
>
> Personally, I liked to make that toolbar appear in the worksheet. By floating
> it over the worksheet, the user has a better chance of noticing it. They can
> always reposition it where they want--either by dragging it or by double
> clicking on the title bar.
>
> But you can dock it if you add a couple of lines of code:
>
> With cb
> .Visible = True
> .RowIndex = msoBarRowLast '<-- Added
> .Position = msoBarTop '<-- Added
>
> ========
> I'd keep this code separate from every other workbook, too. I'd put it in a
> dedicated workbook and save it as an addin. Then just load it on demand, or put
> it in my XLStart folder, or use Tools|Addins to install it.
>
>
>
> maijiuli wrote:
> >
> > Sire, is there a way to change your Navigation toolbar to live only in a
> > specific file.
> >
> > I've been playing around with it for a couple hours now and it works great
> > but when I open other files the Navigation Toolbar always pops up in the
> > floating method.
> >
> > To share this with my company I need the toolbar to either live in only one
> > file so it does not disrupt others files.
> >
> > OR
> >
> > If we can find a way to change the macro so it is not in the float position
> > on open. Maybe if the macro can be changed to live with all the other
> > toolbars on top when a file is open then this would probably be ok. At least
> > this way it is not in the way to the other users who want to use this addin.
> >
> > Sorry if I'm asking for too much but it's too good to let go,
> >
> > --
> > Thank You!
> >
> > "Dave Peterson" wrote:
> >
> > > Maybe you could use something like:
> > > http://contextures.com/xlToolbar01.html
> > >
> > > maijiuli wrote:
> > > >
> > > > Hello,
> > > >
> > > > I'm interested in using a combo box and command button from the control
> > > > toolbar. The combo box will contain the names of all the worksheets in this
> > > > file. This is done with a dynamic named range. The command button will act
> > > > like a "submit" button to take the user to the desired worksheet selected
> > > > from the combo box.
> > > >
> > > > The names of the worksheets are like: Aetna, Blue Shield, Pacificare, etc.
> > > > Is there a way to do this?
> > > >
> > > > Thanks in advance,
> > > >
> > > > MJ
> > > > --
> > > > Thank You!
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>