User Form to appear only on one worksheet only

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
Is it possible to make a user form just appear on a certain worksheet, rather than have it appear on all sheets of the workbook?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
A userform? It would depend where you called it from but in reality its a completely separate entity from a sheet. Are you sure you mean a userform?
 
Upvote 0
It is UserForm2 in the Project Explorer and has 7 command buttons for manipulating the data in various ways on the particular worksheet. When I load it, the form appears on screen no matter which worksheet I am viewing.
 
Upvote 0
Right,

How are you loading the form? by clicking the play button in the VBA window?

If you have the form set to load through a macro that is linked to a button on the worksheet, then the form will appear with that sheet in the background.

Alternatively you can have a condition in the form_initialize event that looks something like this:

Code:
if activesheet.name <> "TheSheetIWanttobebehind" then
msgbox("Can't call this form from this sheet!")
unload me
end if
 
Upvote 0
I have been using a command button to load the form

Private Sub CommandButton20_Click()
UserForm2.Show vbModeless
End Sub

I wanted the form to automatically appear when the particular worksheet was viewed, but it stays visible if I switch to a different worksheet.
 
Upvote 0
You need to use a worksheet change event in that case

Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "YourSheetGoesHere" And UserForm2.Visible = True Then
    Unload UserForm2
End If
If Sh.Name = "YourSheetGoesHere" And Userform2.Visible = False Then
    UserForm2.show
End If
End Sub

So if the sheet that is being made active is not equal to the sheet you want, and the form is actually loaded and visible, then unload that form. Otherwise, leave it alone.

That what you're after?
 
Upvote 0
Thanks for that, it worked fine.

Is there any way to make it reappear when I switch back to the relevant worksheet?
 
Upvote 0
Code:
If Sh.Name = "YourSheetGoesHere" And Userform2.Visible = False Then 
    UserForm2.show 
End If

The above section should do that - i.e. if the sheet name is your one, and the form is not visible, make it show
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top