label control from macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sali - 24 Sep 2007 07:41 GMT

recently, i beleived that executing "recorded macro" must produce the very
*same* effect like the action was recorded

but i found the difference for label form control

inserting from control toolbox i created the label, and the recorded macro
was:
'------
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1", Link:=False, _
DisplayAsIcon:=False, Left:=416.25, Top:=114.75, Width:=157.5, Height _

:=78.75).Select

'------

running that macro [on other worksheet] produced different result, that
object missed the "properties" item from right-click menu
also, macro didn't record the property editing [caption] that i did during
label insertion

is it expecting behaviour, or am i doing something wrong?

i expected that recorded macro *allways* produce the same effect like the
user interactive action?

is there some workaround to got exactly the same result with macro and
interactive action?

thnx

Reply to this Message

Bob Phillips - 24 Sep 2007 09:05 GMT

Not sure what you mean. A Control Toolbox label doesn't have a properties
option on right-click, at least not in the XP I am running.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> recently, i beleived that executing "recorded macro" must produce the very
> *same* effect like the action was recorded
[quoted text clipped - 23 lines]
>
> thnx

Reply to this Message

sali - 24 Sep 2007 09:43 GMT

for info, i tested few, office2000 and office2002, both the same
control toolbox has big capital letter "A" for label insertion
right-click ona inserted label gives me:
==============
cut
copy
paste
-----
properties
view code
-----
label object >
-----
grouping >
order >
-----
format control ...
===============

the label inserted with recorded macro is missing properties and view code
items

not to being a big problem, more to be surprised that recorded macro action
behaving different result

"Bob Phillips" <bob.ngs@somewhere.com> je napisao u poruci interesnoj
grupi:%23UgOwGo$HHA.5164@TK2MSFTNGP05.phx.gbl...

> Not sure what you mean. A Control Toolbox label doesn't have a properties
> option on right-click, at least not in the XP I am running.

Reply to this Message

Bob Phillips - 24 Sep 2007 10:36 GMT

You are absolutely right. I could have sworn I didn't see it with a manually
added label before, but it is there now.

How odd.

You can get at the properties though by double-clicking the label, and that
will take you into the VBIDE for that control, and the properties panes is
shown there.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> for info, i tested few, office2000 and office2002, both the same
> control toolbox has big capital letter "A" for label insertion
[quoted text clipped - 25 lines]
>> Not sure what you mean. A Control Toolbox label doesn't have a properties
>> option on right-click, at least not in the XP I am running.

Reply to this Message

Peter T - 24 Sep 2007 09:32 GMT

It's not clear what you mean by properties in the 'right-click' menu. But
guessing, recorded macros to not record changes to properties of OLEObjects
such as Label caption.

Try this-

Sub test()
Dim ole As OLEObject

Set ole = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=416.25, Top:=114.75, Width:=157.5, Height:=78.75)

ole.Object.Caption = "My Label Caption"

End Sub

also have a look at the following after you have added at least one Label
to the sheet

Sub test2()

Set ole = ActiveSheet.OLEObjects(1)

Stop
'press Alt-v, s
' look at ole in Locals
' and in particular expand the + next to Object for more properties

' press F8 or F5 to complete the routine
End Sub

Regards,
Peter T

> recently, i beleived that executing "recorded macro" must produce the very
> *same* effect like the action was recorded
[quoted text clipped - 23 lines]
>
> thnx

Reply to this Message

sali - 24 Sep 2007 10:40 GMT

thnx for your example of object prop changing.
i suppose, object editing is hidden from macro recorder?

right-click:
when right-click onto inserted label in worksheet, the pop up menu is not
the same when label inserted interactively, or by recorded macro [of the
same action].
as i explained in my post, there is two items missing when inserting by
macro.
but just maybe my office instalation is "tweaked" maybe by some corporate
common controls
currently, all computers i have access to are part of corporate network

