Storing A Variable on Close

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
I have code that runs upon opening. The code opens a UserForm that asks the user a question. I would like to have an option to turn the UserForm on or off WITHOUT storing a value in a worksheet. I guess I would like to have a check box like to have a check box on the UserForm that asks the user if they would like to see the form on Startup. Can this be done?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
As far as I can tell, you are going to need to store a value somewhere...
Why are you against storing a value on the workbook itself?
Place checkbox1 with the caption "Do not show this again."
Place a value of true on some forsaken range somewhere and then check the value of this cell to determine if the form will show or not...
If for some reason you cannot use the workbook, use a text file or the registry if you know how...

Tom
 
Upvote 0
It is hard to explain the reason. The particular code that is running does not compare the values on any sheet currently. Plus, at anytime the user could delete any worksheet in the book so I have no idea where to store it [because it could get deleted].

I would rather not use the registry [don't know how anyway].
 
Upvote 0
Would storing the value to a Name work?

In a workbook_before close event you can do something like:

X = 34
Names.Add Name:="StoredNum", RefersTo:=X

You don't have to hardcode X; you can get/calculate/increment it from somewhere else.

Then, your UserForm can refer to "StoredNum" to do what you want.

HTH,
Jay
 
Upvote 0
Try saving in a worksheet that's very hidden (it can't be unhide via worksheet menu, only by VBA). Or you could use a text file.
 
Upvote 0
Ok
That makes sense...
You could create a hidden worksheet...
If you think that is too much overhead then perhaps some code like this will do...

'call the sub from workbook open
Private Sub Workbook_Open()
CheckFileSwitch
End Sub

'open noopen
'if it does not exist, will generate Error 53
'goto ShowForm and show your form
Sub CheckFileSwitch()
On Error GoTo ShowForm
Open Application.Path & "/noopen" For Input As #1
Close #1
Exit Sub
ShowForm:
If Err.num = 53 Then 'file not found
Err.Clear
Close #1
UserForm1.Show
End If
End Sub


'on your userform place a checkbox
'with the caption "Do not show this again"
'if the user checks the checkbox
'the file noopen will be created
'and the form will no longer show
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Open Application.Path & "/noopen" For Output As #1
Close #1
End If
End Sub

Seems a bit lengthy, but the code is quick
Try it out.
Tom
 
Upvote 0
Tom,

Does this actually create a file? I'm not sure I completely understand it but I'll try it.

Thanks,
Patrick
 
Upvote 0
Yes
A blank file with no extension
It holds no value
If it exists, the form will not show
Visa versa.
I might have the wrong slash / or
I'm always getting them mixed up
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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