Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2006
    Location
    Alexandria, Virginia, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    TextBox Control (VBA/Excel 2003)

    I can't figure out how to drag and drop or paste information from a cell into the TextBox.text control on a Userform. If someone could help me with this problem I would greatly appreciate the help.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: TextBox Control (VBA/Excel 2003)

    Welcome to Woody's Lounge!

    I'm not sure this is possible without using lots of complicated Windows API code. If you show a modeless userform, then click in the worksheet, the userform doesn't react to MouseMove events if you try to drag a cell onto the form. Copy and paste is a much easier way to get data into a text box.

  4. #3
    New Lounger
    Join Date
    Dec 2006
    Location
    Alexandria, Virginia, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox Control (VBA/Excel 2003)

    So far, you have confirmed the conclusion I came to. However I noticed that the MS Excel select function feature has the capabilities I am looking for. When you click the select function icon, it results in the display of a dialog box containing a control simular to the RefEdit control. However, this control has an embedded button command that allows you to select cells anywhere. I will try 2 things today. 1) I will run the create macro while using the SUM function to see if I can pick up what code is generated. 2) I will create a control with an embedded command button.

    Thanks for your response

  5. #4
    New Lounger
    Join Date
    Dec 2006
    Location
    Alexandria, Virginia, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox Control (VBA/Excel 2003)

    Well I tried recording a macro using the save function and this is the results:

    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
    ActiveCell.FormulaR1C1 = "=SUM(R[3]C[-1]:R[10]C[-1])"
    End Sub

    What this tells me is that maybe I can replace the ActiveCell.FormulaR1C1 with TextBox.Text = some sort of function. The function would turn the userform into modeless which will then allow me to select cell(s) and return the the value to the TextBox.Text. Another click would reset the userform to modal. I tried to activate the dblclk, beforedragover, and beforedragordrop events without success. So, at this time it seems the only way to activate this function is to set up a commandbutton.


    Howard B

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: TextBox Control (VBA/Excel 2003)

    I'm afraid that won't work. You cannot change the modal/modeless state of a form while it is open.

    You could use a RefEdit control to let the user select a cell, and a command button to place the value of that cell in the text box:

    Private Sub CommandButton1_Click()
    On Error Resume Next
    Me.TextBox1 = Range(Me.RefEdit1).Value
    End Sub

    But you'd have to add a lot of error checking if you wanted to avoid using the catch-all On Error Resume Next.

  7. #6
    New Lounger
    Join Date
    Dec 2006
    Location
    Alexandria, Virginia, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox Control (VBA/Excel 2003)

    Thanks HansV for your response. I think I may get a chance to try your suggestion today. This is what I am working with, a UserForm containing a TextBox and CommandButton. I was trying to allow the user to select text instead of entering it in the TextBox and then saving it by hitting the CommandButton. Looks like I will have to use the RefEdit , TextBox and a CommaandButton to achiave this goal.


    Howard B

  8. #7
    New Lounger
    Join Date
    Dec 2006
    Location
    Alexandria, Virginia, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox Control (VBA/Excel 2003)

    HansV,
    Thanks a lot. I wonder why MS didn't implement coding for the BeforeDragOver or BeforeDroporPaste events? Anyway, I will try your suggestion today.

    Howard B

Posting Permissions

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