VBA and determining which sheet the code is running under

[jake]

Hi

I am calling code that resides within different sheets.
So I start on sheet1 and press a go button the go button calls a
function on sheet2, sheet3 etc. Within the code on sheet2 I need to
grab the name of sheet2. I cannot hard code it as the sheets maybe
renamed and I need the current sheet name. I have tried using
ActiveSheet but this returns sheet1.

Any ideas?

thanks

Jake

 

[Melanie Breden]

I am calling code that resides within different sheets.

So I start on sheet1 and press a go button the go button calls a
function on sheet2, sheet3 etc. Within the code on sheet2 I need to
grab the name of sheet2. I cannot hard code it as the sheets maybe
renamed and I need the current sheet name. I have tried using
ActiveSheet but this returns sheet1.

is the code in the module of the sheet?
Then use the keyword 'Me' in place of 'ActiveSheet'.

--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[ross]

Calling active sheet from another sheet will return the name of it's
self, it's a class modual,
try running the same code from a standard modual

vis:

Public Sub InAStanardModual()

Sheet1.Activate
MySheet = ActiveSheet.Name

End Sub

wil return the name of sheet one.


another way would be to use the code name, as shown in the vbe window:
sheet1(MyWorkSheetName)

where the "MyWorkSheetNAme" is the name of the worksheet on the tab.

to call a procedure in a standard modual you simple add the name of
the procdure to the calling event, i.e


Button1_click

InAStanardModual

end sub
would run the about code from modual1

good luck

ross