External data validation

[Shane]

I want to use external data validation. I know how to do it. What I
want to do is have a macro that automatically tells the user that the
external workbook needs to be open and will then open it. Does anyone
know how to do this?

Do I use a change event on the validation error?

Thanks in advance.

Shane

 

[Guest]

Hi shane,

One way is to attempt to activate the required workbook and if you get an
error then goto a routine to open it.

The newly opened workbook becomes the active one.

If workbook to open is not in the default path then you will need to add the
path in the wbOpen routine. Record a macro to open it will give you the
required syntax.

Example:

Sub Macro1()

Dim wbName As String

wbName = "Book2.xlsm"

On Error GoTo wbOpen
Windows(wbName).Activate
On Error GoTo 0

MsgBox "Workbook " & ActiveWorkbook.Name & " is open"

GoTo skipwbOpen 'When get to here skip wbOpen

wbOpen:
Workbooks.Open Filename:= _
"Book2.xlsm"
Resume Next

skipwbOpen:

End Sub

Hope it helps.

Regards,

OssieMac