Opening seperate Excel worksheets with a form command button

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
833
Heres the question...

I have a form that loads when the Excel workbook is opened. The workbook contains three sheets. I need to add three command buttons to the userform so that each button opens a worksheet, thereafter the form closes. This action ensures that the user selects the right sheet at start up and is only required for this purpose. Button one for example would open sheet one, button two would open sheet two and so on. I need help with the VBcode.
This message was edited by Jak on 2002-05-15 06:27
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Heres the question...

I have a form that loads when the Excel workbook is opened. The workbook contains three sheets. I need to add three command buttons to the userform so that each button opens a worksheet, thereafter the form closes. This action ensures that the user selects the right sheet at start up and is only required for this purpose. Button one for example would open sheet one, button two would open sheet two and so on. I need help with the VBcode.

Hi Jak,

Once you've added the commandbuttons you just need to double click them and enter code like this:-

Private Sub CommandButton1_Click()
Sheets("sheet1").Activate
Unload Me
End Sub

Private Sub CommandButton2_Click()
Sheets("sheet2").Activate
Unload Me
End Sub

Private Sub CommandButton3_Click()
Sheets("sheet3").Activate
End Sub


Obviously you may need to change the Sheets bit to match the names of your sheets.

HTH,
Dan
 
Upvote 0
Hi Dan

I tried out your code. I got a run-time error 9 message saying the subscript was out of range. I have looked at the code for each sheet and the sheet names are Sheet1, sheet2 and sheet3. The tab names are however different. Will this make a difference?

Any further help would be welcomed.
 
Upvote 0
Jak,

You will need to change the code to match the names of your sheets.

Regards,
Dan
 
Upvote 0
Hi Dan

I feel like Homer Simpson; Doh

I forgot to underscore the separate names of the worksheets. As soon as I did the code executed. I must have been thinking about duff beer, Cheers.
 
Upvote 0
I tried this but it isn't working for me. Could I be doing something wrong. I named my buttons and just want them to open the tab with that name. HELPPPPPPPPPP
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top