VBA rookie question - listbox and GOTO
 

Hi Brad

Private Sub CommandButton1_Click()
On Error Resume Next
Worksheets(ListBox1.Text).Activate
Unload Me
End Sub

The On Error thing takes care of both "no such sheet" and "no listitem selected". Not in a
very elegang way, but it works most of the time.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Brad Zenner" wrote in message
...
I am creating a userform with a listbox of sheet names (e.g. APPLE, BANANA,
PEAR, etc.) All of the items in the list box reference sheet names in the
workbook.

What I'm wondering is if there is a way to go to the sheet that's selected
in the listbox when closing out of the userform (so when I select APPLE and
click OK, it takes me to the APPLE worksheet).

Any help is greatly appreciated. Thanks.
Brad Z

VBA rookie question - listbox and GOTO
 

That worked great! Thanks Harald.

Now, let me throw this at you, or anyone else...Lets say I create Listbox2
on that same user form, is there a way that I can have ListBox2 go to a
specific cell or column on the worksheet selected in Listbox1?

If I can do that great. If not, I'm still very happy about the other code.
Thanks.
Brad

"Harald Staff" wrote in message
...
Hi Brad

Private Sub CommandButton1_Click()
On Error Resume Next
Worksheets(ListBox1.Text).Activate
Unload Me
End Sub

The On Error thing takes care of both "no such sheet" and "no listitem
selected". Not in a
very elegang way, but it works most of the time.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Brad Zenner" wrote in message
...
I am creating a userform with a listbox of sheet names (e.g. APPLE,
BANANA,
PEAR, etc.) All of the items in the list box reference sheet names in
the
workbook.

What I'm wondering is if there is a way to go to the sheet that's
selected
in the listbox when closing out of the userform (so when I select APPLE
and
click OK, it takes me to the APPLE worksheet).

Any help is greatly appreciated. Thanks.
Brad Z

VBA rookie question - listbox and GOTO
 

Brad,

Do you mean to point Listbox2 at a range to give you dynamic loading of that
listbox? If so, try

ListBox2.List = Worksheets(ListBox1.Value).Range("A1:A10").Value

--

HTH

Bob Phillips

"Brad Zenner" wrote in message
...
That worked great! Thanks Harald.

Now, let me throw this at you, or anyone else...Lets say I create Listbox2
on that same user form, is there a way that I can have ListBox2 go to a
specific cell or column on the worksheet selected in Listbox1?

If I can do that great. If not, I'm still very happy about the other code.
Thanks.
Brad

"Harald Staff" wrote in message
...
Hi Brad

Private Sub CommandButton1_Click()
On Error Resume Next
Worksheets(ListBox1.Text).Activate
Unload Me
End Sub

The On Error thing takes care of both "no such sheet" and "no listitem
selected". Not in a
very elegang way, but it works most of the time.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Brad Zenner" wrote in message
...
I am creating a userform with a listbox of sheet names (e.g. APPLE,
BANANA,
PEAR, etc.) All of the items in the list box reference sheet names in
the
workbook.

What I'm wondering is if there is a way to go to the sheet that's
selected
in the listbox when closing out of the userform (so when I select
APPLE
and
click OK, it takes me to the APPLE worksheet).

Any help is greatly appreciated. Thanks.
Brad Z

VBA rookie question - listbox and GOTO
 

Sorry, should have explained this further...

Listbox1 and 2 are lists on a hidden sheet. Listbox1 takes you to a specific
sheet in the workbook. I'd like Listbox2 to take you to a specific cell on
the worksheet that listbox1 selects.

For example, Listbox2 has these values: Week 1, Week 2, Week 3, Week 4. I
would like Week 1, when selected, to go to cell B4, Week 2 to go to E4, Week
3 to H4, Week 4 to K4.

Possible?
Brad

"Bob Phillips" wrote in message
...
Brad,

Do you mean to point Listbox2 at a range to give you dynamic loading of
that
listbox? If so, try

ListBox2.List = Worksheets(ListBox1.Value).Range("A1:A10").Value

--

HTH

Bob Phillips

"Brad Zenner" wrote in message
...
That worked great! Thanks Harald.

Now, let me throw this at you, or anyone else...Lets say I create
Listbox2
on that same user form, is there a way that I can have ListBox2 go to a
specific cell or column on the worksheet selected in Listbox1?

If I can do that great. If not, I'm still very happy about the other
code.
Thanks.
Brad

"Harald Staff" wrote in message
...
Hi Brad

Private Sub CommandButton1_Click()
On Error Resume Next
Worksheets(ListBox1.Text).Activate
Unload Me
End Sub

The On Error thing takes care of both "no such sheet" and "no listitem
selected". Not in a
very elegang way, but it works most of the time.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Brad Zenner" wrote in message
...
I am creating a userform with a listbox of sheet names (e.g. APPLE,
BANANA,
PEAR, etc.) All of the items in the list box reference sheet names
in
the
workbook.

What I'm wondering is if there is a way to go to the sheet that's
selected
in the listbox when closing out of the userform (so when I select
APPLE
and
click OK, it takes me to the APPLE worksheet).

Any help is greatly appreciated. Thanks.
Brad Z