Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

Is this the correct way of writing this code: Private Sub…

  1. 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

  2. Assistant

    The code appears to be mostly correct. However, there are a few things that could be improved:

    1. The commented-out section at the beginning does not serve any purpose and can be removed.

    2. 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.

    3. It is a good practice to include error handling in case any unexpected errors occur. This can be done using the On Error statement.

    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 Sub
    

    Note: You will need to make sure that the PSJulAug, PSSepOct, etc. procedures are defined and implemented correctly elsewhere in your code.