"Peter T" <peter_t@discussions> je napisao u poruci interesnoj
grupi:%23e%23u3Vo$HHA.3900@TK2MSFTNGP02.phx.gbl...

> It's not clear what you mean by properties in the 'right-click' menu. But
> guessing, recorded macros to not record changes to properties of
[quoted text clipped - 5 lines]
> Sub test()
> Dim ole As OLEObject

Reply to this Message

Peter T - 24 Sep 2007 11:32 GMT

First, I hope this typo in my previous post was not misleading

>> recorded macros to not record changes etc...

should read
recorded macros Do not record changes etc...

> i suppose, object editing is hidden from macro recorder?

Indeed some property changes of oleObjects are not recorded. However an
OLEObject is also a Shape and some properties that can be changed via the
right-click "Format Object" dialog will be recorded.

I'd suggest don't worry about what you can and cannot record. Use the
example I posted earlier. It's important to know if the property you want to
change applies to
OLEObject or to OLEObject.Object.

I don't follow what you are trying to say regarding the right-click menu,
for me there is no difference in the menus if the control is inserted
manually or programmatically. Note that manually you should go into 'Design
mode' (top left icon on control toolbox menu) before right clicking the
object and "Format Object" and/or selecting "Properties".

Regards,
Peter T

Regards,
Peter T

> thnx for your example of object prop changing.
> i suppose, object editing is hidden from macro recorder?
[quoted text clipped - 20 lines]
> > Sub test()
> > Dim ole As OLEObject

Reply to this Message

sali - 24 Sep 2007 11:56 GMT

thnx

knowing the object strucure, it is easy to change its properties by macro

regarding right-click pop up menu, i don't want to insist [anyway it is
auxilary problem] but somebody on this tread also agreed that right-click
pop up menu is realy not the same when you insert label manualy, or with
recorded macro.

but, please, this is just my observation, thnx for you helping me understand
the problem and its backgound

thnx

"Peter T" <peter_t@discussions> je napisao u poruci interesnoj
grupi:e8Q9BZp$HHA.5160@TK2MSFTNGP05.phx.gbl...

> First, I hope this typo in my previous post was not misleading

>. It's important to know if the property you want to
> change applies to
[quoted text clipped - 6 lines]
> mode' (top left icon on control toolbox menu) before right clicking the
> object and "Format Object" and/or selecting "Properties".

Reply to this Message

Peter T - 24 Sep 2007 12:28 GMT

> i don't want to insist

Sorry, you are absolutely right !

It seems the right click popup menu named "OLE Object" becomes 'attached' to
Active x controls that were added programmatically. Whereas if added
manually, the popup menu named "ActiveX Control" is attached ('attached' is
not quite the right word).

The "OLE Object" popup does not include the "Properties" button (and some
other differences).

Anyway, you can still get to "Properties" if you click the "Properties" icon
on the Control toolbox toolbar.

I had never noticed, and I agree it's odd.

Regards,
Peter T

> thnx
>
[quoted text clipped - 24 lines]
> > mode' (top left icon on control toolbox menu) before right clicking the
> > object and "Format Object" and/or selecting "Properties".

Reply to this Message

Bob Phillips - 24 Sep 2007 12:47 GMT

Pretty good. He gets two apologies from doubters in one thread <vbg>

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>> i don't want to insist
>
[quoted text clipped - 49 lines]
>> > mode' (top left icon on control toolbox menu) before right clicking the
>> > object and "Format Object" and/or selecting "Properties".

Reply to this Message

Peter T - 24 Sep 2007 13:08 GMT

> Pretty good. He gets two apologies from doubters in one thread <vbg>

and rightly so :-)

Certainly taught me to pay more attention in future, for the next hour or
two at least !

Peter T

Reply to this Message

Bob Phillips - 24 Sep 2007 13:21 GMT

Blimey, you've got a long attention span. I'm impressed <ebg>

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>> Pretty good. He gets two apologies from doubters in one thread <vbg>
>
[quoted text clipped - 4 lines]
>
> Peter T

Reply to this Message