This would go into a General module...

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

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



Kenny wrote:

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!

"OssieMac" wrote:

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.

--
Regards,

OssieMac


"Kenny" wrote:

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!


--

Dave Peterson