Find & Replace Textboxes and Macros

Question

Is there a way to find and replace embedded macros in textboxes?

Example. I create a lot of textboxes and I assign them macro's say RunBox.1 WalkBox.1 SleepBox.1 now lets say I copy those textboxes. It will have the macro's I assigned in the box earlier in it and I'd like to change .1 to .2

Dim tb As TextBox
Dim actionName As String 

Sub AAA()    
    For Each tb In ActiveSheet.TextBoxes
        actionName = tb.OnAction
        actionName = Replace(actionName, "CGSF", "KBTUGSF")
        tb.OnAction = actionName Next 
End Sub

This isn't working not quite sure what I need to do.

score 1 · Accepted Answer · answered Aug 15 '14 at 16:52

1

Change the textbox's OnAction property.

Dim tb as TextBox
Dim actionName as String

For Each tb In ActiveSheet.TextBoxes
    actionName = tb.OnAction
    actionName = Replace(actionName, ".1", ".2")
    tb.OnAction = actionName
Next

answered Aug 15 '14 at 16:52

David Zemens

53,033
11
81
130

This is giving me a Run-Time Error 1004 Unable to set the OnAction property of the TextBox class – Michael Downey Aug 15 '14 at 17:16
For reference this is one of their Range Names TextBox 80 – Michael Downey Aug 15 '14 at 17:17
1

Is `RunBox.1 WalkBox.1` names of the TextBoxes, or names of the *procedure/macro* assigned to the textboxes? – David Zemens Aug 15 '14 at 17:23
1

and what does a Range Name have to do with any of this? – David Zemens Aug 15 '14 at 17:24
1

I guess what I'm trying to find out is what you're actually trying to change? The textbox's `.Name` can be changed the same way, just use `.Name` instead of `.onAction`. – David Zemens Aug 15 '14 at 17:24
The macro's assigned to the textboxes – Michael Downey Aug 15 '14 at 17:29
I guess it doesn't have much to do with it, although it is the name of the textbox. Mostly was pointing that out to verify it was a textbox – Michael Downey Aug 15 '14 at 17:30
1

any chance the sheet is maybe protected? When I test this, it works exactly as I'd expect. I can even give the `onAction` property a named macro that doesn't exist (of course it will error whenyou click it, but you can still assign an invalid named macro). – David Zemens Aug 15 '14 at 17:44
I opened a new Excel file and created a new Text box and it works. Thanks! – Michael Downey Aug 15 '14 at 17:57

Find & Replace Textboxes and Macros

1 Answers1