Can you open an external wb, retrieve a drop down menu then close it

[dpaton05]

I have a wb with a drop down menu that correctly references an external wb to get the list but the external wb needs to be open to do so. It is going to be used by more than one person and will be stored on a server. Is there a way to have the external wb open when the cell with the list is selected, retrieve the drop down list then close it? I can't have it open all the time as it stores additional information that could be added to by another user and might be accidentally saved over.

Thanks

 

[dpaton05]

I thought of a possible solution but I don't know if it will work or how to get it to work.

The idea was that I put code into the change event for the worksheet:

    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("B7")) Is Nothing Then
            Workbooks.Open ThisWorkbook.Path & "\" & "Client_list.xlsm"
        End If
    End If

The client_list file is stored in the same folder and the drop down list is in B7, this code wouldn't open the file upon selection of B7. Could someone help me with the vba please?

 

[Yongle]

Try this combination of event macros

[COLOR=#006400]Private Sub Worksheet_SelectionChange[/COLOR](ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("B7")) Is Nothing Then OpenClientList
    End If
[COLOR=#006400]End Sub[/COLOR]
[COLOR=#006400]Private Sub Worksheet_Chang[/COLOR]e(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("B7")) Is Nothing Then CloseClientList
    End If
[COLOR=#006400]End Sub[/COLOR]
[COLOR=#006400]Private Sub Worksheet_BeforeRightClick[/COLOR](ByVal Target As Range, Cancel As Boolean)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("B7")) Is Nothing Then
            Cancel = True
            CloseClientList
        End If
    End If
[COLOR=#006400]End Sub[/COLOR]
[COLOR=#006400]Private Sub OpenClientList[/COLOR]()
    On Error Resume Next
    Workbooks.Open ThisWorkbook.Path & "\" & "Client_list.xlsm"
    Me.Parent.Activate
[COLOR=#006400]End Sub[/COLOR]
[COLOR=#006400]Private Sub CloseClientList[/COLOR]()
    On Error Resume Next
    Application.ScreenUpdating = False
    Workbooks("Client_list.xlsm").Close False
[COLOR=#006400]End Sub[/COLOR]

Explanation
Selecting the cell opens the workbook
Changing the value closes the workbook
Before Right Click event prevents right-clickng the cell triggering the selection macro and gives a method to close the workbook if value in cell is not amended
I prefer event macros to be "clean" of detail
- may also want to use them for other things later and they become very cluttered
- hence separate macros for opening and cloing the workbooks
On Error Resume Next prevents VBA failing if workbook not found


Q Is the data validation list derived from a static range of cells in Client_List ?

 

[Yongle]

If the data validation list is derived from a static range of cells in Client_List ...
... consider using a formula to get the values from Client_List as illustrated below

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td]Formula in A2 copied down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Ant[/td][td] ='C:\Folder\SubFolder\[Client_list.xlsm]MySheet'!$A1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Cat[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Dog[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Mouse[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Hampster[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Cow[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Sheep[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Lamb[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]Goat[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Elephant[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]