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

Can you write a vba code that can do the following. If I double click a cell in column E…

  1. User

    Can you write a vba code that can do the following. If I double click a cell in column E from E6 downwards, If it does not contain a value then exit sub If the value is negative, for example -123 then exit sub If the value is positive, for example 123 then pop up a Yes No message' "To record this payment of " & show the cell value, line break "and deduct the value from the budget" line break "click Yes to accept & No to leave as is" vbInformation, "RECORD PAYMENT" Then if I click Yes, add a negative sign '-' to the front of the value in the cell.

  2. Assistant

    Sure, here is a VBA code that you can use for the described scenario:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim response As VbMsgBoxResult
        
        If Not Intersect(Target, Me.Range("E6:E" & Me.Cells(Me.Rows.Count, "E").End(xlUp).Row)) Is Nothing Then
            If Target.Value = "" Then
                Exit Sub
            ElseIf Target.Value < 0 Then
                Exit Sub
            ElseIf Target.Value > 0 Then
                response = MsgBox("To record this payment of " & Target.Value & vbCrLf & _
                                  "and deduct the value from the budget" & vbCrLf & _
                                  "click Yes to accept & No to leave as is", vbInformation + vbYesNo, "RECORD PAYMENT")
                If response = vbYes Then
                    Target.Value = -1 * Target.Value
                End If
            End If
        End If
    End Sub
    

    You can add this code to the worksheet module where you want this functionality to work. Double-clicking on any cell in column E from E6 downwards will trigger this code to check the conditions and display the message box accordingly. If you click Yes, it will add a negative sign to the value in the cell.

  3. User

    I forgot to add a requirement to the code. If Taget.Offset(0, 4) does not contain a date, then pop up a message "No Completion Date" and also exit sub

  4. Assistant

    No problem! Here is the updated VBA code with the additional requirement included:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim response As VbMsgBoxResult
        
        If Not Intersect(Target, Me.Range("E6:E" & Me.Cells(Me.Rows.Count, "E").End(xlUp).Row)) Is Nothing Then
            If Target.Value = "" Or Target.Offset(0, 4).Value = "" Then
                MsgBox "No Completion Date", vbExclamation
                Exit Sub
            ElseIf Target.Value < 0 Then
                Exit Sub
            ElseIf Target.Value > 0 Then
                response = MsgBox("To record this payment of " & Target.Value & vbCrLf & _
                                  "and deduct the value from the budget" & vbCrLf & _
                                  "click Yes to accept & No to leave as is", vbInformation + vbYesNo, "RECORD PAYMENT")
                If response = vbYes Then
                    Target.Value = -1 * Target.Value
                End If
            End If
        End If
    End Sub
    

    Now, the code will check if the cell 4 columns to the right of the double-clicked cell contains a date. If it does not contain a date, it will display a message "No Completion Date" and exit the sub without further action.

  5. User

    Can you please seperat targetValue = "" and Target.Offset(0, 4).Value = “” because the message only applies to Target.Offset(0, 4).Value = “”

  6. Assistant

    Certainly! Here is the updated VBA code with the condition for Target.Offset(0, 4).Value = "" separated from the condition for Target.Value = "":

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim response As VbMsgBoxResult
        
        If Not Intersect(Target, Me.Range("E6:E" & Me.Cells(Me.Rows.Count, "E").End(xlUp).Row)) Is Nothing Then
            If Target.Value = "" Then
                Exit Sub
            ElseIf Target.Offset(0, 4).Value = "" Then
                MsgBox "No Completion Date", vbExclamation
                Exit Sub
            ElseIf Target.Value < 0 Then
                Exit Sub
            ElseIf Target.Value > 0 Then
                response = MsgBox("To record this payment of " & Target.Value & vbCrLf & _
                                  "and deduct the value from the budget" & vbCrLf & _
                                  "click Yes to accept & No to leave as is", vbInformation + vbYesNo, "RECORD PAYMENT")
                If response = vbYes Then
                    Target.Value = -1 * Target.Value
                End If
            End If
        End If
    End Sub
    

    Now, when you double click on a cell in column E from E6 downwards, the code will first check if the completion date in the cell that is 4 columns to the right of the double-clicked cell is empty. If it is empty, it will display the message "No Completion Date" and exit the sub.

  7. User

    Is there a way of making the 'Target.Value = -1 * Target.Value' record as a change to the cell because there is a code in the Worksheet_Cahnge event that it needs to trigger

  8. Assistant

    To record the change made to the cell within the Worksheet_BeforeDoubleClick event and trigger the Worksheet_Change event, you can utilize the Application.Undo method after updating the value of the cell. This will essentially undo the change made by the event, but it will trigger the Worksheet_Change event with the new value. Here's how you can modify the code to achieve this:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim response As VbMsgBoxResult
        Dim newValue As Variant
        
        If Not Intersect(Target, Me.Range("E6:E" & Me.Cells(Me.Rows.Count, "E").End(xlUp).Row)) Is Nothing Then
            If Target.Value = "" Then
                Exit Sub
            ElseIf Target.Offset(0, 4).Value = "" Then
                MsgBox "No Completion Date", vbExclamation
                Exit Sub
            ElseIf Target.Value < 0 Then
                Exit Sub
            ElseIf Target.Value > 0 Then
                response = MsgBox("To record this payment of " & Target.Value & vbCrLf & _
                                  "and deduct the value from the budget" & vbCrLf & _
                                  "click Yes to accept & No to leave as is", vbInformation + vbYesNo, "RECORD PAYMENT")
                If response = vbYes Then
                    Application.EnableEvents = False
                    newValue = -1 * Target.Value
                    Target.Value = newValue
                    Application.Undo
                    Target.Value = newValue
                    Application.EnableEvents = True
                End If
            End If
        End If
    End Sub
    

    In this updated code, when the user clicks "Yes" in the message box, the new value is temporarily stored in newValue. Then, the event handling is temporarily disabled using Application.EnableEvents = False. The Target.Value is updated to the new value, followed by using Application.Undo to trigger the Worksheet_Change event with the updated value. Finally, the Target.Value is set again to newValue, and event handling is re-enabled.

    This will allow the change to the cell value to trigger the Worksheet_Change event with the updated value.

  9. User

    error on Application.Undo

  10. Assistant

    I apologize for the oversight. The Application.Undo method cannot be used in this context to trigger the Worksheet_Change event. Instead, we can directly call the Worksheet_Change event subroutine after updating the cell value. Here's how you can modify the code to achieve this:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim response As VbMsgBoxResult
        Dim oldValue As Variant
        Dim newValue As Variant
        
        If Not Intersect(Target, Me.Range("E6:E" & Me.Cells(Me.Rows.Count, "E").End(xlUp).Row)) Is Nothing Then
            If Target.Value = "" Then
                Exit Sub
            ElseIf Target.Offset(0, 4).Value = "" Then
                MsgBox "No Completion Date", vbExclamation
                Exit Sub
            ElseIf Target.Value < 0 Then
                Exit Sub
            ElseIf Target.Value > 0 Then
                response = MsgBox("To record this payment of " & Target.Value & vbCrLf & _
                                  "and deduct the value from the budget" & vbCrLf & _
                                  "click Yes to accept & No to leave as is", vbInformation + vbYesNo, "RECORD PAYMENT")
                If response = vbYes Then
                    Application.EnableEvents = False
                    oldValue = Target.Value
                    newValue = -1 * oldValue
                    Target.Value = newValue
                    Worksheet_Change Target
                    Target.Value = newValue
                    Application.EnableEvents = True
                End If
            End If
        End If
    End Sub
    

    In this updated code, after updating the cell value with the new value, the Worksheet_Change event is manually triggered by calling Worksheet_Change Target with the updated cell as the argument. This should allow the change to the cell value to trigger the Worksheet_Change event with the updated value.