JohnGow383
Board Regular
- Joined
- Jul 6, 2021
- Messages
- 141
- Office Version
- 2013
- Platform
- Windows
Hi. I have a private sub Worksheet_Change(ByVal Target As Range) looking at the last value in a column range which fires other macros when various cases are met. This works fine. I am now trying to add another set of cases to a different cell to fire when the text in that cell (generated by Excel formula) matches the new cases. I am having difficutly getting it to work and think I am definetely screwing up somewhere. Here is the working code without the new code:
The above works fine.
What I'd like to do is have another set of events fire a set of macros with the following details:
Trigger Cell is G1 so when G1 = the following cases:
Case 1 = "Distilled Water Consumption is High. Please Give a Reason in the Message Box" to call Macro1
Case 2 = "Domestic Water Consumption is High. Please Give a Reason in the Message Box" to call Macro2
Case 3 = "Domestic & Distilled Water Consumption is High. Please Give a Reason in the Message Box" to call Macro3
I am trying to add the following code before Range("j13").Select just for the first case, but it's not working:
What am I doing wrong? Thanks
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim triggercells As Range, lrow As Integer
Set triggercells = Range("M4:M53")
If Not Application.Intersect(triggercells, Range(Target.Address)) Is Nothing Then
lrow = Cells(Rows.Count, "M").End(xlUp).Row
If Target.Row = lrow Then
Application.EnableEvents = False
Select Case Target.Value
Case "NOON in PORT", "NOON in TRANS", "ROP", "ROP2"
Range("I5,E4:F4,E5:F5").ClearContents
Case Else
' No Action Required
End Select
On Error Resume Next
Range("D22").Comment.Delete
Range("D23").Comment.Delete
Range("D25").Comment.Delete
Range("D26").Comment.Delete
On Error GoTo 0
Dim cmtCell As Range
Select Case Target.Value
Case "SOP"
Set cmtCell = Range("D22")
Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
Call TalkSOP
Case "ROP"
Set cmtCell = Range("D23")
Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
Call TalkROP
Case "SOP2"
Set cmtCell = Range("D25")
Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
Call TalkSOP2
Case "ROP2"
Set cmtCell = Range("D26")
Call AddAndFmtComment(rCell:=cmtCell, RegType:=Target.Value)
Call TalkROP2
Case "NOON in PORT"
Call TalkNOONinPORT
Case "NOON at SEA"
Call TalkNOONatSEA
Case "NOON in TRANS"
Call TalkNOONinTRANS
Case "EOP"
Call TalkEOP
Case "FAOP"
Call TalkFAOP
Case Else
' Comment already deleted as initialisation step
End Select
Range("j13").Select
Application.EnableEvents = True
End If
End If
End Sub
What I'd like to do is have another set of events fire a set of macros with the following details:
Trigger Cell is G1 so when G1 = the following cases:
Case 1 = "Distilled Water Consumption is High. Please Give a Reason in the Message Box" to call Macro1
Case 2 = "Domestic Water Consumption is High. Please Give a Reason in the Message Box" to call Macro2
Case 3 = "Domestic & Distilled Water Consumption is High. Please Give a Reason in the Message Box" to call Macro3
I am trying to add the following code before Range("j13").Select just for the first case, but it's not working:
VBA Code:
Dim WaterCell As Range
If Not Application.Intersect(WaterCell, Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case "Distilled Water Consumption is High. Please Give a Reason in the Message Box"
Set WaterCell = Range("G1")
Call TalkDistilled
Case Else
End Select
What am I doing wrong? Thanks