VBA code to function differently on right & left click

[hrayani]

Dear Friends,

If Possible, a small VBA code is required which I would assign to a shape named "Index"

I want the Range("A1").value = "Right" when the shape is right clicked
I want the Range("A1").value = "Left" when the shape is left clicked

Regards,

Humayun

 

[Micron]

Shapes on a sheet have no mouse events AFAIK. So a work around is to put an ActiveX control such as a label, on top of your shape. You'd have to set the label properties so that it blends in, and the label and shape borders would have to be very close in proximity. For instance, a rectangular label on a circle or oval may leave shape areas without being able to click on the label because it's not at that location.

Then you'd have to set label properties so that you can't see it, otherwise, you have to worry about the fact that the label will take the focus and show as white (or perhaps some other colour). So matching background color and no border ought to work. That can be difficult because shape palette values are different from control values. F'rinstance, these 2 colours are the same: #4472C4 (shape) and &H00C47444& (activex label) so you need a conversion calculator to convert 44 to Hex 2C, and then you have to realize that the order of the values are reversed in one system vs the other. Notice above that in the shape, C4 is at the end, whereas in the label, it comes first (Excel should put the padded zeros in for you).

A lot of kluding around, so you may want to rethink the goal.
EDIT - forgot to mention that to know which button was clicked you'd use the Button parameter value that the event passes to the sub. Primary button is 1, other is 2.

 

[hrayani]

Hi,

Thanks for a detailed reply

So, I can use an active x command button instead of a huge work around
Can you pls come back with a piece of code which code work with the command button

Regards,

Humayun

 

[Micron]

I'm assuming you don't want things inside of your shape to be noticeable, so I don't think I'd use a button because it has 2 visual states; one normal, one depressed. Clicking it will change normal view to sunken appearance. Then there are the normal, hover and clicked colours whereas a label has only one background colour. If you want to use a button that's your call. You could adapt this label click code to suit if you manage to deal with all the visual variations that a button will give.

Private Sub Label1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'put your own code in this section for what you want to happen with right vs left click
If Button = 1 Then
    'do something
Else
    'do something else
End If

ActiveCell.Select
End Sub

 

[hrayani]

Thanks, but where do I get the label from ??
I can't find it in the active x shapes ??

 

[Micron]

See the highlighted A

 

[hrayani]

Thanks ... Got the label but nothing happens with the clicks

Private Sub Label1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'put your own code in this section for what you want to happen with right vs left click
If Button = 1 Then
    Range("A1").Value = "Right"
Else
    Range("A1").Value = "Left"
End If

ActiveCell.Select
End Sub

 

[Micron]

Right click on the label, choose Assign Macro, then choose New.
You will end up with a click event sub that you don't need. Paste the code I gave you below that then try it. You can remove the click event code, comment it out or leave it.

This is basic stuff that you can research if you don't know how to do something. Like Googling "Excel vba how to assign macro to label"

 

[hrayani]

Sorry, but I do not see assign macro option when I right click the label

 

[Micron]

Then what do you see? Post a pic? Surely you can see it in the piece of the menu that I'm posting?