Opening seperate Excel worksheets with a form command button

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
811
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
811
Thanks for the assist. I will give it a go.
 

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
811
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.
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942

ADVERTISEMENT

Jak,

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

Regards,
Dan
 

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
811
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.
 

Lanesha1

New Member
Joined
Oct 29, 2014
Messages
1
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,640
Messages
5,654,539
Members
418,139
Latest member
nimesh72

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
Top