Userform to Launch Once per Session

breynolds0431

Active Member
Joined
Feb 15, 2013
Messages
303
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello. I have a userform that will launch on worksheet activate if certain conditions are met. So, it doesn't need to launch on workbook_open. But the user will move throughout the workbook to various other tabs and will need to revisit this tab that will show the userform. Basically, the userform is a message box. I would have used a message box, but the information within is quite voluminous. So, I'm looking for something that will limit the userform to launching only one time until it's saved or closed (i.e.; show once per session). Is there a way to count the number of times the userform is shown in a session and then use an if statement to limit it from showing more than once?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
If it’s essentially a textbox that stays open on one tab, once opened, why not just use a textbox? You can add some text that says “Click to close” if required.
 
Upvote 0
If it’s essentially a textbox that stays open on one tab, once opened, why not just use a textbox? You can add some text that says “Click to close” if required.

Hi. Not sure I follow. It's essentially a message box that appears based on certain criteria. It has button functionality that a message box can't handle. The biggest part I need to clarify is that the user form does not stay open. It's triggered by certain conditions giving the user an alert message. The user reviews the sheet and then closes the form. The issue is that once it's shown, it doesn't need to be repeatedly shown to the same user (i.e.; same session). So, I'm guessing a count of how many times the form was shown would be necessary. That count would then be reset after the file is saved/closed.
 
Upvote 0
One method is to set a counter in an obscure cell. Have your Sheet.Activate check for that counter first prior to displaying the UserForm.

Clear the counter number when closing the workbook.
 
Upvote 0
One method is to set a counter in an obscure cell. Have your Sheet.Activate check for that counter first prior to displaying the UserForm.

Clear the counter number when closing the workbook.

That sounds like it's what I need. Is there a way to count either the number of times the user form shows or how many times the specific worksheet is activated?
 
Upvote 0
You be using IF statements to see if your "marker" has already been entered into the cell.

So, with the UserForm, in the macro used to SHOW the Userform, first check the marker cell.

Same thing with the Worksheet_Activate.
 
Upvote 0
you don't need to use a cell, you can use a variable in VBA easily enough
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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