How To Use Macro To Delete Textbook Everytime

[drmingle]

{{When I close my Excel file.}}
​

I run this code (provided by a member on Mr.Excel):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
    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
Application.ScreenUpdating = True
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

{{When I come back later to open my file and performing work in the same workbook.}}
​

Then after I am done I run this code (which should delete the textbox I just created):

Sub Analyst()
'
'
'

'
Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    ActiveSheet.Shapes.Range(Array("TextBox 5")).Select
    Selection.Delete
Application.ScreenUpdating = True
End Sub

My issue is the second Macro can't seem to find the Textbox everytime (it's not stable).

Any help would be appreciated.

 

[JLGWhiz]

The problem is that the TextBox you create, might not be "Textbox 5", it could be Textbox 6 or 7 or some other number, because Excel assigns the number based on how many of that type control is in the collection at the time the control is created, or were created during the same session. The way around it is to name the control when you create it and use that name in the code to delete it instead of the control indexed name.

 

[drmingle]

JLGWhiz,

Thank you for the quick response...

Given this is the code that creates the textbox how would I go about assigning a name to it:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
    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
Application.ScreenUpdating = True
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]

I ended up using this (which works):

selection.name="txtbox1"

 

[JLGWhiz]

Glad you worked it out
Regards, JLG