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?
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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?
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
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.
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323

ADVERTISEMENT

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.
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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?
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323

ADVERTISEMENT

Thanks for that, it worked fine.

Is there any way to make it reappear when I switch back to the relevant worksheet?
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,559
Members
414,156
Latest member
WDMix

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
Top