Opening seperate Excel worksheets with a form command button

Jak · Apr 9th, 2002, 12:14 AM

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 ]

dk · Apr 9th, 2002, 12:56 AM

Quote:

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 · Apr 9th, 2002, 06:23 AM

Thanks for the assist. I will give it a go.

Jak · Apr 9th, 2002, 06:57 AM

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 · Apr 9th, 2002, 09:40 AM

Jak,

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

Regards,
Dan

Jak · Apr 9th, 2002, 01:57 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 12:14 AM	#1
Jak Board Regular Join Date: Apr 2002 Location: Newcastle upon Tyne Posts: 762	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 ]

Apr 9th, 2002, 06:23 AM	#3
Jak Board Regular Join Date: Apr 2002 Location: Newcastle upon Tyne Posts: 762	Thanks for the assist. I will give it a go.

Apr 9th, 2002, 06:57 AM	#4
Jak Board Regular Join Date: Apr 2002 Location: Newcastle upon Tyne Posts: 762	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.

Apr 9th, 2002, 09:40 AM	#5
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	Jak, You will need to change the code to match the names of your sheets. Regards, Dan

Apr 9th, 2002, 01:57 PM	#6
Jak Board Regular Join Date: Apr 2002 Location: Newcastle upon Tyne Posts: 762	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.