worksheet_change / userform close

[James]

Is it possible to close a userform when you
go to another worksheet?

James

 

[smozgur]

In related sheet vba module:


Private Sub Worksheet_Activate()
UserForm1.Show vbModeless
End Sub

Private Sub Worksheet_Deactivate()
UserForm1.Hide
End Sub

 

[smozgur]

the first comment shows userform when you activate Sheet1 and hides when you deactivate it (jump to another one)

This one close the userform completely

Private Sub Worksheet_Deactivate()
unload UserForm1
End Sub

 

[James]

I tried the above and get the compile error
"variable not defined" the userform is named
menu. any suggestions?

James

 

[smozgur]

Please use your own Userform name instead UserForm1..

 

[James]

Thank Smozgur,

My error was in "UserForm.Menu" it works
and i learned something!

James

 

[smozgur]

Nice to hear that, about you learned something.

And there are too much to learn, trust me

 

[James]

Just out of curiosity could it be accomplished without naming a specific
UserForm?

 

[smozgur]

You can use

Unload thisworkbook.VBProject.VBComponents(index)

But you dont want to do this. Because you have to know exact index of UserForm. You can also check all vbcomponents' types in workbook and when you find the 3 for type for a component then you can understand that it is a userform and unload it.

I think using a specific name is easier. But if you have lots of userform and you dont know which is activated then you can apply this kind of code.

 

[smozgur]

Hmm, sorry.

Unload thisworkbook.VBProject.VBComponents(index)

wont work but it is still possible to set an object instead using it directly and play with it.