Do not save a checkbox value when saving or close

[TimE]

I have a message popup each time sheet1 is activated. The user can check a box to stop the message when coming back to sheet1, but I do not want the value saved when the spreadsheet is saved/closed. This way, the message is controllable during the current "session". I need some help with the clearing of (not saving?) the checkbox when closed.

BTW, it is a shared spreadsheet. Any ideas?


Thanks for any assistance.

[Dave Peterson]

You could add something to the auto_open (or workbook_open) routine that
unchecks the checkbox.

Option Explicit
Sub auto_open()

'from Forms toolbar
Worksheets("sheet1").CheckBoxes("check box 1").Value = xlOff

'from Control toolbox toolbar
Worksheets("sheet1").CheckBox1.Value = False

End Sub

Change the worksheet name and the checkbox name to match what you need.


TimE wrote:
>
> I have a message popup each time sheet1 is activated. The user can
> check a box to stop the message when coming back to sheet1, but I do
> not want the value saved when the spreadsheet is saved/closed. This
> way, the message is controllable during the current "session". I need
> some help with the clearing of (not saving?) the checkbox when closed.
>
> BTW, it is a shared spreadsheet. Any ideas?
>
> Thanks for any assistance.
>
> --
> TimE
> ------------------------------------------------------------------------
> TimE's Profile: http://www.excelforum.com/member.php...fo&userid=2139
> View this thread: http://www.excelforum.com/showthread...hreadid=512397

--

Dave Peterson

[TimE]

Thank you for your response. I have decided to use a txt file based on the logged on user to store the preference. The file is created/updated when the checkbox is checked/unchecked. My only problem is when the txt file does not exist when the spreadsheet is first opened. I do not know how to handle the error and continue.

Sample code:

Private Sub Workbook_Open()
Sheets("Schedule").Select
ThisFile = "{UNC}\" & Environ("username") & ".txt"
Open ThisFile For Input As #1
Input #1, sValue
Sheet1.CheckBox1.value = sValue
Close #1
End Sub


Any thoughts?

[Dave Peterson]

Maybe...


Private Sub Workbook_Open()
Sheets("Schedule").Select
ThisFile = "{UNC}\" & Environ("username") & ".txt"
if dir(thisfile) = "" then
'do nothing, doesn't exist
'or turn it off/on
else
Open ThisFile For Input As #1
Input #1, sValue
Sheet1.CheckBox1.value = sValue
Close #1
end if
End Sub

TimE wrote:
>
> Thank you for your response. I have decided to use a txt file based on
> the logged on user to store the preference. The file is
> created/updated when the checkbox is checked/unchecked. My only
> problem is when the txt file does not exist when the spreadsheet is
> first opened. I do not know how to handle the error and continue.
>
> Sample code:
>
> Private Sub Workbook_Open()
> Sheets("Schedule").Select
> ThisFile = "{UNC}\" & Environ("username") & ".txt"
> Open ThisFile For Input As #1
> Input #1, sValue
> Sheet1.CheckBox1.value = sValue
> Close #1
> End Sub
>
> Any thoughts?
>
> --
> TimE
> ------------------------------------------------------------------------
> TimE's Profile: http://www.excelforum.com/member.php...fo&userid=2139
> View this thread: http://www.excelforum.com/showthread...hreadid=512397

--

Dave Peterson

[TimE]

Thank you Dave, it worked. If you have a spare minute, can you help me with something else or point me in the right direction?

I would like to expand the preference file to more than one value and of course be able to read the value when the spreadsheet is opened.

The problem I am facing is how to include what the value is for and then how to "find" it again.

[Dave Peterson]

Take a look at almost any .INI file

You'll see that there are keys like:
[Mail]
MAPI=1
CMCDLLNAME32=mapi32.dll
CMCDLLNAME=mapi.dll
CMC=1
MAPIX=1
MAPIXVER=1.0.0.1

(from my Win.INI file)

If you layout the record the same way, you could read each record and parse it
out.

or...

A little search of google will give you lots of hits:

http://groups.google.co.uk/group/mic...63099721c94c8e

or

http://snipurl.com/f0iz

TimE wrote:
>
> Thank you Dave, it worked. If you have a spare minute, can you help me
> with something else or point me in the right direction?
>
> I would like to expand the preference file to more than one value and
> of course be able to read the value when the spreadsheet is opened.
>
> The problem I am facing is how to include what the value is for and
> then how to "find" it again.
>
> --
> TimE
> ------------------------------------------------------------------------
> TimE's Profile: http://www.excelforum.com/member.php...fo&userid=2139
> View this thread: http://www.excelforum.com/showthread...hreadid=512397

--

Dave Peterson