Announcement

Collapse
No announcement yet.

Delete TextBox

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Delete TextBox



    Hello all

    How to select the TextBox that is placed on the activecell without referencing
    to the TextBox number i.e ( TextBox1 )

    I reached this code, but it is with the textbox number………sometimes I want to delete the TextBox that is placed on the activecell, so the user doesn't know the number of the textbox that is placed on the activecell………

    ActiveSheet.Shapes("TextBox1").Select
    Selection.Delete
    ******************
    Yours
    h

    Your Help Is Highly Appreciated

  • #2
    The following function will return the name of the first textbox it finds that overlaps the range passed to it. You could use it to return the name of the textbox placed on the active cell by calling WhatTextBox(ActiveCell) in your sub. A null string is returned if there isn't one.
    Function WhatTextBox(r As Range) As String
    Dim t As TextBox, cover As Range
    For Each t In ActiveSheet.TextBoxes
    Set cover = Range(t.TopLeftCell, t.BottomRightCell)
    If Not Intersect(r, cover) Is Nothing Then
    WhatTextBox = t.Name
    Exit Function
    End If
    Next t
    WhatTextBox = ""
    End Function
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

    Comment


    • #3
      Hi Derk

      Thanks 4 your reply

      could u please tell me how to use the code u gave me

      thank u
      ******************
      Yours
      h

      Your Help Is Highly Appreciated

      Comment


      • #4
        In your original post you had the lines
        ActiveSheet.Shapes("TextBox1").Select
        Selection.Delete
        I don't know how you intended using them, but if you have my function in a general module of the workbook you could then use
        Dim s As String
        s = WhatTextBox(ActiveCell)
        If s <> "" Then ActiveSheet.Shapes(s).Delete
        instead of your original two lines to delete a textbox over the activecell when there is one. Or change ActiveCell to any range reference you want.
        Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

        Comment


        • #5
          (In a private message it was explained that the code did not work and I was sent an example file)

          The problem was the code assumed the textboxes were from the Forms toolbar when instead they were from the Controls toolbar. The code needs revision as
          Function WhatShape(r As Range) As String
          Dim t As Shape, cover As Range
          For Each t In ActiveSheet.Shapes
          Set cover = Range(t.TopLeftCell, t.BottomRightCell)
          If Not Intersect(r, cover) Is Nothing Then
          WhatShape = t.Name
          Exit Function
          End If
          Next t
          WhatShape = ""
          End Function

          Since it returns the name of the first shape found overlapping with the activecell, you will need to be careful in positioning the shapes. In the example workbook, the shape from above the activecell was also touching the activecell, so it was deleted instead of the intended cell.
          Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

          Comment


          • #6
            Re: Delete TextBox

            Originally posted by Derk
            Since it returns the name of the first shape found overlapping with the activecell, you will need to be careful in positioning the shapes. In the example workbook, the shape from above the activecell was also touching the activecell, so it was deleted instead of the intended cell.
            Seems like an easy fix: just use the "TypeName" function:

            Code:
            Function WhatShape(r As Range) As String
                Dim t As Shape, cover As Range
                For Each t In ActiveSheet.Shapes
                '   Check that shape is a textbox
                    If (TypeName(t) = "TextBox") Then
                        Set cover = Range(t.TopLeftCell, t.BottomRightCell)
                        If Not Intersect(r, cover) Is Nothing Then
                            WhatShape = t.Name
                            Exit Function
                        End If
                    End If
                Next t
                WhatShape = ""
            End Function
            This should work right?
            ~LSwanson

            Comment


            • #7
              Re: Delete TextBox

              It should now return the first textbox it finds overlapping the designated range. There still could be more than one textbox with overlap though, as I recall was the case in the example file. That file had textboxes intended to be one per cell, but there was some overlap.
              Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

              Comment


              • #8


                Re: Delete TextBox

                Originally posted by Derk
                There still could be more than one textbox with overlap though, as I recall was the case in the example file. That file had textboxes intended to be one per cell, but there was some overlap.
                Ah, good point Derk.

                Helmekki, how about if you just make sure to name the textboxes appropriately? Something like "txtA1" for the textbox in cell A1. Then you could use the following function to determine which textbox corresponds to the active cell:

                Code:
                Function WhichText(rngTarget As Range) As String
                    Dim sCurrShape As Shape
                    Dim szType As String, szAddress As String
                    
                '   Initialize the return value to a blank string
                    WhichText = ""
                    
                    For Each sCurrShape In ActiveSheet.Shapes
                    '   Get the type of shape
                        szType = Left(sCurrShape.Name, 3)
                    
                    '   Get the address referenced by the shape
                        szAddress = Right(sCurrShape.Name, Len(sCurrShape.Name) - 3)
                        
                    '   If the shape is a textbox referencing the target address
                        If (szType = "txt" And szAddress = rngTarget.Address) Then
                            WhichText = sCurrShape.Name
                        End If
                    Next sCurrShape
                End Function
                Use it like so:
                Code:
                Dim szTextToDelete As String 
                szTextToDelete = WhichText(ActiveCell) 
                If szTextToDelete <> "" Then ActiveSheet.Shapes(szTextToDelete).Delete
                Think this will work for you?
                ~LSwanson

                Comment

                Working...
                X