Can you write a vba code that can do the following. If I double click a cell in column E…
- 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.
- 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 SubYou 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.
- 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
- 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 SubNow, 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.
- User
Can you please seperat targetValue = "" and Target.Offset(0, 4).Value = “” because the message only applies to Target.Offset(0, 4).Value = “”
- Assistant
Certainly! Here is the updated VBA code with the condition for
Target.Offset(0, 4).Value = ""separated from the condition forTarget.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 SubNow, 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.
- 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
- Assistant
To record the change made to the cell within the
Worksheet_BeforeDoubleClickevent and trigger theWorksheet_Changeevent, you can utilize theApplication.Undomethod after updating the value of the cell. This will essentially undo the change made by the event, but it will trigger theWorksheet_Changeevent 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 SubIn 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 usingApplication.EnableEvents = False. TheTarget.Valueis updated to the new value, followed by usingApplication.Undoto trigger theWorksheet_Changeevent with the updated value. Finally, theTarget.Valueis set again tonewValue, and event handling is re-enabled.This will allow the change to the cell value to trigger the
Worksheet_Changeevent with the updated value. - User
error on Application.Undo
- Assistant
I apologize for the oversight. The
Application.Undomethod cannot be used in this context to trigger theWorksheet_Changeevent. Instead, we can directly call theWorksheet_Changeevent 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 SubIn this updated code, after updating the cell value with the new value, the
Worksheet_Changeevent is manually triggered by callingWorksheet_Change Targetwith the updated cell as the argument. This should allow the change to the cell value to trigger theWorksheet_Changeevent with the updated value.