CODEWITHSUNDEEP
vbaexcel
jongthechemist
jongthechemist

Reputation: 11

application.run vba not working with sheet macros

My file has different sheets with the same-named sub routine inside each doing different things specific to the sheet. So, I'm trying to call dynamically a macro inside a selected sheet using Application.Run from a module. All sheets' "object" name (I don't know how to call those) are already modified to be the same as its view (inside the bracket) name, without the spaces. 

macro = Chr(39) & ThisWorkbook.Name & Chr(39) & "!" & Replace(SheetName, " ", "") & "." & "Harmoniser()"
Application.Run macro

Here's an example of the Harmoniser macro in a Sheet.

Sub Harmoniser()
    ActiveSheet.Range("k22").GoalSeek Goal:=0, ChangingCell:=Range("l13")
    MsgBox ("Done!")
End Sub

Somehow, only the MsgBox works, and it shows it twice everytime. Debugging by putting a break point doesn't work either. It happens to all of my sheets. Is there a limitation to Application.Run that it only allows certain code or there's an error that Excel is not giving me?

Upvotes: 1

Views: 2985

Answers (1)

David Zemens
David Zemens

Reputation: 53663

I get the same thing when I run your code. I tried a few different tweaks and am able to enter debug/breakpoint mode if I do this:

Sub t()
Dim sht As Worksheet
Dim macro As String

For Each sht In ThisWorkbook.Worksheets
    sht.Activate
    macro = sht.CodeName & ".Harmoniser"
    Application.Run macro
Next
End Sub

Your code may have been erroring silently because you were using ActiveSheet without first Activating the sheet which contains the macro to be run. Not sure about the double-display of the MsgBox, but seems like that may be related.

Upvotes: 2

Related Questions