Multiple Object Right Click Event

[Kenny]

I am working on a project that will allow users to programatically add more
activex textboxes to a sheet (not a form). I need to figure out a way to
either add right click code to these text boxes programatically or write a
procedure that will detect the right click on any textbox on a sheet. There
is no set amount of textboxes so I cannot just program all the textboxes for
a right clcik event. I need to add the code programatically or detect all
objects being clicked and be able to determine which one. This will fire a
context menu I already have written. THANKS!

 

[OssieMac]

Hi Kenny,

I am wondering why you need to detect a right click on the text box. Perhaps
if you explain what it is you are trying to do then someone might have an
alternative way of achieving the desired outcome. A double click event comes
to mind.

 

[Kenny]

Hello OssieMac and thanks for responding.

I will have multiple text boxes in a line on a sheet that will hold certain
information. If the user wants to add another row of information - the
program will add another set of textboxes programatically. The right click
event is for a context menu to cut, copy and paste. I already have this
written.

I want to use text boxes because the users of the current spreadsheet do not
like having to double click on cells to enter information. I tried sending F2
to them, but this is a little quirkey. Of course if there was some kind of
table control I could add in to the sheet - that did not require its column
width to be the same as the sheet under it. Or did not require a double click.

This is basically going to be a record holder. It cannot be in a user form -
it has to be on a sheet and it requires a context menu to cut copy and paste.

Thanks for you help!

 

[Dave Peterson]

This would go into a General module...

Option Explicit
Dim TBoxes() As New Class1
Sub Autpen()

Dim OLEObj As OLEObject

TBoxCount = 0
For Each OLEObj In Worksheets("sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
TBoxCount = TBoxCount + 1
ReDim Preserve TBoxes(1 To TBoxCount)
Set TBoxes(TBoxCount).TBoxGroup = OLEObj.Object
End If
Next OLEObj

End Sub

 

[Dave Peterson]

Hit the send key too fast. Ignore that other message...

This would go in a general module:

Option Explicit
Public TBoxes() As New Class1
Public TBoxCount As Long
Sub Autpen()

Dim OLEObj As OLEObject

TBoxCount = 0
For Each OLEObj In Worksheets("sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
TBoxCount = TBoxCount + 1
ReDim Preserve TBoxes(1 To TBoxCount)
Set TBoxes(TBoxCount).TBoxGroup = OLEObj.Object
End If
Next OLEObj

End Sub

It ties the existing textboxes from the Control toolbox toolbar to a single
class -- where the code to handle the rightclick would go.

If you add more textboxes from the control toolbox to the worksheet, you'll have
to add it to that TBoxes array (increment the TBoxCount, too).

(I was changing those Dim's to Public's when I hit the send key earlier!)

And this is the Class1 Module.
(Insert|Class Module)
It has to be called Class1 (or whatever you want--but you'll have to match the
code).

Option Explicit
Public WithEvents TBoxGroup As MSForms.TextBox
Private Sub TBoxGroup_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

If Button = xlSecondaryButton Then
MsgBox TBoxGroup.Name & vbLf & "had the rightclick button pressed"
Else
MsgBox TBoxGroup.Name & vbLf & "not the rightclick button"
End If
End Sub


The class module stuff was stolen from John Walkenbach's site:
http://spreadsheetpage.com/index.php/file/multiple_userform_buttons_with_one_procedure/

With minor changes to use textboxes, not commandbuttons.