Sending data from a UserForm ComboBox to spreadsheet cell

rferwerda

New Member
Joined
Mar 19, 2002
Messages
4
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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 :wink: ):

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
 
Upvote 0
):

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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top