PDA

View Full Version : VBA macro for running multiple macros in multiple worksheets in the same workbook!



kas
06-05-2015, 05:06 AM
Hi,

I am facing some difficulty with running multiple macros in multiple worksheets. This might be simple, and I searched quite alot, but couldn't find the answer that I am looking for.

The VBA code that I tried is not working.

So I have one workbook (that is one Excel document). There are two sheets, Sheet 1 and Sheet 2. I have three Macros, Macro 1, 2 and 3.

I want to run Macro 1 and 2 in Sheet 1 and Macro 3 in Sheet 2. Hence, I want to create a VBA code which first runs Macro 1 and then Macro 2 in Sheet 1 and then Macro 3 in Sheet 2.

I thought I have to use the worksheet.Activate command but it doesn't seem to be working. I used the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet1").Activate
Select Case Target.Column
Case Is = 4, 5, 6, 7
Call Macro1
Call Macro2
End Select
Sheets("Sheet2").Activate
Call Macro3
End Sub

Sheet 1 seems to be working with Macro 1 and 2 but then it doesn't go to Sheet 2 and does not run Macro 3.

So basically even by using the above code, the only part that it is running is:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet1").Activate
Select Case Target.Column
Case Is = 4, 5, 6, 7
Call Macro1
Call Macro2
End Select
End Sub

Maybe there is a problem with the 'module' where I'm creating the above code.
In order to create Macro 1 and Macro 2, I right-clicked on 'Sheet1' in the left hand side panel in VBA, insert>module.
Same for Macro 3, I right-clicked on 'Sheet 2' and insert>module.

But for running the above code which combines macros from both sheets, which module should I use, where should I right-click or double-click to create a module, VBA Project (Workbook1) or thisworkbook?
What is the correct code that I should be using?

Any help will be greatly appreciated! I am really struggling with this!

Kenneth Hobs
06-05-2015, 06:30 AM
Welcome to the forum!

Worksheet code should go in Worksheet objects. To make sure that you add the right event code to a worksheet, right click its tab, View Code, and work there. Of course you can easily do that from the Visual Basic Editor (VBE) by doubleclicking the worksheet object in the Project Explorer. If is not visible, enable that View by clicking the View menu in VBE. Right click in the Project Explorer to insert Modules or insert them via VBE's Insert menu.

What you might want to consider is to pass your Target value to a Module. Then for each event in as many worksheet objects as you like, you call the Module rather than duplicating all of the code.

Please use code tags when pasting code. It will keep your code structure. Click the # icon in a reply or type the code tags manually. e.g. (code)'your code here(/code) and replace ()'s with []'s.

Your code in your module can be set to act on a passed worksheet object rather than the default if not specified, ActiveWorksheet. You probably need to post your Macro1-3 macros if you need help doing that.

Here is a short example showing the concepts that I explained.
Sheet1 and Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Macro1 Target, ActiveSheet, "Macro1"
Macro2 Target, ActiveSheet, "Macro2"
End Sub

Module1:

Sub Macro1(Target As Range, ws As Worksheet, s As String)
Select Case Target.Column
Case Is = 4, 5, 6, 7
MsgBox "Target.Column: " & Target.Column & vbLf & _
"Target's Worksheet Name: " & Target.Parent.Name
Case Else
Exit Sub
End Select

MsgBox "Worksheet.Name: " & ws.Name & vbLf & _
"String Value: " & s & vbLf & _
"Target.Address: " & Target.Address & vbLf & _
"Target.Address(External:=True): " & Target.Address(External:=True)
End Sub


Sub Macro2(Target As Range, ws As Worksheet, s As String)
Select Case Target.Column
Case Is = 4, 5, 6, 7
MsgBox "Target.Column: " & Target.Column & vbLf & _
"Target's Worksheet Name: " & Target.Parent.Name
Case Else
Exit Sub
End Select

