Change formula for Textbox Shape in VBA

[IM4FLSt]

Hi guys

So I have recorded a macro that changes the formula for a textbox shape. It gave me the following:

ActiveSheet.Shapes("TextBox 1").Select
Selection.Formula = "=A1"

I would like to know how to do this without selecting the shape. Something like:

ActiveSheet.Shapes("TextBox 1").Formula = "=A1"

but of course this code does not work. Any help would be greatly appreciated.

Thanks

 

[Gary McMaster]

Try something like the sample below.

Hope it helps.

Gary

Public Sub Test()

Dim oTextbox As TextBox

For Each oTextbox In ActiveSheet.TextBoxes
    Debug.Print oTextbox.Name
Next oTextbox

'Change text box name to match a name in immediate (debug) window
ActiveSheet.TextBoxes("Text Box 1").Formula = "A2"

End Sub

 

[IM4FLSt]

Gary,

Thanks for the response. Your suggestion helped. The only weird thing I run into now is when the textboxes are grouped, it causes an error with the code, almost like it doesn't see them as textboxes anymore. So that is kinda strange, but I can deal with that. Again thanks for the help.

John

 

[Gary McMaster]

John,

When shapes are grouped they behave as one. Even Shapes.Count will report only one shape for that group.

A couple things you could look at are "GroupItem", which lets you address individual items inside of a group and "ShapeRange". ShapeRange acts as a container that will allow you to temporarily ungroup a number of shapes while remembering what composed the group. Once the shapes are in the ShapeRange you can make changes to the ungrouped shapes within and then regroup the shapes by referencing the ShapeRange.

Gary

 

[Gary McMaster]

John,

Here's a sample that I hope will demonstrate some aspects the "GroupItem" and "ShapeRange" topics that I mentioned.

Hope it helps.

Gary

Public Sub Test()
 
'Setup:
 
'Use a new workbook with this code in a standard module (written for XL2007)
 
'Requires 2 or more "GROUPED" Text boxes (not ActiveX) on the active sheet (group manually via GUI)

'One of the text boxes must be named "MyTextBox" prior to grouping
 
'Place some text in cell "A2"
 
Dim oShape As Shape
Dim oShapeRange As ShapeRange
Dim oTextBox As TextBox
Dim oMyGroup As Shape
 
For Each oShape In ActiveSheet.Shapes
    If oShape.Type = msoGroup Then
    
        'Change all text boxes to blue as a group
        oShape.Fill.ForeColor.RGB = RGB(0, 0, 255)
        
        Debug.Print oShape.Name
        Debug.Print "Grouped Items Count: " & vbTab & oShape.GroupItems.Count
        Debug.Print oShape.GroupItems(2).Name ' Name unknown
        Debug.Print oShape.GroupItems("MyTextBox").Name
        
        'Change text box named "MyTextBox" to red while still grouped
        oShape.GroupItems("MyTextBox").Fill.ForeColor.RGB = RGB(255, 0, 0)
        
        'Shape count changes as byproduct of Group/UnGroup
        Debug.Print "Shape Count: " & ActiveSheet.Shapes.Count
        
        Set oShapeRange = oShape.Ungroup 'Preserve group members in shape range
        
        Set oTextBox = ActiveSheet.TextBoxes("MyTextBox")
        
        Debug.Print "Shape Count: " & ActiveSheet.Shapes.Count
            
        'oShapeRange.Group 'Note that this line will cause the group name to change automatically
        
        Set oMyGroup = oShapeRange.Group 'Alternative for above line to preserve name
        oMyGroup.Name = "MyGroup" 'Name the group for future ID
        
        Debug.Print "Shape Count: " & ActiveSheet.Shapes.Count
        
        oTextBox.Formula = "A2" 'Need some text in cell A2
            
    Else
        'Code for single (ungrouped) shapes
    End If
    
Next oShape
 
End Sub

 

[IM4FLSt]

Wow Gary,

Thanks for all your help. You've been great!

John

 

[steveapa]

Hi Gary. I was reading this post from a couple years ago, it is exactly what I need. Everything seems to work as you explain, but when I try to assign a formula, it does not appear to have a formula.. In the watch window i have listed, , I see "******** doesn't support this property or method>. I ungrouped this text box from it's group to see If I could reset the formula ungrouped and get the same message - In watch window, the expression is 'Sheets("#-Process (3)").Shapes("Text Box 581").Formula'. I get the same error message. I do see all the other values of the text box, but there is no Formula? Thanks... Steve

I am using excel 2013

 

[Gary McMaster]

Hi Steve,

I only have XL 2000. I'm not sure I can help if it is related to XL2013.

Maybe if you post all the code you have someone else can try it with a newer version.

Gary

 

[steveapa]

SOLVED! Hi Gary. I used what you had plus some other info I found and I got it to work. You code helped me, so I will share what I have learned. Here was the original problem... I have a sheet with a group of text boxes. These text boxes, most, reference other cells on the same sheet. When I copy this sheet to a new sheet, the text boxes still have the formula in them, but when you change the referenced cell, the text bus does not update. I found that in the new sheet, if I selected a text box - hit F2 to edit the cell, placed the cursor in the formula bar and hit enter, the link started working again. So, I wanted to use VB to read the formula and reset it to the same thing. That is what I did. I could not find the formula under the 'Sheets("#-Process (3)").Shapes("Text Box 581").Formula' to reset it. Here is the code

Public Sub AddAdditionalProcessTab()Dim oShape As Shape
Dim i As Integer


    Application.ScreenUpdating = False
    Sheets("#-Process (1)").Visible = True
    Sheets("#-Process (1)").Select
    ActiveSheet.Buttons.Add(291, 65.4, 94.2, 16.8).Select
    Sheets("#-Process (1)").Copy Before:=Sheets("Component_List")
    Sheets("#-Process (1)").Visible = False
    
    '  This is needed because, when the grouped text boxes are copied, they loose
    '   their formula.  I can hit F2 and enter to correct it, or the below corrects it
    
    For Each oShape In ActiveSheet.Shapes
        If oShape.Type = msoGroup Then
            For i = 1 To oShape.GroupItems.Count
                If oShape.GroupItems(i).Type = msoTextBox Then
                    oShape.GroupItems(i).Select
                    Selection.Formula = Selection.Formula
                End If
            Next i
        End If
    Next oShape
    Cells(1, 1).Select
    Application.ScreenUpdating = True
End Sub

I was weird. When I evaluated the formula during debug, it appeared that the formula changed. It should be '

= A1

', but it was '

=A1

' - no space between the equals and the cell reference. By setting the formula to itself, excel corrected the syntax. Hope I could be of some help to you.

Thanks Again.
Steve.

PS I am self taught

 

[Gary McMaster]

Steve,

Thanks for the feedback.

Gary