How can I tell which DRAWING textbox is active?

ChgsAllAround

New Member
Joined
Apr 11, 2008
Messages
36
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!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Selection.Name would give you the name of the active textbox.
 

ChgsAllAround

New Member
Joined
Apr 11, 2008
Messages
36
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
Apr 11, 2008
Messages
36
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Presumably you have checked the Edit Objects option when protecting the sheet?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,539
Office Version
365
Platform
Windows
You might want to have a look at Application.Caller.

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

ChgsAllAround

New Member
Joined
Apr 11, 2008
Messages
36
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

New Member
Joined
Apr 11, 2008
Messages
36
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,359
Messages
5,444,005
Members
405,259
Latest member
charliechaz

This Week's Hot Topics

Top