Reference to ActiveX control on worksheet requires full worksheet name

I have a combobox called ReportMonth on a worksheet (i.e. not on a form)
called Summary. I want to include the current ReportMonth value (which can
change from one month to the next) in the CenterHeader along with some other
text, ready for printing, so I put the necessary code in the
Workbook_BeforePrint event module. This works fine, except that I can't set
a reference to the worksheet and use this when I refer to the combobox, I
have to use the full worksheet name i.e.:

Dim wks As Worksheet
Set wks = Worksheets("Summary")
....
[code to set CenterHeader]... wks.ReportMonth.Value ...
....

produces an Object Required error. I have to "spell it out" fully and do
this instead:

....
[code to set CenterHeader]... Worksheets("Summary").ReportMonth.Value ...
....

which works fine.

This might seem petty, but it seems to go against all the advice on setting
references to objects as good coding practice.

Why doesn't the neater code work?

Ian

I have not run across this before but tested it and (at least on Excel XP)
you are correct. I would assume it is because using
Worksheets("Summary").ReportMonth is already a "violation" of strict object
referencing. The actual full reference to the combobox should be
Worksheets("Summary").OLEObjects("ReportMonth").Object. In my test,
ws.OLEObjects("ReportMonth").Object.Value does give the proper result.

So I guess we can look at it two ways: the VBA developers made a mistake by
not having the shorthand method follow all the regular object referencing
rules, or alternatively they did us a favor by even allowing it at all, so
who are we to say it should behave like a "regular" object reference?

--
- K Dales


"Ian Ripsher" wrote:

> I have a combobox called ReportMonth on a worksheet (i.e. not on a form)
> called Summary. I want to include the current ReportMonth value (which can
> change from one month to the next) in the CenterHeader along with some other
> text, ready for printing, so I put the necessary code in the
> Workbook_BeforePrint event module. This works fine, except that I can't set
> a reference to the worksheet and use this when I refer to the combobox, I
> have to use the full worksheet name i.e.:
>
> Dim wks As Worksheet
> Set wks = Worksheets("Summary")
> ....
> [code to set CenterHeader]... wks.ReportMonth.Value ...
> ....
>
> produces an Object Required error. I have to "spell it out" fully and do
> this instead:
>
> ....
> [code to set CenterHeader]... Worksheets("Summary").ReportMonth.Value ...
> ....
>
> which works fine.
>
> This might seem petty, but it seems to go against all the advice on setting
> references to objects as good coding practice.
>
> Why doesn't the neater code work?
>
> Ian
>
>
>

partly... a bit ot theory:

it has do with instantiation of the worksheet class
AND the typing of the object variable.

suppose the codename for sheet "Summary" is sheetSummary
the combobox reportmonth only exists as a property of
the instance of the worksheet class called sheetSummary

just compile following.. then uncomment the last line.
(needs a sheet Name:Summary,Codename SheetSummary
with 1 embedded CommandButton control from the controls toolbox.)

Sub foo()
Dim MySheet As SheetSummary
Dim AnObject As Object
Dim ASheet As Worksheet

Set MySheet = SheetSummary ' or worksheets("Summary")
Set AnObject = SheetSummary
Set ASheet = SheetSummary


Debug.Print Worksheets("Summary").CommandButton1.Caption
Debug.Print
Worksheets("Summary").OLEObjects("CommandButton1").Object.Caption
Debug.Print SheetSummary.CommandButton1.Caption

Debug.Print MySheet.CommandButton1.Caption
Debug.Print AnObject.CommandButton1.Caption

'THIS wont work
'Debug.Print ASheet.CommandButton1.Caption

End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


K Dales wrote :

> I have not run across this before but tested it and (at least on
> Excel XP) you are correct. I would assume it is because using
> Worksheets("Summary").ReportMonth is already a "violation" of strict
> object referencing. The actual full reference to the combobox should
> be Worksheets("Summary").OLEObjects("ReportMonth").Object. In my
> test, ws.OLEObjects("ReportMonth").Object.Value does give the proper
> result.
>
> So I guess we can look at it two ways: the VBA developers made a
> mistake by not having the shorthand method follow all the regular
> object referencing rules, or alternatively they did us a favor by
> even allowing it at all, so who are we to say it should behave like a
> "regular" object reference?

"K Dales" <KDales@discussions.microsoft.com> wrote in message
news:0A1B769D-DF41-4459-8590-82631DF00254@microsoft.com...
>I have not run across this before but tested it and (at least on Excel XP)
> you are correct. I would assume it is because using
> Worksheets("Summary").ReportMonth is already a "violation" of strict
> object
> referencing. The actual full reference to the combobox should be
> Worksheets("Summary").OLEObjects("ReportMonth").Object. In my test,
> ws.OLEObjects("ReportMonth").Object.Value does give the proper result.
>
> So I guess we can look at it two ways: the VBA developers made a mistake
> by
> not having the shorthand method follow all the regular object referencing
> rules, or alternatively they did us a favor by even allowing it at all, so
> who are we to say it should behave like a "regular" object reference?
> --
> - K Dales

Many thanks for this - you've educated me - I didn't realise that
Worksheets("Summary").ReportMonth was a violation (and therefore shouldn't
really work, even though it does!) and what the full reference should be!
While I was doing this, I did look at the OLEObjects collection, but I
didn't include the .Object at the end, which is why that threw up an error
as well!

I figured that, because UserFormName.ControlName always works (and is the
correct ref) when the control is on a userform, then logically,
WorksheetName.ControlName ought to be correct when it's on a worksheet.
Unfortunately the MS guidance (and the book I'm using) is rather shy on
controls embedded on the worksheet - a lot more guidance is provided on
userform controls.

Thanks again