Search code examples
vbaexcelexcel-2013

Find & Replace Textboxes and Macros

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.

Solution

  • 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