Userform to Launch Once per Session

breynolds0431

Board Regular
Joined
Feb 15, 2013
Messages
69
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?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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.
 

breynolds0431

Board Regular
Joined
Feb 15, 2013
Messages
69
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.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,861
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.
 

breynolds0431

Board Regular
Joined
Feb 15, 2013
Messages
69

ADVERTISEMENT

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?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,861
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.
 

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
you don't need to use a cell, you can use a variable in VBA easily enough
 

Watch MrExcel Video

Forum statistics

Threads
1,123,144
Messages
5,599,978
Members
414,354
Latest member
Flaxarn

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