Why doesn't this work
 

Hi All,

Below is the code l am using in a ComboBox. The form populates fine,
the 1st column being the combobox from which the user selects the
data. Text boxes are used to display the associated data in columns 2,
3 & 4. My requirement is then that the data in columns 2,3, 7 4 can be
edited and written back to the worksheet. With the code below only the
edited data in column 2 is written back!

Any help gratefully appreciated. This is driving me nuts.

Private Sub cbStaff_Change()
tbCat.Value = cbStaff.Column(1)
tbSkill.Value = cbStaff.Column(2)
tbNum.Value = cbStaff.Column(3)

End Sub

Private Sub cmdCancel_Click()
Unload frmStaffEdit
End Sub

Private Sub cmdOK_Click()
Dim r As Integer
r = cbStaff.ListIndex + 3
With Sheets("Staff List")
Cells(r, 3).Value = tbCat.Value
Cells(r, 4).Value = tbSkill.Value
Cells(r, 5).Value = tbNum.Value
End With
Unload frmStaffEdit
End Sub

Private Sub UserForm_Initialize()
cbStaff.Value = ""
End Sub



TIA

Regards

MB

Why doesn't this work
 

I suspect you have those cells linked to to your combobox and you are getting
some interaction with that link. Suggest you break the link (rowsource)
and see how it works (load your combobox list with code).

--
Regards,
Tom Ogilvy


"michael.beckinsale" wrote:

Hi All,

Below is the code l am using in a ComboBox. The form populates fine,
the 1st column being the combobox from which the user selects the
data. Text boxes are used to display the associated data in columns 2,
3 & 4. My requirement is then that the data in columns 2,3, 7 4 can be
edited and written back to the worksheet. With the code below only the
edited data in column 2 is written back!

Any help gratefully appreciated. This is driving me nuts.

Private Sub cbStaff_Change()
tbCat.Value = cbStaff.Column(1)
tbSkill.Value = cbStaff.Column(2)
tbNum.Value = cbStaff.Column(3)

End Sub

Private Sub cmdCancel_Click()
Unload frmStaffEdit
End Sub

Private Sub cmdOK_Click()
Dim r As Integer
r = cbStaff.ListIndex + 3
With Sheets("Staff List")
Cells(r, 3).Value = tbCat.Value
Cells(r, 4).Value = tbSkill.Value
Cells(r, 5).Value = tbNum.Value
End With
Unload frmStaffEdit
End Sub

Private Sub UserForm_Initialize()
cbStaff.Value = ""
End Sub



TIA

Regards

MB