Erratic Behaviour of Buttons

I have been trying for several days to attach some buttons to a worksheet,
to set the properties caption and colour, and to assign a macro to each of
them.

Somehow I have actually achieved this with some of them, but I do not know
how. When I right click a button (in design mode) I get different messages
for each.

For instance, one button has the caption "Update" and when I right click
this I get a menu:
Cut, Copy, .. . Edit Text, Grouping, Order, Assign Macro, Format Control.
Right clicking this a second time seems to turn off design mode and "Assign
Macro" disappears from the list.
This does not toggle with the first list on reclicking. Design mode has to
be turned on again.

But another button "Find Unique Values", when I right click it, produces
Cut, Copy,....Properties, View Code, Command Button Object, Grouping, Order,
Format Control.

Note that the options "Properties" and "View Code" are not available for the
first button, and that "Assign Macro" is not available for the second
button.

The behaviour of the "Find Unique Values" button follows that described
(for commandbuttons) in Richard Shepherd's "Excel VBA Macro Programming".
When not in design mode, the button does not react to a right click; a macro
can be assigned by inserting it in Private Sub CommandButton1_Click(). In
the properties menu I find "CommandButton1 Command Button", and "Sheet6
Worksheet", but none of my other buttons are listed.

So it seems that the "Update" button, and other like it, cannot be
commandbuttons. The behaviour of the "Update" button does not follow
Shepherd. It responds to a right click when not in design mode, and offers
the option "Assign Macro". This is very convenient, but on the other hand I
cannot change the properties.So if such buttons are not commandbuttons, what
are they? If I right click "Update" and then call up "Properties" by right
clicking the sheet tab, I get properties for Sheet6 and the CommandButton1
only.

To confuse the issue further, the "Find Unique Values" button, on right
clicking, sometimes comes up with a different menu: "Move Here, Copy Here,
Cancel". Click "Cancel" and reclick, and the same menu appears. Click
somewhere outside the menu and reclick and the "Cut, Copy, Properties..."
options return. I have not fathomed how I can deliberately get the "Move
Here..." menu to pop up.

To sum up,
(1) can someone tell me how to change to properties of buttons to which I
can assign a macro,
and what kind of buttons are these, if not commandbuttons? What must I click
in the toolbox to get such a button?

(2) Alternatively, can one assign a macro to a commandbutton without having
to paste it into the code window?

(3) What causes the "Move/Copy Here" window to appear ? (And what is meant
by "Here"?)

Thanks in advance for any help!

RogerPB

Hello Roger,

Your problem stems from that there are 2 types of buttons. Excel has it's own set of controls that can be placed on the worksheet using the Forms toolbar. The other control are known as Control Toolbox or Active X controls which belong to VBA. This why you are seeing different properties for the controls.

Hopet his answers your question,
Leith Ross

Hi Roger,

Excel has 2 sets of command buttons - Forms and Controls. It would appear to
me that you have been using both of these. I find working with Controls
easier, and (I believe) that Excel only includes Forms for backward
compatibility.

To see the difference, when you insert a Form button, the default name is
"Button1", and this will give you the "Assign Macro" option. When you insert
a Control button, the default name is "CommandButton1" and will give you a
"View Code" option instead.

There are several differences in behaviour (more than I know about), but
this should explain why you are experiencing these problems.

I'm not sure about the Move/Copy here question.....could you be more
specific about what triggers it?

Hope this helps

Warm regards

Sunil Jayakumar

