MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA Which Text Box Are they In?

Posted by Bill on October 27, 2000 4:24 PM

When the user invokes the macro, they may either be in a cell or in a text box. I was using this code to determine where they are:
On Error Resume Next
ThisRow = Selection.Row
On Error GoTo 0
If ThisRow = "" Then
ThisRow = Selection.TopLeftCell.Row
End If
This worked fine until we added protection to the sheet. On Format - TextBox - Protection, the TextBox is Locked, but LockText is unchecked. This allows the user to edit text in the text box.

When the sheet is protected and the user starts to edit the text box, then hits a button to run the macro, the above code does not report that the user is in a text box. Instead, it reports the last row of the cell where the cellpointer was previous to the user editing the text.

This is incredibly vexing. The Name Box (next to the formula bar) correctly shows that they are in Text Box 1517, but I can not find a way for VBA to tell me which text box they are editing.

Any ideas would be appreciated.


Posted by Ivan Moala on October 28, 2000 1:04 AM

When protecting the sheet deselect protect Object
or vai code
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _