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

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

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

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

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

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