I need a VBA event that does the following: When I enter a value in range D6:D305 of my…
- User
I need a VBA event that does the following:
When I enter a value in range D6:D305 of my Active sheet, it checks the value of D1, then it finds the match in row B31:V31 of sheet 'Sector Budget' and checks if the value exists in the corresponding column of the range B32:V42. If the match does not exist, it pops up a message "The Subsector description is not correct" and it then clears the cell entry made in my Active Sheet. If the entry matches the conditions above then it pops up the message "A negative value will decrease the budget." & vbCrLf & "" & vbCrLf & _ "A positive value will increase the budget." & vbCrLf & "" & vbCrLf & _ "Enter value in column E" and then selects Target.Offset(0, 1)
- Assistant
You can use the Worksheet_Change event in VBA to achieve this. Here's an example code that you can use:
Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim sectorRange As Range Dim subsectorRange As Range Dim subsectorCell As Range Dim valueCell As Range Set ws = ThisWorkbook.Sheets("Sector Budget") 'Check if the changed range is D6:D305 in the active sheet If Not Intersect(Target, Me.Range("D6:D305")) Is Nothing Then 'Check if D1 has a value If Me.Range("D1").Value <> "" Then Set sectorRange = ws.Range("B31:V31") Set subsectorRange = ws.Range("B32:V42") 'Find the matching value in the row B31:V31 of sheet 'Sector Budget' Set subsectorCell = sectorRange.Find(What:=Me.Range("D1").Value, LookIn:=xlValues, LookAt:=xlWhole) 'If a match is found If Not subsectorCell Is Nothing Then Set valueCell = subsectorRange.Cells(subsectorCell.Column - subsectorRange.Column + 1) 'Check if the value exists in the corresponding column of the range B32:V42 If WorksheetFunction.CountIf(valueCell, Me.Range("D6:D305").Value) = 0 Then 'If match does not exist, show message and clear cell entry MsgBox "The Subsector description is not correct" Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True Else 'If entry matches the conditions, show message and select Target.Offset(0, 1) MsgBox "A negative value will decrease the budget." & vbCrLf & "" & vbCrLf & _ "A positive value will increase the budget." & vbCrLf & "" & vbCrLf & _ "Enter value in column E" Target.Offset(0, 1).Select End If End If End If End If End SubTo add this event to your worksheet, follow these steps:
- Press ALT + F11 to open the VBA editor.
- In the Project Explorer pane, find your workbook and expand it.
- Double-click on the sheet where you want to add the event code.
- Paste the above code into the code window.
- Close the VBA editor.
Now, whenever you enter a value in the range D6:D305 of the active sheet, the event code will be triggered and perform the checks you mentioned.
- User
I need a VBA event that does the following:
When I enter a text value in range D6:D305 of my Active sheet, it checks the value of D1 and then it finds the first match in row B31:V31 of sheet 'Sector Budget'.
When the first match is found, it then checks down the specific column where the first match was found for the value entered in range D6:D305 of my Active sheet, this is the second match.
If the second match is not found, it pops up a message "The Subsector description is not correct" and it then clears the cell entry made in my Active Sheet.
If the second matche is found, then it pops up the message "A negative value will decrease the budget." & vbCrLf & "" & vbCrLf & _ "A positive value will increase the budget." & vbCrLf & "" & vbCrLf & _ "Enter value in column E" and then selects Target.Offset(0, 1)
- Assistant
Apologies for the confusion. Here's an updated code that should meet your requirements:
Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim sectorRange As Range Dim subsectorRange As Range Dim subsectorCell As Range Dim valueCell As Range Dim firstMatch As Range Dim secondMatch As Range Set ws = ThisWorkbook.Sheets("Sector Budget") 'Check if the changed range is D6:D305 in the active sheet If Not Intersect(Target, Me.Range("D6:D305")) Is Nothing Then 'Check if D1 has a value If Me.Range("D1").Value <> "" Then Set sectorRange = ws.Range("B31:V31") Set subsectorRange = ws.Range("B32:V42") 'Find the first matching value in the row B31:V31 of sheet 'Sector Budget' Set firstMatch = sectorRange.Find(What:=Me.Range("D1").Value, LookIn:=xlValues, LookAt:=xlWhole) 'If a first match is found If Not firstMatch Is Nothing Then Set valueCell = subsectorRange.Cells(firstMatch.Column - subsectorRange.Column + 1) 'Check if the value exists in the corresponding column of the range B32:V42 Set secondMatch = valueCell.Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole) If secondMatch Is Nothing Then 'If second match does not exist, show message and clear cell entry MsgBox "The Subsector description is not correct" Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True Else 'If second match is found, show message and select Target.Offset(0, 1) MsgBox "A negative value will decrease the budget." & vbCrLf & "" & vbCrLf & _ "A positive value will increase the budget." & vbCrLf & "" & vbCrLf & _ "Enter value in column E" Target.Offset(0, 1).Select End If End If End If End If End SubPlease follow the same steps provided earlier to add this event to your worksheet.
Now, when you enter a text value in the range D6:D305 of the active sheet, the event code will be triggered and perform the checks as you mentioned. It will find the first match in row B31:V31 of the 'Sector Budget' sheet and then check down the specific column for the second match. The rest of the logic remains the same as before.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.