record and play macro to create commandbutton gives different results

[andreco]

Please help explain this apparent mystery:

If I record a macro using View->Toolbars->Control Toolbox->Command Button, I get a commandbutton whose right-click menu (under Design Mode) shows me a Properties option and at the bottom a Format Control option, and I get the code below. So far so good; that's what I want.

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1",
  Link:=False, DisplayAsIcon:=False, Left:=314, Top:=293, Width:=72, 
  Height:=24).Select

However, if I execute this newly created macro, I get a commandbutton whose right-click menu doesn't contain a Properties Option and at the bottom a Format Object option). A different animal. Why? why? why?

[lucas]

You have discovered why most of us don't create controls on the fly and why no one has addressed your problem.

Can you not make them visible or not visible instead?

[andreco]

Actually, my purpose is not to create the control on the fly but to create it from code. Since I wasn't getting the expected result, I then tried the macro recorder and ended up with the puzzling result.

Your solution - if I understood well - is to create it on the fly (apparently the only way) and then playing with it from then on thru code. Not a bad idea, I might end up doing that. But still, the question lingers: why?

[lucas]

creating controls on the fly means creating them using code.

Create them normally in the vbe and change thier visibility to suit your need.

That way the coding can be done in the vbe and you are done.

[lucas]

attached is a very simple example.

[andreco]

Ok, I misunderstood. But that is precisely the point: The control I create thru code (at least with OLEObjects.Add etc) is not the one I want (even though the one created with the macro recorder is - and the code is the same!).

But forget the recorder. Again: I need to create thru code a control (say a commandbutton) that upon right click includes the Properties option and at the end the Format Control option (what I get now doesn't have the Properties option and the last option is Format Object).

[lucas]

see, if you create it with code you then have to write code to be added to the new control........becomes a daunting and unnessessary task.

I think you are still confused.

If this is on a sheet, the kind of button you wish to create which is an activeX control can be adde to the sheet by:

right click on any toolbar and make sure that "visual basic" is checked.

from that toolbar add your control to the sheet and you will be able to right click on it as you require......

[andreco]

Thanks a lot for your efforts, Lucas. However I do need to create the ActiveX control (thanks for the name!) thru code. As a matter of fact, I create it in a given module and then another module changes some of its properties and later another one actually deletes the control. I'm only having this rather unexpected problem with the creation (I've already done a lot of duanting tasks

I guess I could create it manually and instead of deleting it I could just hide it. But, at least for the challenge: Must I finally assume that it is impossible to create a "properly behaving" ActiveX control thru code or at least not with the OLEObjects.Add method?

By the way, the "improperly behaving" control I do get actually does have a number a properties I can modify thru code, but they are very limited in number. I have tried to define what kind of control it is that I'm getting; from the options I get upon right-clicking it seems to be an "object" (?). I even checked the right-click options for a forms-generated commandbutton, and even though there ar some differences it still much resembles the ActiveX control behavior. Any ideas?

I'm starting to believe that I'm unable to clearly explain this issue...

[lucas]

Maybe this will give you some information that would help:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=274

a search of the knowledgebase will glean even more ideas......


If after some research you still have questions, post them here.

[andreco]

I discovered that once the properties window for a "proper" control is displayed, simply clicking on the "improper" control shows exactly the same properties as the "proper" one.

Furthermore, these properties are all accesible and modifiable thru code for both controls. Conclusion: They are the same! Well, with one small difference: Right-clicking shows a different set of options. Why? Beats me ...

I hope this helps other people; I was going nuts assuming I couldn't modify the properties of the control just because upon right-clicking it didn't show the option.

[lucas]

I saw somthing similar to this once. Seems like it was an html object....been to long to be sure.when you select it in design view what is its name in the name box located to the upper left of the grid.....to the left of the formula bar.