running two change by value in one worksheet

[armchairandy]

Not sure if this is possible.. but

I already have a change event in a work sheet - works as I want it to

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
Set KeyCells = Range("G17:G205")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Dim iCol As Integer
Range("Y3").Activate
For iCol = 0 To 100
If ActiveCell.Offset(0, iCol).Value = Range("B1").Value Then
ActiveCell.Offset(1, iCol).Value = Range("H14").Value
Exit For
End If
If iCol = 100 Then
'MsgBox "This Metric is not due to Start yet!"
End If
Next iCol
End If
End Sub

Kindly donated by WaterGypsy

I have a drop-box with two options in another cell on the same worksheet, where one option would runs macro, the other option does nothing.

How can I enter both in the Change by value section? I do not want both codes running if only one range changes. Alternatively is there a mechanism where I can run the drop-box option separately.

Any help appreciated

Andrew

 

[Fluff]

Is the drop-box a data validation dropdown?
If so what cell is it in & what needs to happen?

 

[mumps]

If you have a second Worksheet_Change macro based on your drop down list, please post it as well. If you don't yet have it, then explain in detail what you want the second Worksheet_Change macro to do.

 

[armchairandy]

Mumps

This is the second "macro" based on the value in cell D7

Sheets("M1").Activate
If (Range("D7")) = "Automatic" Then
Exit Sub
Else
Dim Answer As String
Dim MyNote As String
MyNote = "Are you sure you want to continue? This will delete the formulas in the actual column"
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "CWC Data Deletion")

If Answer = vbYes Then

Sheets("M1").Activate

Range("H17:H205").Select
Selection.Locked = False
Selection.FormulaHidden = False
Selection.ClearContents

End If
End If
End Sub

Andrew

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim KeyCells As Range
   Set KeyCells = Range("G17:G205")
   If Not Application.Intersect(KeyCells, Range(Target.Address)) _
         Is Nothing Then
      Dim iCol As Integer
      Range("Y3").Activate
      For iCol = 0 To 100
         If ActiveCell.Offset(0, iCol).Value = Range("B1").Value Then
            ActiveCell.Offset(1, iCol).Value = Range("H14").Value
            Exit For
         End If
         If iCol = 100 Then
            'MsgBox "This Metric is not due to Start yet!"
         End If
      Next iCol
   ElseIf Target.Address(0, 0) = "D7" Then
      If Target = "Automatic" Then Exit Sub
      Dim Answer As String
      Dim MyNote As String
      MyNote = "Are you sure you want to continue? This will delete the formulas in the actual column"
      Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "CWC Data Deletion")
      
      If Answer = vbYes Then
         With Sheets("M1").Range("H17:H205")
            .Locked = False
            .FormulaHidden = False
            .ClearContents
         End With
      End If
   End If
End Sub

 

[mumps]

I assume that the second macro is a Worksheet_Change event. Is it triggered by a change in D7? Is the macro in the worksheet code module for "M1"?

 

[armchairandy]

Fluff

Many thanks - works perfectly

saved my reserve of migraine pills

Regards

Andrew

 

[Fluff]

You're welcome & thanks for the feedback