+ Reply to Thread
Results 1 to 4 of 4

can you detect the selection on a textbox in excel vba

Hybrid View

incidentals can you detect the selection... 07-07-2019, 08:22 AM
Vraag en antwoord Re: can you detect the... 07-07-2019, 08:31 AM
incidentals Re: can you detect the... 07-07-2019, 09:27 AM
Norie Re: can you detect the... 07-07-2019, 09:50 AM
  1. #1
    Registered User
    Join Date
    07-06-2019
    Location
    isle of arran, scotland
    MS-Off Ver
    excel since MSO98 to date
    Posts
    33

    can you detect the selection on a textbox in excel vba

    I am trying to identify a textbox that has been clicked

    using shapes.addtextbox i added a number of text boxes

    I now wonder if when selected i can recognise that it is a textbox and select the associated cell instead

  2. #2
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: can you detect the selection on a textbox in excel vba

    Place an example of your file

  3. #3
    Registered User
    Join Date
    07-06-2019
    Location
    isle of arran, scotland
    MS-Off Ver
    excel since MSO98 to date
    Posts
    33

    Re: can you detect the selection on a textbox in excel vba

    this is the code that generates the textboxes

    Sub BORROW()
    'escape clause
    If IsEmpty(ActiveCell) Then Exit Sub
    
    'check fo previous borrowing
    
    
    
    'continue to borrow
    Set mydocument = ActiveSheet
    
    FontSize = ActiveCell.Font.Size
    ReducedFont = FontSize * 0.75
    
    crossout = vbRed
    MarkUp = vbBlack
    
    cTop = ActiveCell.Top
    cLeft = ActiveCell.Left
    
    cwidth = ActiveCell.Width
    cheight = ActiveCell.Height
    
    'Debug.Print "cell width = " & cwidth & " cell height = " & cheight
    
    cNudgeLeft = ActiveCell.Left + cwidth / 10
    cnudgeUp = ActiveCell.Top - cheight / 20
    
    cBackOff = cwidth / 6
    
    cShoveLeft = cLeft + cwidth * 0.4
    cShoveUp = cTop - cheight * 0.4
    
    cNextLeft = cLeft + cwidth - cBackOff
    
    
    'cross out
    With mydocument.Shapes.AddTextbox(msoTextOrientationHorizontal, _
    cNudgeLeft, cnudgeUp, cwidth, cheight)
    .Name = ActiveCell.Address & "/"
    .TextFrame.Characters.Text = "/"
    .TextFrame.Characters.Font.Size = FontSize
    .TextFrame.Characters.Font.Color = crossout
    .Fill.Visible = False
    .Line.Visible = False
    '.Line.Visible = msoFalse
    '.Fill.Transparency = 1
    End With
    
    'reduce value
    With mydocument.Shapes.AddTextbox(msoTextOrientationHorizontal, _
    cShoveLeft, cShoveUp, cwidth, cheight)
    .Name = ActiveCell.Address & "r"
    .TextFrame.Characters.Text = ActiveCell.Value - 1
    .TextFrame.Characters.Font.Size = ReducedFont
    .TextFrame.Characters.Font.Color = MarkUp
    .Fill.Visible = False
    .Line.Visible = False
    '.Fill.Transparency = 1
    End With
    
    'place 1
    With mydocument.Shapes.AddTextbox(msoTextOrientationHorizontal, _
    cNextLeft, cShoveUp, cwidth, cheight)
    .Name = ActiveCell.Address & "b"
    .TextFrame.Characters.Text = "1"
    .TextFrame.Characters.Font.Size = ReducedFont
    .TextFrame.Characters.Font.Color = MarkUp
    .Fill.Visible = False
    .Line.Visible = False
    '.Fill.Transparency = 1
    End With
    
    
    End Sub
    when the user selects the textbox on the sheet I want to change the selection to the cell identified in the textbox.name

    currently I do not know how to identify the selecton of a textbox only that it gains handles when selected on the sheet so some event must be happening.

    see image
    textbox selected.png
    Last edited by incidentals; 07-07-2019 at 09:30 AM.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: can you detect the selection on a textbox in excel vba

    If these are textboxes created from the Forms toolbar and they have been assigned a macro you can identify them using Application.Caller.

    That will give you the name of the control that has called the macro which you can then use to get a reference to the control and all it's properties.
    If posting code please use code tags, see here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. detect if selection is range or shape
    By glda19 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-31-2018, 02:20 AM
  2. Detect which textbox has the cursor in
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-14-2015, 03:57 PM
  3. Textbox to detect if there is a duplicate
    By siobeh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 06:42 AM
  4. Excel 2007 : Auto detect cell selection
    By Obfuscated in forum Excel General
    Replies: 2
    Last Post: 09-06-2010, 03:00 AM
  5. Detect when autofilter drop-down selection made
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2009, 04:05 PM
  6. [SOLVED] How to detect last row of Selection?
    By joeu2004@hotmail.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2006, 01:00 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1