If Then Statement to Apply Code for TextBox

[drmingle]

I would like to apply the following code (only if the shape is not already placed in the location):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    
    Sheets("Pricing").Shapes.AddTextBox(msoTextOrientationHorizontal, 807.3529133858, _
        5.2940944882, 632.6470866142, 180.8823622047).Select
    Selection.ShapeRange.Line.Visible = msoFalse
    Range("F10:J10").Select
    Sheets("Pricing").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ThisWorkbook.Save
End Sub

For example,

If <textbox> worksheet area doesn't have a textbox then
apply the above code.
Else
exit sub

Currently if I use the above code a second time it throws an error.

Any help would be appreciated.</textbox>

 

[tlowry]

Here's one approach:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If CtrlExists(SheetName:="Pricing", Ctrltype:=msoTextBox) = False Then
        Sheets("Pricing").Shapes.AddTextbox(msoTextOrientationHorizontal, 807.3529133858, _
        5.2940944882, 632.6470866142, 180.8823622047).Select
        Selection.ShapeRange.Line.Visible = msoFalse
        Range("F10:J10").Select
        Sheets("Pricing").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        ThisWorkbook.Save
    End If
End Sub

Function CtrlExists(SheetName, Ctrltype) As Boolean
    CtrlExists = True
    Dim shp
    For Each shp In Sheets(SheetName).Shapes
        If shp.Type = Ctrltype Then Exit Function
    Next shp
    CtrlExists = False
End Function

 

[drmingle]

tlowry,

Works perfectly...

Thank you.

 

[drmingle]

tlowry,

I added one line of code to assign a name to the text box created. I need to have it assigned since there are times that I will want to delete the textbox after I have created it and need to reference the object to do so...

Any ideas?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False

    If CtrlExists(SheetName:="Pricing", Ctrltype:=msoTextBox) = False Then
        ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 803.8235433071, _
        1.7647244094, 607.9411811024, 189.7058267717).Select
        Selection.ShapeRange.Line.Visible = msoFalse
        Selection.Name = "txtBox1"
        Range("F10:J10").Select
        
        ''
        Range("D16:O634").Select
        Selection.Locked = True
        Selection.FormulaHidden = True
        Range("Q16:AG55").Select
        Range(Selection, Selection.End(xlDown)).Select

        Selection.Locked = True
        Selection.FormulaHidden = True

        ''
        Sheets("Pricing").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        ''
        Range("B16").Select
        ''
        ThisWorkbook.Save
        ''
    End If
    
    
Application.ScreenUpdating = True
End Sub

 

[tlowry]

Here's a suggestion...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If CtrlExists(SheetName:="Pricing", Ctrltype:=msoTextBox) = False Then
        Sheets("Pricing").Shapes.AddTextbox(msoTextOrientationHorizontal, 807.3529133858, _
        5.2940944882, 632.6470866142, 180.8823622047).Select
        '
        [COLOR=#008000][B]Selection.Name = "TextBox_Godzilla01"[/B][/COLOR]
        '
        Selection.ShapeRange.Line.Visible = msoFalse
        Range("F10:J10").Select
        Sheets("Pricing").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        ThisWorkbook.Save
    End If
End Sub

Function CtrlExists(SheetName, Ctrltype) As Boolean
    CtrlExists = True
    Dim shp
    For Each shp In Sheets(SheetName).Shapes
        If shp.Type = Ctrltype Then Exit Function
    Next shp
    CtrlExists = False
End Function