none
Excel VBA Userform instances gone after minimizing Excel Application Window RRS feed

  • Question

  • I created a userform called UserFrom1 and set its ShowModal as false. I wrote the following code in UserForm1 click event sub

    dim newuserform as new Userform1
    newuserform.show
    msgbox(VBA.userforms.count & " instances of UserForm1 are open")

    I also add the following code in thisworkbook open event sub:

    UserForm1.show

    After open this workbook, UserForm1 shows. If I click the UserForm1, another instance opens and magbox tells how many instances are open. When I re-activate the Excel Application Window after minimized it, I still can see all opened instances in the screen. This is exactly what I want.

    But I cannot add any CommandButton in the UserForm1. If I do so, When I minimize and then re-activate the Excel Application Window, I can only see the origial UserForm1 in the screen and other instanaces have gone. I want to keep all opened instances. How can I fix this problem?

    I use WindowXP and MS Office 2010, but I saved this workbook as Excel 97-2003 Workbook (*.xls)

    Please help and thanks in advance


    Qingping Cheng
    Thursday, March 17, 2011 11:17 PM

Answers

  • Hi,

    Sorry my mistake. I forgot to mention that if you are maintaining a variable globally and every time you re-instantiate the variable the older instance will be gone & that's why the forms are disappearing. However i'm not sure about your requirement but i've tried an alternative way to make sure all the instances of forms to be open even on minimizing.

    Can you use a Array of forms instead of a single variable... like show below.

    'Module - declare array as big as you want or you can re-dim it as & when required

     

    Public newuserform(10) As UserForm1

    'Workbook open

    Private Sub Workbook_Open()

        Set newuserform(0) = New UserForm1

        newuserform(0).Show vbModeless

    End Sub

    'Button click
    Private Sub CommandButton1_Click()
        Set newuserform(VBA.UserForms.Count + 1) = New UserForm1
        newuserform(VBA.UserForms.Count + 1).Show vbModeless
        MsgBox (VBA.UserForms.Count & " instances of UserForm1 are open")
    End Sub
    Make sure you remove the items from array once the instance of form is closed. let me know if this helps.

     


    Regards, Subhash Sreenivasachar
    • Proposed as answer by subhash.85s Wednesday, March 23, 2011 4:53 AM
    • Marked as answer by qp Cheng Wednesday, March 23, 2011 12:42 PM
    Tuesday, March 22, 2011 5:19 AM

All replies

  • Hi Qingping,

    Thanks for posting in the MSDN forum.

    As far as I see, that we are able to create many instance of a user form, but we are able to show only one of them.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, March 21, 2011 11:25 AM
    Moderator
  • Hi,

    I was able to replicate the issue which you are facing. Can you declare the form variable in a public module & create instance of the object in the button click. I was able to resolve the issue by doing so. Hope this helps!

    'Module1 - 

    Public newuserform As UserForm1

    'Button Click

    Private Sub CommandButton1_Click()

        Set newuserform = New UserForm1

        newuserform.Show vbModeless

        MsgBox (VBA.UserForms.Count & " instances of UserForm1 are open")

    End Sub 

     


    Regards, Subhash Sreenivasachar
    Monday, March 21, 2011 1:36 PM
  • Hi subhash.85s,

    After more testing, I found that (i) if I close an instance, say Instacne A, all instacnes opened by click the button on Instance A disappear after minimizing Excel Application Window. (ii) if I click the button on Instance A for 3 times I have 3 new instances opened, after minimizing Excel Application Window, two of the new instances disappear.

    Many thanks for your help!

     


    Qingping Cheng
    Monday, March 21, 2011 11:21 PM
  • Hi,

    Sorry my mistake. I forgot to mention that if you are maintaining a variable globally and every time you re-instantiate the variable the older instance will be gone & that's why the forms are disappearing. However i'm not sure about your requirement but i've tried an alternative way to make sure all the instances of forms to be open even on minimizing.

    Can you use a Array of forms instead of a single variable... like show below.

    'Module - declare array as big as you want or you can re-dim it as & when required

     

    Public newuserform(10) As UserForm1

    'Workbook open

    Private Sub Workbook_Open()

        Set newuserform(0) = New UserForm1

        newuserform(0).Show vbModeless

    End Sub

    'Button click
    Private Sub CommandButton1_Click()
        Set newuserform(VBA.UserForms.Count + 1) = New UserForm1
        newuserform(VBA.UserForms.Count + 1).Show vbModeless
        MsgBox (VBA.UserForms.Count & " instances of UserForm1 are open")
    End Sub
    Make sure you remove the items from array once the instance of form is closed. let me know if this helps.

     


    Regards, Subhash Sreenivasachar
    • Proposed as answer by subhash.85s Wednesday, March 23, 2011 4:53 AM
    • Marked as answer by qp Cheng Wednesday, March 23, 2011 12:42 PM
    Tuesday, March 22, 2011 5:19 AM
  • Hi Subhash,

    Using Array as public variable of UserFrom1 is a great idea and it works.

    I added a publica long variable numberofinstances instead of vba.userforms.count in the item of the array so I don't have to remove the item from the array when its instance has been closed. Based on your code I made the following changes

    Module - declare array as big as you want or you can re-dim it as & when required

    Public newuserform(10) As UserForm1
    
    Public numberofinstances as Long

    Workbook open

    Private Sub Workbook_Open()
    
     numberofinstances = 0 'initial 
    
     Set newuserform(numberofinstances) = New UserForm1
    
     newuserform(numberofinstances).Show vbModeless
    
    End Sub

    Button click

    Private Sub CommandButton1_Click()
    
    if numberofinstacnes >9 then
    
     msgbox("Too many sessions opened")
    
     exit sub
    
    end if
    
     numberofinstacnes = numberofinstances +1 
    
     Set newuserform(numberofinstacnes) = New UserForm1
    
     newuserform(numberofinstacnes).Show vbModeless
    
     MsgBox (VBA.UserForms.Count & " instances of UserForm1 are open")
    
    End Sub
    Thanks again for your help!

    Qingping Cheng
    • Marked as answer by qp Cheng Tuesday, March 22, 2011 11:22 PM
    • Unmarked as answer by qp Cheng Wednesday, March 23, 2011 12:42 PM
    Tuesday, March 22, 2011 11:21 PM