Solved: save combobox selection to worksheet

[lhtqasonline]

Hi
I'm busy with a Userform that is used to update a supplier database worksheet.
I have a combobox that is populated from the worksheet.
The user would select supplier and the form will show values like tel number fax etc.
That portion works but my problem is i want to use another combobox on the form that is populated by an array.
Based on the selection of the combobox i want the selected value saved to a field on the worksheet.
I've seen examples how to show it on a msgbox but i need it to be saved to the worksheet when i click on my save changes button

Any suggestions

[Bob Phillips]

Something as simple as this perhaps?

[vba]

Worksheets("Sheet1").Range("A1").Value = CombobBox1.Value
[/vba]

[rbrhodes]

Example perhaps?

[lhtqasonline]

I use Match to populate combobox and then a if statement on OK button click to update the cells again

Here is sample of my code

I have the following under Option Explicit

Option Explicit
Private r As Long
Private Declare Function ShellExecute& Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hWnd As Long, _
ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long)

'and then

Private Sub SupName_Change()
r = Application.Match(Me.SupName, Range("B1:B2000"), 0)
Me.SuppCode = Cells(r, 3)
Me.SuppContact = Cells(r, 4)
Me.SuppTel1 = Cells(r, 5)
Me.SuppTel2 = Cells(r, 6)
'etc......

This populates my form with values from the worksheet and then

Private Sub btnOK_Click()
If r > 0 Then
Cells(r, 3) = Me.SuppCode
Cells(r, 4) = Me.SuppContact
Cells(r, 5) = Me.SuppTel1
Cells(r, 6) = Me.SuppTel2
'etc.....

That saves any changes on the form back to the worksheet

Now i added a Combobox with array i want saved back in a cell on the worksheet

Private Sub ComboBox1_Change()

With Me.ComboBox1
'!!!HERE is my problem i want it saved to Cells(r,20) = Me.ComboBox1 !!!

End With
End Sub

Private Sub UserForm_Initialize()
Dim aryBERating()
Dim i As Long
aryBERating = Array("<R5m (EME)", "R5m-R35m (QSE)", ">R35m Large")
With ComboBox1
For i = 0 To UBound(aryBERating)
.AddItem aryBERating(i)
Next i
End With
End Sub

My problem is i want the value selected in the combobox saved back to Cells(r,20) = Me.ComboBox1

Suggestion?

[Bob Phillips]

[vba]

Cells(r, 20).Value = .Value = Me. CombobBox1.Value
[/vba]

[lhtqasonline]

i just checked my form and it's not populating anymore, busy checking what went wrong

[lhtqasonline]

thanks xld your suggestion worked

Thank you very much

[lucas]

Mark your thread solved using the thread tools at the top of the page.