Enable Option Button if File Exists

[E.Q.]

I have employees that open up five monthly reports each night after midnight
numbers are collected for the day. The reports names have the format of
yy-mmrpt.xls. So for example two reports for Jan '08 would be 08-01tss.xls
and 08-01po4.xls. We have reports dating back to 2001.
I amended the userform used to select the files by adding a spinner to
select the year and gave it a range of 2001 - current year + 1. The form's
Activate event initializes the spinner to the current year and sets the
spinner max. (Before adding the spinner, someone needed to enter VBA and
change a string variable to select the year.) The month and report id are
picked by radio buttons and check boxes respectively
I need to add some error handling code for this (they've created a set of
files for 2008, but the spinner will choose future files that have yet to be
created after new year's day).
I've thought a simple msgbox "file not found" in the error code would work,
but a more elegant solution would be to check which files exists and disable
the check box for any report if no report exist for the implied month (via
radio button) and year (via spinner). I actually have a similar problem for
earlier years... the first few years we had four reports. So I have a
07-01SHF.xls file but I don't have a 04-01SHF.xls file.

So in short, I'd like to find out if a file exists via VBA and prevent the
user from asking for it if it ain't there.
Any suggestions would be appreciated.
Peace.
EQ

 

[Chip Pearson]

I got lost in your explanation, but you can use the Dir function to
determine if a file exists:

Me.SpinButton1.Enabled = (Dir("C:\YourFilename.xls") <> vbNullString)

This code will enable SpinButton1 if the file "C:\YourFileName.xls" exists.
If the file does not exist, the spinner is disabled. You can use wildcards
in the file name, such as "C:\ABC*.xls". This will match a file name that
begins with 'ABC' and is followed by zero or more characters and has an
'.xls' extension.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[E.Q.]

Sorry if the explanation was a bit wordy, but you managed to answer the
question perfectly... The user form works exactly as I hoped it would after
using the DIR funtion as you suggested.
Thank you Very Much.
Peace.
EQC