You need to set up a class to handle all the textboxes. You don’t get as many events in the class as in the worksheet module, but you get enough.
Insert a new class module, call it CText. After “Option Explicit” at the top, add this:
Choose MyText from the left hand dropdown atop the module, then click on Enter in the right hand dropdown. You get an Event procedure like the following. I’ve inserted the MsgBox just to show that it worked and could identify which textbox was clicked on.
Public WithEvents MyText As MSForms.TextBox
Now you need to activate the textboxes. Put this into the declarations section of a regular module:
Private Sub MyText_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
and insert these procedures:
Dim TheTextBoxes() As New CText
Run the activation procedure, then click in a textbox. The message box will tell you which one it is, and you can follow up with whatever you need to do. I think I would put my procedures into the main module like this (untested):
Dim sh As Shape
Dim iTextBoxCount As Long
ReDim TheTextBoxes(1 To 1)
iTextBoxCount = 0
For Each sh In ActiveSheet.Shapes
If sh.OLEFormat.Object.OLEType = xlOLEControl Then
iTextBoxCount = iTextBoxCount + 1
ReDim Preserve TheTextBoxes(1 To iTextBoxCount)
Set TheTextBoxes(iTextBoxCount).MyText = sh.OLEFormat.Object.Object
Dim iTexts As Long
On Error Resume Next
For iTexts = LBound(TheTextBoxes) To UBound(TheTextBoxes)
Set TheTextBoxes(iTexts).TheText = Nothing
Sub ProcessTextBox(txt as MSForms.TextBox)
If txt.Text = Range("TextBox1_default").Value Then txt.Text = ""