MsgBox "Worksheet.Name: " & ws.Name & vbLf & _
"String Value: " & s & vbLf & _
"Target.Address: " & Target.Address & vbLf & _
"Target.Address(External:=True): " & Target.Address(External:=True)
End Sub


If your code is going to modify a worksheet, be sure to use Application.EnableEvents=False before and set it to True after.

kas
06-05-2015, 06:51 AM
Hello Kenneth,

Thank you very much for your reply. I have noted your code. But I am still confused, can I just clarify two things, please?


1) First, does it matter how we create a module? As you know there are many different ways, I can click on Insert>module. Then right-click on VBAProject(Workbook1) and insert>module, or right-click on Sheet1 (or Sheet 2) and insert module, or right click on 'this workbook' and insert module. So there a difference in the type of module created from all these sources?
If I want Macro 1 and Macro 2 to run on Sheet 1 only and Macro 3 to run on Sheet 3 only, does it matter how I create a module? What is the best way to create a module if I want this sort of arrangement? Or should I create one module and use the 'activesheet' command to specify which module to run in which sheet?

I can see that there are different ways of inserting a module. Right now whats happening is that when I insert a module and 'run' it, it runs on any sheet in the module. So if I am on 'Sheet 1' at the moment, it runs in Sheet1 and if I am on 'Sheet 2', it runs on Sheet 2, maybe thats how its supposed to be. Like you suggested, should I double-click on say Sheet1 or 'view code' on Sheet1 before writing the code for Macro 1 and Macro2? If I 'view code' on Sheet1, will the macro written (combined for Macro1 and 2) will only run on Sheet1?

2) I have tried something like this:
Sub onetwomacros()
Worksheets("Sheet1name").Activate
Select Case Target.Column
Case Is = 4, 5, 6, 7
Application.EnableEvents=False
Call Macro1
Call Macro2
Application.EnableEvents=True
End Select
End Sub


Sub threemacro()
Worksheets("Sheettwoname").Activate
Application.EnableEvents=False
Call Macro3
Application.EnableEvents=True
End Sub

The above code does seem to work apparently, do you think it is right? Also, you mentioned using the Application.Enable events command as well, yes I think the worksheets change when I use the macros, the values in some of the cells change so should I use it? Have I used it correctly in the above command?

Kenneth Hobs
06-05-2015, 07:53 AM
Right clicking shows a context menu window for that window so anywhere in the project explorer shows the same content menu options. Use the method that you like best.

I do prefer that new users be in the Worksheet or ThisWorkbook objects when they create events and create them via the drop down menus. When created properly, they can be copied to other routines if needed.

While Activate is required sometimes, I prefer not to use it much. It is not efficient. It is needed on occasion though. When in doubt if you have the right reference, use debug methods to check. A quick check can be a MsgBox like:

MsgBox ActiveSheet.Name In VBE, a line like this places the output into the Immediate Window.

Debug.Print ActiveSheet.Name
Another debug method is to run one line at a time. Press F8 while in a routine and hover cursor over variables to see what they resolve to.

A relative reference like this:

MsgBox Range("A1").Addresshas an implied ActiveSheet object prefix.
You can be more absolute by:

Dims ws as Worksheet
set ws = Worksheets("Sheet3")
With ws
MsgBox .Name
End With
The . before Name means to use the object defined with With. With makes code more readable and efficient. I only use With when I refer to an object more than 2 or 3 times.

Should events stop firing, it is likely due to an error during your code that did not run the Application.EnableEvents=True. You can restart Excel or in the Immediate Window, type that code and press Enter key to execute it to reset it quickly. I like to use the Immediate Window to do quick checks. ? in the Immediate window and an expression will act like Debug.Print. e.g.

?1=1Pressing the Enter key will execute that line and show the result, True. This is another nice tools to use for debugging short snippets.

I would suggest trial and error to see if it works as you expected. Use the MsgBox and Debug.Print methods to give yourself quick checks to verify that it does what you want.