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

Answer 1 / 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
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
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
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
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
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
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
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
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