Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rookie question | Charts and Charting in Excel | |||
goto (F5) question | Excel Discussion (Misc queries) | |||
Listbox Question | Excel Discussion (Misc queries) | |||
Validation Data & Goto question | Excel Discussion (Misc queries) | |||
Listbox question | Excel Programming |