Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Delete TextBox

  1. #1
    Join Date
    7th May 2004
    Location
    Libya
    Posts
    619

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,380
    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.
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th May 2004
    Location
    Libya
    Posts
    619

    Hi Derk

    Thanks 4 your reply

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

    thank u

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,380
    In your original post you had the lines
    VB:
    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
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,380
    (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
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    22nd April 2006
    Posts
    243

    Re: Delete TextBox

    Quote 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:

    VB:
    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?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,380

    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.

  8. #8
    Join Date
    22nd April 2006
    Posts
    243

    Re: Delete TextBox

    Quote 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:

    VB:
    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:
    VB:
    Dim szTextToDelete As String 
    szTextToDelete = WhichText(ActiveCell) 
    If szTextToDelete <> "" Then ActiveSheet.Shapes(szTextToDelete).Delete 
    
    
    Think this will work for you?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: May 7th, 2008, 13:43
  2. Pass Vlookup Result Of TextBox To Another TextBox
    By TheNewbie in forum EXCEL HELP
    Replies: 7
    Last Post: February 15th, 2008, 06:06
  3. Copy Textbox Text When Cursor Moved From Textbox
    By senarumugam2003 in forum EXCEL HELP
    Replies: 5
    Last Post: February 7th, 2007, 20:14
  4. Link text to textbox and format textbox
    By NoviceUser in forum EXCEL HELP
    Replies: 1
    Last Post: July 22nd, 2005, 09:08
  5. Delete based on textbox input
    By Olly_w in forum EXCEL HELP
    Replies: 8
    Last Post: December 17th, 2004, 23:26

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno