Refrering to a button on another sheet

[eti3nne]

If I want to refer to a button on a particular sheet from a module, how do I preface a:-
If CommandButton2.Caption = "Hello" then ...statement?

1) If sheet name is say Sheet1
2) if sheet name is say a variable like WeekEndingDate
3) or do i use say ... Sheet33(WeekEnding12Jul2009).. (this is how it appears in the code lists.)

The reason I am asking is because if I put the refrence on the same code sheet as the button, 'CommandButton2.Caption' statement runs Ok, but if I put this code in a module and call the code, the statement seems to be being ignored or is looked for elsewhere (or something!?)

Thanks for looking.

 

[SkipVought]

Hi,

SheetObject.Shapes("object name")
[code]



Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[eti3nne]

Hi skip,

SheetObject.Shapes("object name")?

Sorry, But I'm not sure how to apply this to this situation.

 

[SkipVought]

Sheets have Names and CodeNames. The Name is what you see on the sheet tab.

1) If sheet name is say Sheet1
2) if sheet name is say a variable like WeekEndingDate
3) or do i use say ... Sheet33(WeekEnding12Jul2009).. (this is how it appears in the code lists.)

Statement 1) & 2) mean WHAT? And whats Sheet33. You must be clear.

Again if you refer to a shape on a sheet...

dim oShp as Shape
Set oShp = Sheets("YourSheetName").Shapes("YourShapeName")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

.. if you use activex commandbutton, you get its caption after (oShp from Skip's code):

MsgBox oShp.OLEFormat.Object.Object.Caption

combo

 

[eti3nne]

SkipVought/combo,

Tried the Shapes construct but couldn't obtain the .caption value. (Hadn't got as far as trying the activex suggestion) when ...

A colleague suggested obtaining the CommandButton.caption value direct from the actual worksheet and passing it to my sub, (where unique code will run, dependant on the value of the .caption).

The commandbutton click event then can set the .caption value as required.

Now working fine. Thank you both anyway for the suggestions.