Calling a macro with a variable from “Personal.xlsb”

xlUser1

Board Regular
Joined
Jul 11, 2008
Messages
55
I have a macro in Personal.xlsb called Public Sub Get_Name(shName As String)

When I use below sub to call Get_Name from Personal.xlsb, I get an error at the Application.Run "Personal.xlsb!Get_Name(shName)"

Code:
Public Sub Set_Name()
    Dim shName As String
    shName = ActiveSheet.Name
[B]     Application.Run "Personal.xlsb!Get_Name(shName)"[/B]
End Sub
If I remove the variable from Get_Name macro and use Application.Run "Personal.xlsb!Get_Name", it runs ok. Any help on how to get it to work with the variable?

Thanks in advance for the help
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
Code:
  Application.Run "Personal.xlsb!Get_Name", ActiveSheet.Name
 

xlUser1

Board Regular
Joined
Jul 11, 2008
Messages
55
Wow....Thank you very much for the quick answer!!!
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,045
Messages
5,412,026
Members
403,409
Latest member
IHRAcer

This Week's Hot Topics

Top