How can I tell which DRAWING textbox is active?

[ChgsAllAround]

I have a sheet with 30 textboxes made with the drawing toolbar. The user enters data, then clicks on a button which formats the page to adjust row size to match amount of info. I am having trouble finding the code to store the currently selected textbox in a variable so that I can place the focus back to the same textbox when the macro ends. Can anyone help me with the code to get the active textbox? Thanks!

 

[RoryA]

Selection.Name would give you the name of the active textbox.

 

[ChgsAllAround]

Thanks, Rory. This works, but only if my sheet is not protected. Unfortunately, I have to have the sheet protected and even if I unprotect the sheet in code, I still get an error. Here is the starting code:
Sub FormatPage()
Dim WhereWasI As String
ActiveSheet.Unprotect Password:="xxxxxx"
WhereWasI = Selection.Name
.......
........

I get a "Application-defined or Object-defined error"

Any ideas on how to get around this?

 

[RoryA]

I can't reproduce that - you should be able to access the selection name even if the sheet is still protected, assuming you have a textbox selected. What kind of button are you using (Forms toolbar?) and which version of Excel? Also, are you sure which line is causing the error?

 

[ChgsAllAround]

The button is a drawing toolbar rectangle with the Format Page macro assigned to it. Excel version is 2003. Yes, I'm sure it's that line as the line is highlighted each time. When the sheet is unprotected through code, if I hold the mouse over the words "Selection.name" while the macro is running, the value is empty. If I don't have the sheet protected at all before running the macro, "Selection.name" does have the name of the textbox as it's value. I have verified multiple times that one of the textboxes is selected before clicking on the rectangle.

 

[RoryA]

Presumably you have checked the Edit Objects option when protecting the sheet?

 

[Norie]

You might want to have a look at Application.Caller.

Though it isn't actually clear what you are trying to achieve.

 

[ChgsAllAround]

Rory, Yes, I have Edit Objects checked and still get the error. Plus, while having that option enabled, it allows my users to actually move the boxes around, which is not a good.

Norie,
I'm just trying to send the active textbox to VBA. I need something that traps the name of the textbox or the location of the cell behind it. There's just something about drawing textboxes that just doesn't follow the normal rules. With the sheet protected before running the macro, even with unprotecting the sheet in code first, all I get is an error due to the fact that both "selection.name" and "application.caller.value" are empty.

 

[ChgsAllAround]

I've made it work. I was using the "application.caller" incorrectly. Here is my current code:

Sub FormatPage()
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="xxxxx"
Set LastCell = ActiveSheet.Shapes(Application.Caller)
.....
......
Application.ScreenUpdating = True
LastCell.Select
ActiveSheet.Protect Password:="xxxxx"
End Sub

Now my only problem is that the last shape clicked on was the button to run the macro, not the textbox that was last entered into. bummer...guess I haven't gained anything except more knowledge!

Thanks!

 

[RoryA]

I cannot replicate that problem with 2003. (if you don't have edit objects enabled, you can't select the text box). If you add a Msgbox TypeName(Selection) line before you try and access the Selection.Name, what do you get?