Identifying current selection textbox/shape

[JoeyL]

I cant figure out how to detect in vba what textbox I have selected. Details:

I am trying to make it so a user can select a pre-placed textbox and then press a macrobutton to add another textbox below the one that was selected (with a connector arrow between the two boxes). The textboxes are about 45x120, and the space between the selected textbox and the new created textbox is the same size as a textbox. The user is then able to move the new textbox around, keeping the arrow connector attached to the two text boxes. From there, the user may select any of the textboxes that they have added, and then add another below theat selected textbox, on and on, etc..

Any help? Im desperate!

 

[Gary McMaster]

I suspect that the root of your problem is that it depends on how you select the text box and whether or not it goes into "text edit" mode when you select it.

If I make sure the border is selected and it's not in text edit mode then run the following sample, it returns the name and I can use that to set the variable.

Public Sub Test()

MsgBox Selection.Name

Dim oShape As Shape

Set oShape = ActiveSheet.Shapes(Selection.Name)
'oShape.OnAction = "Test2"

End Sub

You could also use "Application.Caller" and assign a common macro to all of the text boxes. Then you could control if and when it goes into edit mode as well as all of the other properties. If needed you could display a user form that allows the user to choose what they want to do with their selection. Edit text, copy, move etc.

Public Sub Test2()

'Assign "Test2" as the "OnAction" macro for all text boxes
Dim oShape As Shape

Set oShape = ActiveSheet.Shapes(Application.Caller)

oShape.Select

MsgBox oShape.Name

End Sub

Gary

 

[JoeyL]

Hey Gary! Thanks for the responce.

What if they do both? The users are not computer users. They have a hard time just opening the excel template. Having them do anything like insert textboxes, know if they have correctly selected a textbox, what a textbox is... etc. is not an option.

What if they have the textbox in edit, or if they have it in border selection, but by pressing the big green macrobutton at the top of the page a new textbox will magically appear below the textbox they have selected.

I'm thinking (with my limited knowledge) that it would look something like:
dim Selected_Textbox as shape
dim New_Textbox as shape
Selected_Textbox = ActiveSheet.Shapes(Selection.Name)
New_Textbox = (parameters for new textbox size)
When macro_pressed
Insert New_textbox below Selected_Textbox offset.by(3,6)
Insert arrow_connector from center_bottom connector_node of Selected_Textbox to center_ top connector_node of New_Textbox
End when

Sorry about my code butchery, I hope I conveyed my meaning.

I am currently using the following to insert a textbox into the selected cell (which isnt what I need).

Sub InsertSimpleTextBox1()
'
' InsertSimpleTextBox1 Macro
'
'
Dim Left As Double, Top As Double
With ActiveSheet
Left = ActiveCell.Left
Top = ActiveCell.Top
.Shapes.AddTextBox msoTextOrientationHorizontal, Left, Top, 95, 45
End With

End Sub

Could ActiveCell be replaced with something like ActiveShape?

 

[Gary McMaster]

You can trigger the code by clicking the shape without adding an extra button.

Please try sample below in a new workbook.

Gary

Option Explicit

Sub InsertSimpleTextBox1()
'
' InsertSimpleTextBox1 Macro
'
'
Dim Left As Double, Top As Double
Dim oShape As Shape
'With ActiveSheet
Left = ActiveCell.Left
Top = ActiveCell.Top
Set oShape = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, Left, Top, 95, 45)
oShape.OnAction = "Click_Shape"
'End With

End Sub

Public Sub Click_Shape()

Dim oShape As Shape

'All shapes processed by this procedure
Set oShape = ActiveSheet.Shapes(Application.Caller)

MsgBox oShape.Name

With oShape
    .Top = .Top + 10
    .Left = .Left + 10
    .Fill.ForeColor.RGB = RGB(255, 0, 0)
End With

'Maybe ask user if they want to text edit this shape or make new

'Use position of this shape to add another shape
'Set oShape = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, newLeft, newTop, 95, 45)
'oShape.OnAction = "Click_Shape"

oShape.Select 'Select to edit

End Sub

 

[JoeyL]

Thanks for helping Gary. I tried the new code in a new workbook as you said.

I am not sure the reason for the msgbox (although, it would be a cool thing if I could enter text into that msgbox to have that text transfer into the selected textbox, as the only way to edit the text inside).

If I click on the inserted red textbox, the msgbox comes up. When I select OK on the msgbox, the red textbox shifts right and down and becomes text editable.

I'm trying to do something like (with the red textbox selected):

[click this button to add a new box]

[textbox] [textbox] [textbox] [textbox]
................................V.................
...........................[NewBox]............

If the button is pressed with a textbox selected (any text box), then a new textbox is created below the selected textbox. If the textbox is selected or if the textbox is selected and in text edit mode, a new box should still be created when the button is pressed.

 

[Gary McMaster]

The msgbox is not needed. It it there just to show you that the system knows the name of the shape that was just selected, your original question.

Maybe you could create a user form that will be displayed inside the "Click_Shape" procedure. The form could ask the user what they want to do with the shape they just selected. Maybe edit the text in the box, maybe create a new text box beneath the one they clicked. Whatever you have in mind.

Gary