Combining Multiple Macros

[ppp1812]

I am new to VBA and Macros.
But would like to know how can two or more different Vba Codes in Excel can be combined and used simultaneously.

 

[bobhc]

ppp1812

I think the VBA gurus will need to know a bit more about what you are trying to do. Different codes do different things so combining unknown code is going to be messy.

 

[ThrottleWorks]

Hi ppp1812, Welcome to the awesome forum !

I think I am not able to understand correctly what your problem is.

Are you trying to "Call" the macros, for example, you have sub abc1 & sub abc2.
Do you want to run abc1 & abc2 in serial order, if yes.

In the sub abc1, at the end of the code but before "End Sub" you have to wright Call abc2.

This will prompt VBA to run the sub abc2 without manual intervention.

Also, meanwhile experts on the Forum help you, could you please check following link.

http://chandoo.org/forum/threads/new-users-please-read.294/

P.S. - Bob Sir, sorry I saw your post now.

 

[ppp1812]

Given below are the two codes which i need to run in one excel sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
            If Target.Cells.Count > 1 Then Exit Sub
            If Not Intersect(Target, Range("A2:A9999")) Is Nothing Then
              With Target(1, 9)
                    .Value = Date
              End With
            End If
End Sub

and the other one is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            Dim rngF As Range
            Dim rngFS As Range
            Dim lRow As Long
            Dim lCol As Long
 
            Set rngF = ActiveSheet.AutoFilter.Range
            Set rngFS = ActiveSheet.Range("FilterStatus")
 
            lCol = rngF.Columns(1).Column - 1
            lRow = rngF.Columns(1).Row
 
'            -------------------------------------------------------------------
            If Target.Count > 1 Then GoTo exitHandler
'            -------------------------------------------------------------------
 
            If Target.Address = rngFS.Address Then
              If rngFS.Value = "On" Then
                  rngFS.Value = "Off"
              Else
                  rngFS.Value = "On"
              End If
            End If
 
            If UCase(rngFS.Value) = "ON" Then
              If Not Intersect(Target, rngF) Is Nothing Then
                  If Target.Row > lRow Then
                    rngF.AutoFilter Field:=Target.Column - lCol, _
                                          Criteria1:=Target.Value
                  ElseIf Target.Row = lRow Then
                    rngF.AutoFilter Field:=Target.Column - lCol
                  End If
               End If
            End If
 
exitHandler:
            Exit Sub
End Sub

Now how can i combine these two so that they run together ???

 

[ThrottleWorks]

Hi ppp1812,

I am still confused

Could you please confirm, "Now how can i combine these two so that they run together ??? " is this different from

"Are you trying to "Call" the macros, for example, you have sub abc1 & sub abc2.
Do you want to run abc1 & abc2 in serial order, if yes."

 

[Luke M]

If you want both macros to run whenever either event occurs, structure would look like this:

'Two event macros to trap our conditioners, pass the Target as a variable
Private Sub Worksheet_Change(ByVal Target As Range)
Call CombineMacro(Target)
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call CombineMacro(Target)
End Sub
 
'=============================
Private Sub CombineMacro(myTarget As Range)
'This is where we do all of the actual work
Dim rngF As Range
Dim rngFS As Range
 
Dim lRow As Long
Dim lCol As Long
Set rngF = ActiveSheet.AutoFilter.Range
Set rngFS = ActiveSheet.Range("FilterStatus")
 
lCol = rngF.Columns(1).Column - 1
lRow = rngF.Columns(1).Row
 
If myTarget.Cells.Count > 1 Then Exit Sub
 
'Should turn this on since you're about to make a change.
'Prevents recursive calling of Change Event macros
Application.EnableEvents = False
 
If Not Intersect(myTarget, Range("A2:A9999")) Is Nothing Then
    myTarget(1, 9).Value = Date
End If
 
If myTarget.Address = rngFS.Address Then
    If rngFS.Value = "On" Then
        rngFS.Value = "Off"
    Else
        rngFS.Value = "On"
    End If
End If
 
If UCase(rngFS.Value) = "ON" Then
    If Not Intersect(myTarget, rngF) Is Nothing Then
        If myTarget.Row > lRow Then
            rngF.AutoFilter Field:=myTarget.Column - lCol, _
            Criteria1:=myTarget.Value
        ElseIf myTarget.Row = lRow Then
            rngF.AutoFilter Field:=myTarget.Column - lCol
        End If
    End If
End If
Application.EnableEvents = True
exitHandler:
 
End Sub

 

[Sam Mathai Chacko]

Just to remind you, that the above code will only work if you've pasted the entire code in the respective sheet module. It is an event triggered procedure that is based on the class within which it is written (read supposed to be written), in your case, the sheet module.

 

[Debraj]

Hi ppp1812,

I am little bit confused..

* Event 1 ."Worksheet_Change" only focus on Column A, and update Column I as date.
* Event 2. "Worksheet_SelectionChange" focused on complete filtered area.. and in filtered area, on each click, it will re-filter previous conditions & New Selcection.. .. just shorten your visisbility..

If i have read atleast above two macro clearly, then you dont need to combining both macro.. as both are working their own business..

actual problem is when you update anything in Column A.. it update value in column I (no issue)
but at the same time it re-filter according to column A.. am i right..

then why not exclude column A from filtration process.. will hboth situation.

below this line... just add another line..

If Target.Count > 1 Then GoTo exitHandler
If Target.Column = 1 Then GoTo exitHandler

Please let us know.. if I am in the right direction..
PS: I have highlighted in your post.. where you have to add this line..

 

[ppp1812]

Just to remind you, that the above code will only work if you've pasted the entire code in the respective sheet module. It is an event triggered procedure that is based on the class within which it is written (read supposed to be written), in your case, the sheet module.

I got the issue of combining the two macros sorted out.
Thanks for giving your precious time
Prakash