Is this the correct way of writing this code: Private Sub…
- User
Is this the correct way of writing this code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EnableEvents = False Dim doubleClickedCell As Range Set doubleClickedCell = Target.Cells(1) ''''If Target.CountLarge > 1 Then Exit Sub ''''If Target.Column = 4 Then ' Column D ''''PSJulAug Target ''''previousValue = doubleClickedCell.Value ''''doubleClickFlag = True ''''End If
If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("D3:D25")) Is Nothing Then ' Limit range to D3:D25 If Target.Column = 4 Then ' Column D previousValue = doubleClickedCell.Value doubleClickFlag = True PSJulAug Target End If End If
If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("D28:D50")) Is Nothing Then ' Limit range to D28:D50 If Target.Column = 4 Then ' Column D previousValue = doubleClickedCell.Value doubleClickFlag = True PSSepOct Target End If End If
If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("D53:D75")) Is Nothing Then ' Limit range to D53:D75 If Target.Column = 4 Then ' Column D previousValue = doubleClickedCell.Value doubleClickFlag = True PSNov Target End If End If
If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("D78:D100")) Is Nothing Then ' Limit range to D78:D100 If Target.Column = 4 Then ' Column D previousValue = doubleClickedCell.Value doubleClickFlag = True PSDecJan Target End If End If
If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("D103:D125")) Is Nothing Then ' Limit range to D103:D125 If Target.Column = 4 Then ' Column D previousValue = doubleClickedCell.Value doubleClickFlag = True PSFeb Target End If End If
If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("D128:D150")) Is Nothing Then ' Limit range to D128:D150 If Target.Column = 4 Then ' Column D previousValue = doubleClickedCell.Value doubleClickFlag = True PSMarApr Target End If End If
If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("D153:D175")) Is Nothing Then ' Limit range to D153:D175 If Target.Column = 4 Then ' Column D previousValue = doubleClickedCell.Value doubleClickFlag = True PSMayJun Target End If End If
Application.EnableEvents = True
End Sub
- Assistant
The code appears to be mostly correct. However, there are a few things that could be improved:
-
The commented-out section at the beginning does not serve any purpose and can be removed.
-
Instead of repeating the same code for each range, you can create a loop and store the ranges in an array. This will make the code more concise and easier to maintain.
-
It is a good practice to include error handling in case any unexpected errors occur. This can be done using the
On Errorstatement.
Here is an improved version of the code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EnableEvents = False Dim doubleClickedCell As Range Set doubleClickedCell = Target.Cells(1) On Error GoTo ErrorHandler Dim rangesToCheck As Variant rangesToCheck = Array("D3:D25", "D28:D50", "D53:D75", "D78:D100", "D103:D125", "D128:D150", "D153:D175") Dim i As Long For i = LBound(rangesToCheck) To UBound(rangesToCheck) If Not Intersect(Target, Range(rangesToCheck(i))) Is Nothing And Target.Column = 4 Then previousValue = doubleClickedCell.Value doubleClickFlag = True Select Case i Case 0 PSJulAug Target Case 1 PSSepOct Target Case 2 PSNov Target Case 3 PSDecJan Target Case 4 PSFeb Target Case 5 PSMarApr Target Case 6 PSMayJun Target End Select Exit For End If Next i ErrorHandler: Application.EnableEvents = True End SubNote: You will need to make sure that the
PSJulAug,PSSepOct, etc. procedures are defined and implemented correctly elsewhere in your code. -