Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Sending data from a UserForm ComboBox to spreadsheet cell

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm new to Excel VBA macros, and I'm kinda stuck on where to proceed next. I'm attempting to present the end user with small UserForm (so far, this works) that gives them the opportunity to make a choice from a ComboBox. After the choice is made, and the Command button on the UserForm is clicked, I'd like to have the selected data be sent to the active cell (actually, a cell two cells to the right, relative). Making the choice go to the cell is the part not yet working.

    What I've got so far:

    UserForm:
    =========
    Private Sub CommandButton1_Click()

    End Sub

    Private Sub UserForm_Initialize()
    Dim MyArray As Variant
    Dim i As Integer

    'Initialize array with values to populate ComboBox.
    MyArray = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10")

    For i = LBound(MyArray) To UBound(MyArray)

    'Add a value from MyArray to ComboBox1.
    UserForm1.ComboBox1.AddItem MyArray(i)

    Next
    End Sub

    Private Sub UserForm_Click()

    End Sub

    ========================================

    (this UserForm is called from the following Macro code - the macro selects the highlighted cell on one sheet, switches to another sheet, and pastes the contents into the current active cell on that sheet. Then, the UserForm is presented - I'd like to be able to complete the task by having the UserForm's selection from the ComboBox then be pasted into the cell two positions to the right of the active cell):

    Sub Pick()

    ActiveCell.Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveCell.Offset(0, 0).Range("A1").Select
    ActiveSheet.Paste
    UserForm1.Show
    End Sub

    ============================================

    Any help in getting this step figured out would be appreciated. Thank you all!

    -Bob

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bob

    Hope you don't mind me saying, but you are over-complicating things.

    Try:

    Private Sub CommandButton1_Click()
    Sheet2.Range("A1") = ComboBox1
    ComboBox1 = vbNullString
    End Sub

    Private Sub UserForm_Initialize()
    Dim i As Integer

    For i = 1 To 10
    Me.ComboBox1.AddItem i
    Next i

    End Sub

  3. #3
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    This code would send the combobox selected value into the A1 cell in Sheet1.

    Private Sub CommandButton1_Click()
    Sheet1.Cells(1,1)=Combobox1.Text
    End Sub

    is that ok?


    Suat

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could probably just use:

    Sub Pick()
    ActiveCell.Copy Destination:= _
    Sheets("Sheet2").Range("A1")
    UserForm1.Show
    End Sub

    To launch your UserForm. Try to avoid relying on Active cells and Selections and work with them directly. Rarely a need to Select or Activate.



  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Little Italy
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why not use ComboBox.ControlSource property directly?

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave, smozgur,

    Thanks for the help - I believe I'm on the right track. However, I'm now getting an error when clicking on the CommandButton to send the choice to the sheet:

    Run time error '424'

    Object required.

    This error points to the statement:

    Sheet2.Cells(1,1) = ComboBox1.Text

    or

    Sheet2.Range("A1") = ComboBox1

    Any ideas what I'm missing? Thanks!

    -Bob

  7. #7
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think :

    Sheet2 object name is not "Sheet2" in your project

    or

    ComboBox1 object name is not "ComboBox1" in your UserForm

    Who knows? Error just says that. May be you need full reference for those objects:

    Workbookname.Sheet2....=UserFormName.Combobox1....

    Here : Sheet2 is not the name you see in label, it is the object name which you see in project window. If you want to use sheet name then you should use:

    Sheets("Sheet2").....

    May be helps.

    Suat

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Smozgur,

    Thanks - I believe that corrected the problem. The only thing not currently working is the cell to which the combobox data is being sent. The premise here is to already have an active cell (the choice can change, but the choice is made BEFORE the UserForm is called), and then send the combobox selection to a cell offset two cells to the right. This is what I'm currently using (remember, beginner here, no snickerring ):

    Private Sub CommandButton1_Click()
    Worksheets("MyTeam").Activate
    ActiveCell.Offset(0, 2).Range("A1").Select
    Sheets("MyTeam").Range("A1") = UserForm1.ComboBox1.Value
    UserForm1.ComboBox1.Value = vbNullString
    UserForm_Click

    End Sub

    Private Sub UserForm_Click()
    Unload Me

    End Sub

    ============================================

    The value froom the combobox is ending up in cell R1, C1. Where's my mistake?

    Thanks!

    -Bob

  9. #9
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ):

    Private Sub CommandButton1_Click()
    Worksheets("MyTeam").Activate
    ActiveCell.Offset(0, 2).Range("A1").Select
    activecell.value= UserForm1.ComboBox1.Value
    UserForm1.ComboBox1.Value = vbNullString
    UserForm_Click

    End Sub

    Private Sub UserForm_Click()
    Unload Me

    End Sub


  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al Chara,

    Thank you - that did the trick!

    And, thank all of you for your help! I've learned alot!

    -Bob

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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