Hi guys,
I've got a double click event which I'm having issues with...
Background:
I have a worksheet which displays sets of data based on values that have been selected from either of 2 combo boxes (Responsible and Reports to). I then have code which allows the user to double click on certain cells (which contain the name of a Responsible person / the person they are reporting to) which will then display the other set of data based on the range that is double clicked.
I.e. if a responsible persons data is shown and you double click on the person they are reporting to's name then it will take you to their teams data and vice versa
It is set up so that it recognizes which set of data is being displayed and whether you have selected a valid range/name
The problem is that when you double click a blank cell (say by accident) and then select a valid cell with a name in it which would normally run the beforedoubleclick event it doesn't trigger.
I've included my code below and would really appreciate some help!
'This will run before a double click
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Turns off enableevents
Application.EnableEvents = False
Application.ScreenUpdating = False
'elseif the left 2 characters of the selections address is $B and the font of A5 is bold and the right 2 characters of the selections address is greater than 11 and the selection is not empty then
If Left(Selection.Address, 2) = "$B" And Range("A5").Font.Bold = True And Right(Selection.Address, 2) > 11 And Not IsEmpty(Selection.Value) Then
Cancel = True
ActiveSheet.AutoFilterMode = False
'set the variable selection1 equal to the selections value
Selection1 = Selection.Value
'Count the total number of entries in column C + 7
TotalRows = WorksheetFunction.CountA(Columns("C")) + 7
'Clears any existing Data and formatting
Range("B11:N" & TotalRows).Clear
Range("B11:N" & TotalRows).ClearFormats
'sets the text of A4:B4 bold
Range("A4:B4").Font.Bold = True
'sets the text of A5:B5 not bold
Range("A5:B5").Font.Bold = False
'Sets combobox2's selected value to the first value in the list
Sheets("RIO Review").ComboBox2.ListIndex = 0
'Sets the selection of combobox1 to the variable selection1
Sheets("RIO Review").ComboBox1.Value = Selection1
'sets the text of A4:B4 bold
Range("A4:B4").Font.Bold = True
'sets the text of A5:B5 not bold
Range("A5:B5").Font.Bold = False
'Runs the responsibleRIO macro (Module 2)
ResponsibleRIO
'else if the left 2 characters of the selections address is $B and the right of the selections address is 10 and the selection is not empty then
ElseIf Left(Selection.Address, 2) = "$B" And Right(Selection.Address, 2) = "10" And Not IsEmpty(Selection.Value) Then
Cancel = True
ActiveSheet.AutoFilterMode = False
'Set the variable selection1 equal to the selection value
Selection1 = Selection.Value
'Count the total number of entries in column C + 7
TotalRows = WorksheetFunction.CountA(Columns("C")) + 7
'Clears any existing data and formatting
Range("A10:N" & TotalRows).Clear
Range("A10:N" & TotalRows).ClearFormats
'sets the text of A4:B4 not bold
Range("A4:B4").Font.Bold = False
'sets the text of A5:B5 bold
Range("A5:B5").Font.Bold = True
'Sets combobox1's selected value to the first value in the list
Sheets("RIO Review").ComboBox1.ListIndex = 0
'Sets the selection of combobox1 to the variable selection1
Sheets("RIO Review").ComboBox2.Value = Selection1
'sets the text of A4:B4 not bold
Range("A4:B4").Font.Bold = False
'sets the text of A5:B5 bold
Range("A5:B5").Font.Bold = True
'Runs the ReportingRIO macro (Module 2)
ReportingRIO
'If the cell that has been double clicked is empty then end
'If Selection.Value = "" Or IsEmpty(Selection) Then
'MsgBox "Please select a valid user"
'Exit Sub
'Cancel = True
'end if
ElseIf Range("B5").Font.Bold = True Then
Cancel = False
ReportingRIO
ElseIf Range("B4").Font.Bold = True Then
Cancel = False
ResponsibleRIO
End If
End Sub
Bookmarks