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

 

[Edwin Balansuah]

Please, how do I disable the file menu in excel?