Storing A Variable on Close

[poleary2000]

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?

[poleary2000]

I need to do this ASAP. Any ideas?

[Tom Schreiner]

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

[poleary2000]

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].

[Jay Petrulis]

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

[Ricky Morris]

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.

[Tom Schreiner]

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

[poleary2000]

Tom,

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

Thanks,
Patrick

[Tom Schreiner]

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

[poleary2000]

So will it work if the user moves the file to a different directory.