Sending data from a UserForm ComboBox to spreadsheet cell

[rferwerda]

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

[Dave Hawley]

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

[smozgur]

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?

[Dave Hawley]

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.

[Sbirro Cornuto]

Why not use ComboBox.ControlSource property directly?

[rferwerda]

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

[smozgur]

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.

[rferwerda]

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

[Al Chara]

):

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

[rferwerda]

Al Chara,

Thank you - that did the trick!

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

-Bob