"Roger PB" <[email protected]> wrote in message
news:[email protected]...
>I have been trying for several days to attach some buttons to a worksheet,
>to set the properties caption and colour, and to assign a macro to each of
>them.
>
> Somehow I have actually achieved this with some of them, but I do not know
> how. When I right click a button (in design mode) I get different
> messages for each.
>
> For instance, one button has the caption "Update" and when I right click
> this I get a menu:
> Cut, Copy, .. . Edit Text, Grouping, Order, Assign Macro, Format Control.
> Right clicking this a second time seems to turn off design mode and
> "Assign Macro" disappears from the list.
> This does not toggle with the first list on reclicking. Design mode has to
> be turned on again.
>
> But another button "Find Unique Values", when I right click it, produces
> Cut, Copy,....Properties, View Code, Command Button Object, Grouping,
> Order, Format Control.
>
> Note that the options "Properties" and "View Code" are not available for
> the first button, and that "Assign Macro" is not available for the second
> button.
>
> The behaviour of the "Find Unique Values" button follows that described
> (for commandbuttons) in Richard Shepherd's "Excel VBA Macro Programming".
> When not in design mode, the button does not react to a right click; a
> macro can be assigned by inserting it in Private Sub
> CommandButton1_Click(). In the properties menu I find "CommandButton1
> Command Button", and "Sheet6 Worksheet", but none of my other buttons are
> listed.
>
> So it seems that the "Update" button, and other like it, cannot be
> commandbuttons. The behaviour of the "Update" button does not follow
> Shepherd. It responds to a right click when not in design mode, and offers
> the option "Assign Macro". This is very convenient, but on the other hand
> I cannot change the properties.So if such buttons are not commandbuttons,
> what are they? If I right click "Update" and then call up "Properties" by
> right clicking the sheet tab, I get properties for Sheet6 and the
> CommandButton1 only.
>
> To confuse the issue further, the "Find Unique Values" button, on right
> clicking, sometimes comes up with a different menu: "Move Here, Copy Here,
> Cancel". Click "Cancel" and reclick, and the same menu appears. Click
> somewhere outside the menu and reclick and the "Cut, Copy, Properties..."
> options return. I have not fathomed how I can deliberately get the "Move
> Here..." menu to pop up.
>
> To sum up,
> (1) can someone tell me how to change to properties of buttons to which I
> can assign a macro,
> and what kind of buttons are these, if not commandbuttons? What must I
> click in the toolbox to get such a button?
>
> (2) Alternatively, can one assign a macro to a commandbutton without
> having to paste it into the code window?
>
> (3) What causes the "Move/Copy Here" window to appear ? (And what is meant
> by "Here"?)
>
> Thanks in advance for any help!
>
> RogerPB
>
>
>
www.ayyoo.com/loans.html

There are two types of controls available from the toolbars. One is Forms
and the other is Control Toolbox. Buttons from the Forms toolbar respond to
right-click and assign macro. A button from the Control Toolbox responds to
the Design icon and code goes in Private Sub CommandButton1_Click().
Mike F
"Roger PB" <[email protected]> wrote in message
news:[email protected]...
>I have been trying for several days to attach some buttons to a worksheet,
>to set the properties caption and colour, and to assign a macro to each of
>them.
>
> Somehow I have actually achieved this with some of them, but I do not know
> how. When I right click a button (in design mode) I get different
> messages for each.
>
> For instance, one button has the caption "Update" and when I right click
> this I get a menu:
> Cut, Copy, .. . Edit Text, Grouping, Order, Assign Macro, Format Control.
> Right clicking this a second time seems to turn off design mode and
> "Assign Macro" disappears from the list.
> This does not toggle with the first list on reclicking. Design mode has to
> be turned on again.
>
> But another button "Find Unique Values", when I right click it, produces
> Cut, Copy,....Properties, View Code, Command Button Object, Grouping,
> Order, Format Control.
>
> Note that the options "Properties" and "View Code" are not available for
> the first button, and that "Assign Macro" is not available for the second
> button.
>
> The behaviour of the "Find Unique Values" button follows that described
> (for commandbuttons) in Richard Shepherd's "Excel VBA Macro Programming".
> When not in design mode, the button does not react to a right click; a
> macro can be assigned by inserting it in Private Sub
> CommandButton1_Click(). In the properties menu I find "CommandButton1
> Command Button", and "Sheet6 Worksheet", but none of my other buttons are
> listed.
>
> So it seems that the "Update" button, and other like it, cannot be
> commandbuttons. The behaviour of the "Update" button does not follow
> Shepherd. It responds to a right click when not in design mode, and offers
> the option "Assign Macro". This is very convenient, but on the other hand
> I cannot change the properties.So if such buttons are not commandbuttons,
> what are they? If I right click "Update" and then call up "Properties" by
> right clicking the sheet tab, I get properties for Sheet6 and the
> CommandButton1 only.
>
> To confuse the issue further, the "Find Unique Values" button, on right
> clicking, sometimes comes up with a different menu: "Move Here, Copy Here,
> Cancel". Click "Cancel" and reclick, and the same menu appears. Click
> somewhere outside the menu and reclick and the "Cut, Copy, Properties..."
> options return. I have not fathomed how I can deliberately get the "Move
> Here..." menu to pop up.
>
> To sum up,
> (1) can someone tell me how to change to properties of buttons to which I
> can assign a macro,
> and what kind of buttons are these, if not commandbuttons? What must I
> click in the toolbox to get such a button?
>
> (2) Alternatively, can one assign a macro to a commandbutton without
> having to paste it into the code window?
>
> (3) What causes the "Move/Copy Here" window to appear ? (And what is meant
> by "Here"?)
>
> Thanks in advance for any help!
>
> RogerPB
>
>
>

