View original (French)

Activate 1 UserForm from another in VBA Excel?

Solved
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   -  
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   -
Hello,

I am working with the "modal" properties of two userforms... Grrr!!!

The principle of what I want to achieve:
I have two UserForms. When I open the file, both userforms appear side by side. So far, no problem!
With a button on one, I want to activate the second one, and vice versa, while keeping both visible!
Of course, Hide would have been very useful, but I really want to keep both userforms visible on the screen.

Is it possible?
If so, how?

Thank you very much for your contributions and/or wise advice...

Excel 2003 (not that important as it's >97, but I still mention it...)

--
Best regards,
Franck P

1 answer

  1.
    aquarelle Posted messages 7181 Registration date   Status Moderator Last intervention   1 311
     
    Good evening,

    I don't know if this will work for you, but you can make your userforms non-modal: 
    UserForm1.Show 0 UserForm2.Show 0


    You just need to click on the userform of your choice to activate it.
    This also allows you to work on your worksheet without closing your userforms.

    Have a good evening
    --
    "To find a solution to one's problems, one must make an effort."
    -1
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Hello watercolor and thank you.

      Actually, I know the modal property of UserForms.

      However, what I want to do is an automatic activation of UserForm2 from UserForm1 without any user intervention...

      Something like:
      In UserForm1: 
      Private Sub CommandButton1_Click() 'My procedure code here UserForm2.Activate End Sub Private Sub UserForm_Activate() MsgBox "Hello UserForm1 is activated" End Sub
      In UserForm2: 
      Private Sub UserForm_Activate() MsgBox "Hello UserForm2 is activated" End Sub Private Sub CommandButton1_Click() 'My procedure code here UserForm1.Activate End Sub

      So I tried replacing: UserForm1.Activate with: UserForm1.Show 1, userForm1.Show 0, but I still get the error message: "you cannot display a UserForm in modal mode blah blah blah..."

      Well, if that’s not possible, I’ll just hide one of the two with Hide...
      0
    2. aquarelle Posted messages 7181 Registration date   Status Moderator Last intervention   1 311
       
      Hello,

      For me, it works if I put:
      In USF1: 
      Private Sub CommandButton1_Click() 'My procedure code here Load UserForm2 UserForm2.Show 0 End Sub Private Sub UserForm_Activate() MsgBox "Hello, USF1 is activated" End Sub


      In USF2: 
      Private Sub UserForm_Activate() MsgBox "Hello, USF2 is activated" End Sub Private Sub CommandButton1_Click() 'My procedure code here Load UserForm1 UserForm1.Show 0 End Sub


      And to launch the display of both UserForms: 
      Sub Auto_Open() Load UserForm1 UserForm1.Show 0 Load UserForm2 UserForm2.Show 0 End Sub
      0
    3. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Thank you again for your interest in my issue.
      The problem with "Load" is that it reloads my user form into memory. However, in my Initialize procedure, I reset some of my public variables to zero. So, I'm afraid that Load will systematically reset variables that change as I use my user forms.

      I eventually found an alternative solution. I'm sharing it here; it might be useful...

      1- Replace the calls to the procedures: Private Sub UserForm_Activate() with: Sub UserForm1_Activate() (replace UserForm1 with the name of your UserForm)

      2- Call them from the other UserForm by: 
      CallByName UserForm1, "UserForm1_Activate", VbMethod
      3- Place a textbox (which we will make invisible) in each user form

      So the overall code would be:
      In USF1: 
      Private Sub CommandButton1_Click() CallByName UserForm2, "UserForm2_Activate", VbMethod With UserForm2.TextBox1 .Visible = True .SetFocus .Visible = False End With End Sub Sub UserForm1_Activate() MsgBox "USF 1 activated" End Sub

      In USF2: 
      Private Sub CommandButton1_Click() CallByName UserForm1, "UserForm1_Activate", VbMethod With UserForm1.TextBox1 .Visible = True .SetFocus .Visible = False End With End Sub Sub UserForm2_Activate() MsgBox "USF 2 activated" End Sub

      Here is an example workbook attached

      Could this make a useful CCM tip?
      Have a great day and thank you again.
      0
    4. aquarelle Posted messages 7181 Registration date   Status Moderator Last intervention   1 311
       
      Could it be a useful tip for CCM, right?
      Well, yeah... let’s get to work ;)

      Have a good day to you too, and you're welcome for my help, which turned out to be pretty useless ^^
      0
    5. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Well, yes... let's get to work ;)
      I'll get to it as soon as I finish this damn binder!!!...
      Yes, your help was useful because without you, I wouldn't have pushed myself harder and I would have just used hide...
      0