MouseUp and Down events for command buttons

[popper]

Hi all

Using VBA in Excel, I want to intercept a mouseup and mousedown event for a command button dragged off the control toolbox and placed in the spreadsheet.
I have used the declaration

Private Sub CommandButton1_mousedown(button, shift, x, y)

and also the version with "Byval button as long" etc but to no avail. I get the following message:


"Procedure declaration does not match description of event or procedure having the same name"

I am able to use the 'Click' event. What am I doing wrong?

With thanks

 

[xlbo]

Don't think you can do that without class modules

Events are attached to specific objects. If the button is being dragged off the commandbar onto the spreadsheet, it won't have any events associated with it so there is nothing for you to hook into...you can;t create a generic event for a command button without getting into class modules....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

Use object/event dropdowns to build event procedure. In the above case they generate the following:

Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

End Sub

This is useful for event procedures that depend on location of control.

combo

 

[popper]

Thanks Geoff and Combo for the responses.

Geoff: However, I am able to generate a Click event on this button, so why not a mousedown and mouseup. What are class modules and should I get into them to solve this?


Combo: Don't really understand the response. Can you elaborate a little further?

Regards

 

[xlbo]

Yes but I bet you're generating the click event for the button AFTER you've dragged it off the controls menu

To create the event dynamcially for any button that gets dragged off the controls toolbox, you would need to dynamically write the code based on the control's name

CommandButton1_MouseDown

will only work for a control called command_button1 and there will only ever be 1 of those in any given workbook...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

You use wrong argument types (all variants instead of Integer, Integer, Single, Single).
What I suggested was to allow vba to create procedure definition for you. With proper name, arguments definitions and for event that can be handled.

Each worksheet has (or can have, that is created when you display vbe window) code module - a class module that extends standard worksheet object. This is the room for event procedures for worksheet and activex objects that are in it.
When you double-click the command button the default procedure (Click) is created and you are redirected to it. From the right dropdown you can select any other available event and vba will properly define it.
There are all objects that can generate events in the left dropdown list. When you select any, you have the situation similar to described above.

combo

 

[popper]

Thanks again Geoff and Combo. I did not quite follow your instructions, particularly where to right and left click for the dropdowns (Combo) and 'dynamically write code" (Geoff) but when I corrected the definitions, it worked fine.

Would love to understand that stuff eventually.

Thanks guys (girls?).

Regards

Popper

 

[combo]

1. go to vbe,
2. display sheet's code module (double-click object in project evplorer window,
3. on the top you should see two drop-downs (with (general) - object selector, (declarations) - declarations, general procedures and object events if object in the left one selected).

combo