Try this combination of event macros
Code:
[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 ?
