Userform

    Hi,


    I have a question:
    I have a userform in workbook 1.
    I want to open that userform when I click a commandbutton in workbook2.
    Workbook 1 and 2 are opened.
    How can I do that?


    thanx
    Fluppe

    Hi Fluppe,


    If Book1.xls contains a userfom named Userform1, to show the userfrom add a subroutine to a module within Book1.xls.


    Public Sub ShowA()
    ' in module in Book1.xls project
    Userform1.show
    End Sub


    Book2.xls also contains a userform called Userform1. To show this add a subroutine to a module in Book2.xls.


    Public Sub ShowB()
    ' in module in Book2.xls project
    Userform1.show
    End Sub



    Assign the macro Book1.xls!ShowA to a button in either workbook to display userform. Likewise you could use the macro Book2.xls!ShowB.


    Hopefully this is clear.


    Cheers
    Andy

    [h4]Cheers
    Andy    [/h4]

    I think that I may be interpreting this differently. If you want to display the UserForm in a different Workbook you will have to make it modeless by changing ShowModal to false in the Properties of the UserForm. I have done this before, but activated the UserForm in it's own Workbook then switched Workbooks.

    Hi Andy,
    I want just one userform that can be used in different workbooks. I don't want to copy every userform to each workbook.


    Hi royUK,
    Maybe I did something wrong because the ShowModal doesn't seem to work?

    Hi Fluppe


    This works for me. Here is an example and a screenshot of setting.


    Show the form and open another Workbook. The form should stay visible.


    Using WindowsXP and Excel2002. This might make a difference if you aren't. There is a workaround somewhere and I will try to find it if you need it


    Roy

    Hi royUK,
    in the example the module and the userform are in the same file.
    When I want to show your userform but the code pasted in another module in another workbook I get a runtime error '424' object required.
    I'm using excel2000.

    Hi Fluppe,


    The macro assignment for the button in workbook2 would still be workbook1.xls!ShowA.


    But if as Roy suggests you want the user to be able to change workbooks whilst the userform is displayed then you need the Modal option.


    This requires XL2000 or above.


    Stephen Bullen has an example of a Modeless userform on his site.


    http://www.bmsltd.co.uk/Excel/Default.htm


    Cheers
    Andy

    [h4]Cheers
    Andy    [/h4]

    Hi, Roy
    The example you gave me works perfect but I don't think that this is what I need.


    I want to store the userform1 in a "mainpage" because I have alot of workbooks that contain the code userform1.show. I don't want to copy the userform to each workbook because when I have to change something on the userform I have to do it a hundred times instead of one time in the "mainpage".
    I don't want to open the userform1 in the mainpage and then go to a certain workbook. When I'm in a workbook I want to open the userform stored in the mainpage.


    I don't understand the example that Andy gave me.



    Thanx
    Fluppe :(

    Hi,


    Within the workbook, Mainpage.xls (I'm assuming thats what it is called) create a public sub routine to display the userform,


    Public Sub ShowUserform1


    Userform1.show


    End Sub


    To display this userform from any other workbook use this as the assigned macro.


    mainpage.xls!ShowUserform1


    If the workbooks are in different folders then add the full path to the line above.


    Now when a button is pressed that requires Userform1 from within mainpage.xls it should be displayed.


    If the workbook mainpage.xls is not already open then it should be loaded, this main prompt the user for macro enable.


    hopefully this is what you are after.


    Cheers
    Andy

    [h4]Cheers
    Andy    [/h4]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login