UserForm to deactivate after first use

[harveya915]

I have a UserForm that pops up when the Sheet is opened for the first time. The UserForm only contains instructions and an "OK" button that makes the UserForm unload. The only problem now is that the UserForm will be popping up every time the sheet is opened.
Is there a way for the User Form to only pop up the first time the sheet is opened?

 

[ZVI]

I would do it slightly differently.
I would write a ShowInstructions sub, and have that called from the (one time) workbook Open event.
That way, the user could look at the instructions whenever they wanted (by calling ShowInstructions), but it would be forced on them only once.

Hi Mike,
Thanks, your suggestion is more user-freandly.
Here is the implementation:

' Put all the code to ThisWorkbook module
 
Private Sub Workbook_Open()
  If GetSetting(AppName:="HarveyaApp", Section:="Startup", Key:="Ok", Default:="0") = "0" Then
    SaveSetting AppName:="HarveyaApp", Section:="Startup", Key:="Ok", setting:="1"
    ShowInstructions
  End If
End Sub
 
Sub ShowInstructions()
  ' Assign Help button/shape to this sub to see the Instructions in UserForm1 anytime
  UserForm1.Show
End_Sub
 
Private Sub RestorePopUp()
  ' For debugging purpose only - add one more auto-popup ability
  DeleteSetting AppName:="HarveyaApp", Section:="Startup", Key:="Ok"
End Sub

Vlad

 

[My Aswer Is This]

yes

Well that is a different story.
In your original post you said:

I have a UserForm that pops up when the ​

Sheet​

is opened for the first time
And sheets are not opened. Sheets are activated.​

 

[harveya915]

Well that is a different story.
In your original post you said:

I have a UserForm that pops up when the ​

Sheet​

is opened for the first time
And sheets are not opened. Sheets are activated.​

I do apologize. I am new. I really don't know the difference between sheet and workbook.

But now I know there is a difference.

 

[harveya915]

Hi Mike,
Thanks, your suggestion is more user-freandly.
Here is the implementation:

' Put all the code to ThisWorkbook module
 
Private Sub Workbook_Open()
  If GetSetting(AppName:="HarveyaApp", Section:="Startup", Key:="Ok", Default:="0") = "0" Then
    SaveSetting AppName:="HarveyaApp", Section:="Startup", Key:="Ok", setting:="1"
    ShowInstructions
  End If
End Sub
 
Sub ShowInstructions()
  ' Assign Help button/shape to this sub to see the Instructions in UserForm1 anytime
  UserForm1.Show
End_Sub
 
Private Sub RestorePopUp()
  ' For debugging purpose only - add one more auto-popup ability
  DeleteSetting AppName:="HarveyaApp", Section:="Startup", Key:="Ok"
End Sub

Vlad

I'm curious to know, does it have to say "HarveyApp"? Or do i substitute this for something else?

 

[ZVI]

I'm curious to know, does it have to say "HarveyApp"? Or do i substitute this for something else?

You may change "HarveyaApp" across the code to "MyApp" or something similar.
It's the name saved in the registry of a computer.
Read more details in VBA help for the SaveSetting function.

 

[harveya915]

And just for the debugging purpose, this code will add one more popup ability:

Sub RestorePopUp()
  DeleteSetting AppName:="HarveyaApp", Section:="Startup", Key:="Ok"
End Sub

What does this one do exactly? And where does it go? Does it go in ThisWorkbook?

 

[ZVI]

What does this one do exactly? And where does it go? Does it go in ThisWorkbook?

Code of RestorePopUp deletes the previosly saved (via Workbook_Open) registry setting.
After such a deleting the UserForm1 will be shown once again at opening of Workbook.
You may put code of RestorePopUp to any VBA module including ThisWorkbook module.
In the top comment line of the code in post #11 it is recommended to 'Put all the code to ThisWorkbook module

 

[harveya915]

Hi Mike,
Thanks, your suggestion is more user-freandly.
Here is the implementation:

' Put all the code to ThisWorkbook module
 
Private Sub Workbook_Open()
  If GetSetting(AppName:="HarveyaApp", Section:="Startup", Key:="Ok", Default:="0") = "0" Then
    SaveSetting AppName:="HarveyaApp", Section:="Startup", Key:="Ok", setting:="1"
    ShowInstructions
  End If
End Sub
 
Sub ShowInstructions()
  ' Assign Help button/shape to this sub to see the Instructions in UserForm1 anytime
  UserForm1.Show
End_Sub
 
Private Sub RestorePopUp()
  ' For debugging purpose only - add one more auto-popup ability
  DeleteSetting AppName:="HarveyaApp", Section:="Startup", Key:="Ok"
End Sub

Vlad

I'm getting a Compile error: Ambiguous name detected: Workbook_Open
Any help with this?

 

[harveya915]

Did some reading, and it's basically saying i'm using Workbook_Open twice. I'm guessing I just have to remove the duplicate.

 

[ZVI]

Did some reading, and it's basically saying i'm using Workbook_Open twice. I'm guessing I just have to remove the duplicate.

Yes, delete the previous Workbook_Open subroutine