none
How to call VBA userform from Excel Button (or) How to call VBA userform from workbook

    Question

  • Hi ,

    I have taken an autoshape in Excel ( for button).
    In VBA i ahve one form.

    So, my issue is .. how can we access VBA form through the button in excel

    or                       how can we access VBA form through Thisworkbook in VBA project


    Please help me in this issue
    Monday, November 23, 2009 10:20 AM

Answers


  • Insert Shape in Excel (Ractangle, oval etc)

    Right click on the shape and click the Assign Micro option.
    Note: A new window will open asking your macro name (module name)

    In that window, click the New button.
    Note: It will create a new macro for you.

    e.g.
    Sub Rectangle1_Click()

    End Sub

    then you insert a userform, select Insert>Userform.

    Now, go to the module1 code window where your shape click method is defined, refer above sequence. and write a userform1.show statement which will call your userform on that shape's click event.

    Sub Rectangle1_Click()
        Userform1.Show
    End Sub


    Adding code in the workbook event..

    Open ThisWorkbook code window.
    In the code window, Select the Workbook object from object dropdown box.
    by default Workbook_Open method will be created. Inside it, write the code to show the userform.
    e.g.

    Private Sub Workbook_Open()
       Userform1.Show
    End Sub


    Regards,
    Sanjay

    • Marked as answer by Tim Li Monday, November 30, 2009 2:50 AM
    Friday, November 27, 2009 7:08 AM
  • Hi,

    You can use the Show method of the userform

    Sub Button_Click()

        Userform1.Show

    End Sub

    Change userform1 to appropriate name.
    Cheers www.andypope.info
    • Marked as answer by Tim Li Monday, November 30, 2009 2:50 AM
    Monday, November 23, 2009 10:46 AM

All replies

  • Hi,

    You can use the Show method of the userform

    Sub Button_Click()

        Userform1.Show

    End Sub

    Change userform1 to appropriate name.
    Cheers www.andypope.info
    • Marked as answer by Tim Li Monday, November 30, 2009 2:50 AM
    Monday, November 23, 2009 10:46 AM

  • Insert Shape in Excel (Ractangle, oval etc)

    Right click on the shape and click the Assign Micro option.
    Note: A new window will open asking your macro name (module name)

    In that window, click the New button.
    Note: It will create a new macro for you.

    e.g.
    Sub Rectangle1_Click()

    End Sub

    then you insert a userform, select Insert>Userform.

    Now, go to the module1 code window where your shape click method is defined, refer above sequence. and write a userform1.show statement which will call your userform on that shape's click event.

    Sub Rectangle1_Click()
        Userform1.Show
    End Sub


    Adding code in the workbook event..

    Open ThisWorkbook code window.
    In the code window, Select the Workbook object from object dropdown box.
    by default Workbook_Open method will be created. Inside it, write the code to show the userform.
    e.g.

    Private Sub Workbook_Open()
       Userform1.Show
    End Sub


    Regards,
    Sanjay

    • Marked as answer by Tim Li Monday, November 30, 2009 2:50 AM
    Friday, November 27, 2009 7:08 AM
  • Usefull Info..

    Thanks :)
    Monday, November 30, 2009 5:08 AM
  • Thanks!
    Tuesday, June 21, 2011 3:32 AM
  • this works great, so simple.

    thanks,joe

    Saturday, August 11, 2012 8:23 PM