VBA for Userform to be stacked in a particular sheet

[blackorchids2002]

Hi Masters,

Just a question, is it possible to display a userform and will remain in a particular sheet (ex. Sheet1)?

 

[AlphaFrog]

You could Show\Hide a userform when a sheet is Activated\Deactivated if that's what you mean?

Example in the Sheet1 code module:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Activate()
    UserForm1.Show vbModeless
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Deactivate()
    UserForm1.Hide
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[JLGWhiz]

Hi Masters,

Just a question, is it possible to display a userform and will remain in a particular sheet (ex. Sheet1)?

A UserForm is not part of the Excel basic worksheet platform. It is created on a different platform and is basically a separate window from the worksheet. You can use the Activate event to make a userform appear when a worksheet is activated:

Private Sub Worksheet_Activate()
UserForm1.Show
End Sub

This would give the appearance that the form is attached to it.

To keep the form from appearing when another sheet is slected:

Private Sub Worksheet_Deactivate()
Unload UserForm1
End Sub


These code snippets would go in the sheet code module of the particular sheet that you want the form to appear with.

A note of caution...While the userform is open in Modal form, you cannot make any manual changes to worksheets.

 

[blackorchids2002]

Hi AlphaFrog,

Perfrect!!! This is what I'm looking for.

Thanks a lot for your time.

Cheers,
Lhe

You could Show\Hide a userform when a sheet is Activated\Deactivated if that's what you mean?

Example in the Sheet1 code module:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Activate()
    UserForm1.Show vbModeless
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Deactivate()
    UserForm1.Hide
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[AlphaFrog]

You're welcome and thank you for the feedback. You could thank JLGWhiz as well. Same solution a few minutes later, but no less deserving of gratitude.

 

[JLGWhiz]

You're welcome and thank you for the feedback. You could thank JLGWhiz as well. Same solution a few minutes later, but no less deserving of gratitude.

Always happy to see somebody solve their problems, no matter who gets the accolades.
Regards, JLG