Thanks to Sunil and Mike,

I had in the meantime, since writing, with the help of the Help screen
fathomed out that I had been using bothForms and Controls
I would agree that using Controls gives one greater control, because one can
define properties.

On the other hand, assigning a macro with a click is more convenient than
having to write or paste code.
But it seems there is no way to change, for instance, the colour of a
button made using Forms.

As regards the copy/move question, the problem is precisely that I don't
know what triggers this menu. It came up sporadically both with Forms and
Controls! And if you should click the option "Move here", what is moved
where?

Thanks again,

Roger PB

"Sunil Jayakumar" <sunil.jayakumar[at]gmail.com> schrieb im Newsbeitrag
news:[email protected]...
> Hi Roger,
>
> Excel has 2 sets of command buttons - Forms and Controls. It would appear
> to me that you have been using both of these. I find working with Controls
> easier, and (I believe) that Excel only includes Forms for backward
> compatibility.
>
> To see the difference, when you insert a Form button, the default name is
> "Button1", and this will give you the "Assign Macro" option. When you
> insert a Control button, the default name is "CommandButton1" and will
> give you a "View Code" option instead.
>
> There are several differences in behaviour (more than I know about), but
> this should explain why you are experiencing these problems.
>
> I'm not sure about the Move/Copy here question.....could you be more
> specific about what triggers it?
>
> Hope this helps
>
> Warm regards
>
> Sunil Jayakumar
>
> "Roger PB" <[email protected]> wrote in message
> news:[email protected]...
>>I have been trying for several days to attach some buttons to a worksheet,
>>to set the properties caption and colour, and to assign a macro to each of
>>them.
>>
>> Somehow I have actually achieved this with some of them, but I do not
>> know how. When I right click a button (in design mode) I get different
>> messages for each.
>>
>> For instance, one button has the caption "Update" and when I right click
>> this I get a menu:
>> Cut, Copy, .. . Edit Text, Grouping, Order, Assign Macro, Format Control.
>> Right clicking this a second time seems to turn off design mode and
>> "Assign Macro" disappears from the list.
>> This does not toggle with the first list on reclicking. Design mode has
>> to be turned on again.
>>
>> But another button "Find Unique Values", when I right click it, produces
>> Cut, Copy,....Properties, View Code, Command Button Object, Grouping,
>> Order, Format Control.
>>
>> Note that the options "Properties" and "View Code" are not available for
>> the first button, and that "Assign Macro" is not available for the second
>> button.
>>
>> The behaviour of the "Find Unique Values" button follows that described
>> (for commandbuttons) in Richard Shepherd's "Excel VBA Macro
>> Programming". When not in design mode, the button does not react to a
>> right click; a macro can be assigned by inserting it in Private Sub
>> CommandButton1_Click(). In the properties menu I find "CommandButton1
>> Command Button", and "Sheet6 Worksheet", but none of my other buttons are
>> listed.
>>
>> So it seems that the "Update" button, and other like it, cannot be
>> commandbuttons. The behaviour of the "Update" button does not follow
>> Shepherd. It responds to a right click when not in design mode, and
>> offers the option "Assign Macro". This is very convenient, but on the
>> other hand I cannot change the properties.So if such buttons are not
>> commandbuttons, what are they? If I right click "Update" and then call
>> up "Properties" by right clicking the sheet tab, I get properties for
>> Sheet6 and the CommandButton1 only.
>>
>> To confuse the issue further, the "Find Unique Values" button, on right
>> clicking, sometimes comes up with a different menu: "Move Here, Copy
>> Here, Cancel". Click "Cancel" and reclick, and the same menu appears.
>> Click somewhere outside the menu and reclick and the "Cut, Copy,
>> Properties..." options return. I have not fathomed how I can deliberately
>> get the "Move Here..." menu to pop up.
>>
>> To sum up,
>> (1) can someone tell me how to change to properties of buttons to which I
>> can assign a macro,
>> and what kind of buttons are these, if not commandbuttons? What must I
>> click in the toolbox to get such a button?
>>
>> (2) Alternatively, can one assign a macro to a commandbutton without
>> having to paste it into the code window?
>>
>> (3) What causes the "Move/Copy Here" window to appear ? (And what is
>> meant by "Here"?)
>>
>> Thanks in advance for any help!
>>
>> RogerPB
>>
>>
>>
> www.ayyoo.com/loans.html
>
>