Cell Validation to External Worksheet

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 19, 2007

• #2

Look here:

http://www.contextures.com/xlDataVal05.html

 

Upvote 0

W

wrightr

New Member

Joined

Sep 13, 2006

Messages

16

• Jun 19, 2007

• #3

Thats great thanks.

How do I get the workbook to always be open - do I have to add code to get this done?

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 19, 2007

• #4

You could put some code to open it in the Workbook_Open event procedure of the workbook containing the data validation.

 

Upvote 0

W

wrightr

New Member

Joined

Sep 13, 2006

Messages

16

• Jun 19, 2007

• #5

Thanks Andrew

Don't suppose there is a standard bit of code for this I could use?

Thanks

Rick

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 19, 2007

• #6

Not exactly standard, but this worked for me:

Code:

Private Sub Workbook_Open()
    Dim Source As String
    Dim Pos As Integer
    Dim SourceName As String
    Source = ThisWorkbook.Names("MyList").RefersTo
    If InStr(1, Source, ":") > 0 Then
        Pos = InStr(1, Source, "!")
        SourceName = WorksheetFunction.Substitute(Left(Source, Pos - 1), "'", "")
        Workbooks.Open Right(SourceName, Len(SourceName) - 1)
        ThisWorkbook.Activate
    End If
End Sub

The code assumes you used the name MyList in your Data Validation. Change to suit.

 

Upvote 0

W

wrightr

New Member

Joined

Sep 13, 2006

Messages

16

• Jun 19, 2007

• #7

Andrew

Thanks for your help on this but can't get it to work.

Is there code I could put in the template that requires the validation (not the one holding the validation) that automatically opens the validation file and then closes it when the workbook is closed?

Thanks again for the help

Rick

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 19, 2007

• #8

In what way didn't it work? The code I posted goes in the ThisWorkbook module of the workbook that contains the data validation.

 

Upvote 0

W

wrightr

New Member

Joined

Sep 13, 2006

Messages

16

• Jun 19, 2007

• #9

Andrew

Apologies, added the text to the wrong section.

I take it that I can now hide this file and then get the file to close on shutdown?

Many thanks

Rick

 

Upvote 0