macro

[juan1]

how do I change the below to not be an event, but run from a macro1?
thanks
Jan

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range

'change the input cell range as desired

Set rInt = Intersect(Target, Range("plus20pct"))
If Not rInt Is Nothing Then
For Each rCell In rInt
If IsNumeric(rCell) Then
With Application
.EnableEvents = False
rCell = rCell * 1.2
.EnableEvents = True
End With
End If
Next
End If
End Sub

 

[juan1]

Is there a way to run a macro that says don't run the event macro if I chose not to do the multiplication in the cell?
thanks

 

[MrTeeny]

In that case you'd still need to use worksheet_change, try this

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
Application.Calculation = xlCalculationManual
    Dim KeyCells As Range
    Set KeyCells = Range("A1:D6")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then




     If IsNumeric(Target.Value) And Not IsEmpty(Target.Value) Then Target.Value = Target.Value * 1.2
       
    End If
    Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[MrTeeny]

Try this then if you want yes no options

Private Sub Worksheet_Change(ByVal Target As Range) 
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
Application.Calculation = xlCalculationManual
    Dim KeyCells As Range
    Dim answer As Integer
    Set KeyCells = Range("A1:D6")
    
 
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing And IsNumeric(Target.Value) And Not IsEmpty(Target.Value) Then


   answer = MsgBox("Multiply number?", vbYesNo + vbQuestion)
   If answer = vbYes Then
     Target.Value = Target.Value * 1.2
Else
    'do nothing
End If
     
    
       
    End If
    Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[juan1]

thanks so much this second one is exactly what I need.
awesome
Juan