How do I pass the Sheet Name as an argument from a Form button?

[StaffsLebowski]

Hi,

I have a Form Button (not Command button) on a sheet and want to call a global function. Something like this:

MyFile.xlsm!MyFunction(Sheet.Name)

How do I do this using the Assign Macro dialog box?

Thank you for your help

StaffsLebowski

 

[RoryA]

Given that the sheet has to be active for you to press the button, you could simply use activesheet.name in the called code?

 

[StaffsLebowski]

Hi Rory, and thanks for your reply

That's true and a workaround, but doesn't give me the answer I want.

I have a global function I want to call from anywhere, using a passed sheet name. In this instance, the function is being called from a button so, I'd like to know, in this example, is it possible?

 

[Kyle123]

Public Sub Button1_Click()
    MyFunction ActiveSheet.Name
End Sub

 

[StaffsLebowski]

Thanks Kyle123,

I got this to work:

'MyFile.xlsm'!'MyProcedure activesheet.Name'

 

[StaffsLebowski]

Just to add, the single quotes are important

https://www.teachexcel.com/excel-tu...-macro-called-from-a-button-or-sheet-in-excel

 

[RoryA]

I got this to work:

'MyFile.xlsm'!'MyProcedure activesheet.Name'

Just bear in mind that you will have problems if you do that and save the file in xlsb format.