Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Opening seperate Excel worksheets with a form command button

  1. #1
    Board Regular Jak's Avatar
    Join Date
    Apr 2002
    Location
    Newcastle upon Tyne
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular Jak's Avatar
    Join Date
    Apr 2002
    Location
    Newcastle upon Tyne
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular Jak's Avatar
    Join Date
    Apr 2002
    Location
    Newcastle upon Tyne
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jak,

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

    Regards,
    Dan

  6. #6
    Board Regular Jak's Avatar
    Join Date
    Apr 2002
    Location
    Newcastle upon Tyne
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    New Member
    Join Date
    Oct 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Opening seperate Excel worksheets with a form command button

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •