Log In
Register
Home
Active Member
March 5, 2018
Offline
Hello,
I need to combine two Private Sub Worksheet_Change(ByVal Target As Range) I'm new to Excel VBA code, how can I do this? Code below.
1)
Option Explicit Const strAFM As String = "D3:D1000" Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range, AFM As String, rngTomi As Range Set Rng = Range(strAFM) Set rngTomi = Intersect(Target, Rng) If rngTomi Is Nothing Then Exit Sub If rngTomi.Count <> 1 Then rngTomi.ClearContents Exit Sub End If If Trim(Target.Value) = "" Then Exit Sub AFM = Right("000000000" & Target.Value, 9) If isAFM(AFM) = False Then MsgBox "no afm" Target.Activate Exit Sub End If End Sub
2)
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub Dim Rng As Range Set Rng = Me.Range("ColTarget") If Intersect(Target, Rng) Is Nothing Then Exit Sub ResizeTbl End Sub
October 5, 2010
Offline
Hi Giannis,
It depends on what you want the combined Subs to do.
For example, you have If Target.Count > 1 Then Exit Sub in one Sub, do you want this in the combined Sub?
You have two similar statements
Set Rng = Range(strAFM)
Set Rng = Me.Range("ColTarget")
which one do you want to use?
Do you want to call ResizeTbl in the combined sub? If so, at what point?
Without clear direction of what you want to do I don't know how to combine the code. Not knowing what ResizeTbl and isAFM do, also makes it tricky.
If you want to provide code, please attach your workbook to the forum post.
Regards
Phil
Active Member
March 5, 2018
Offline
Hello,
Thanks for the answer, finally found a solution as below and works
Option Explicit Const strAFM As String = "D3:D1000" Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range, AFM As String, rngTomi As Range If Not Target.Count > 1 Then Set Rng = Me.Range("ColTarget") If Not Intersect(Target, Rng) Is Nothing Then ResizeTbl End If Set Rng = Range(strAFM) Set rngTomi = Intersect(Target, Rng) If Not rngTomi Is Nothing Then If rngTomi.Count <> 1 Then Application.EnableEvents = False rngTomi.ClearContents Application.EnableEvents = False Exit Sub End If If Trim(Target.Value) = "" Then Exit Sub AFM = Right("000000000" & Target.Value, 9) If isAFM(AFM) = False Then MsgBox "no afm" Target.Activate Exit Sub End If End If End Sub
1 Guest(s)
