Greetings fellow VBAers,

I'm writing a workbook that requires the user(s) to input the file names of
other workbooks into a range of cells so that my VBA code can open and
collect data from them. I've written a bit of VBA code to return an error to
the user(s) of my workbook if they have input the file name of one of these
files incorrectly. It looks like this:

Dim USERFILE As Variant

For Each USERFILE In Range("C5:C8")
If Dir("F:\NEWESTIMATE\" & USERFILE & ".xls", vbNormal) = "" Then
MsgBox "' " & USERFILE & ".xls '" & " is not a valid estimate!",
vbOKOnly, "Error!"
End
End If
Next

Is there an excel function that can achieve the same results? I wish to
apply conditional formatting to the cells containing the file names in such
a way that if the user enters a file name, and that file does not exist, the
color of that cell (or some other visual aid,) will change to alert the
user.

Using both of these methods together would be ideal!

Any help would be greatly appreciated. :) TIA

Mike-hime.

Hi Mike-hime,

You can do this with Conditional Formatting and a custom VBA function
(User-Defined Function or UDF). Paste this function into a standard module
in the VBE:

Public Function FileExists(rsPath As String) As Boolean
On Error Resume Next
FileExists = Len(Dir$(rsPath, vbNormal))
On Error GoTo 0
End Function

Now, select the cell (let's say A1) with the filename to check. Select
Format | Conditional Formatting..., select "Formula Is" from the dropdown,
and enter the following formula:

=Not(FileExists(A1))

Now just click the Format... button and select the desired format for
invalid filenames.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Mike-hime wrote:
Greetings fellow VBAers,

I'm writing a workbook that requires the user(s) to input the file
names of other workbooks into a range of cells so that my VBA code
can open and collect data from them. I've written a bit of VBA code
to return an error to the user(s) of my workbook if they have input
the file name of one of these files incorrectly. It looks like this:

Dim USERFILE As Variant

For Each USERFILE In Range("C5:C8")
If Dir("F:\NEWESTIMATE\" & USERFILE & ".xls", vbNormal) = "" Then
MsgBox "' " & USERFILE & ".xls '" & " is not a valid
estimate!", vbOKOnly, "Error!"
End
End If
Next

Is there an excel function that can achieve the same results? I wish
to apply conditional formatting to the cells containing the file
names in such a way that if the user enters a file name, and that
file does not exist, the color of that cell (or some other visual
aid,) will change to alert the user.

Using both of these methods together would be ideal!

Any help would be greatly appreciated. :) TIA

Mike-hime.

Thanks for the help. Your suggestion was excellent :)

Now maybe you can help me something else.

While I am editing formulas in Excel if I use any navigation keys on the
keyboard, Excel inserts relative cell references automatically. (For
instance, pressing left adds a relative cell reference to the cell just left
of the active cell.) If I can't find where to turn this feature off in
Excel's options, I *WILL* kill myself. I prefer to type cell references
(Relative or Absolute,) manually and use the arrow keys to navigate while I
edit.

I'm sure I'm just over looking the check box somewhere, so I figured one of
the experienced users could probably tell me right off the top of their
head.

Thx again,

-Mike-hime

Hi Mike-hime,

F2 will toggle this behavior on and off (notice that the status bar switches
between "Edit" to "Enter" when you hit F2 while editing a cell). So if
you're editing a formula and want to use the arrow keys to move in the
formula bar, you should hit F2 first. I don't think there is a global
option to turn that feature off.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Mike-hime wrote:
Thanks for the help. Your suggestion was excellent :)

Now maybe you can help me something else.

While I am editing formulas in Excel if I use any navigation keys on
the keyboard, Excel inserts relative cell references automatically.
(For instance, pressing left adds a relative cell reference to the
cell just left of the active cell.) If I can't find where to turn
this feature off in Excel's options, I *WILL* kill myself. I prefer
to type cell references (Relative or Absolute,) manually and use the
arrow keys to navigate while I edit.

I'm sure I'm just over looking the check box somewhere, so I figured
one of the experienced users could probably tell me right off the top
of their head.

Thx again,

-Mike-hime