websmythe
04-23-2008, 12:42 PM
I have a userform with a listbox, comboboxes, text boxes, and commandbutton to update the current record in the worksheet; eg: Suppliers, Materials, Units, Qty & Price. The Suppliers and Units comboboxes are filled once when the form is initialized. On Listbox_click() the materials combobox is updated with respective options for the current record from another worksheet. The Price textbox is updated on the Materials combobox_change() event. Qty is user specified.
Does anyone know why the following wont save the current values of the comboboxes to the worksheet?
<Reposted Code>
Private Sub btnSave_Click()
Dim thisIdx As Long
Dim celSuppliers as String
Dim celProducts as String
Dim celUnits as String
Dim celQty as String
Dim celPrice as String
celSuppliers = "E"
celProducts = "F"
celUnits = "G"
celQty = "H"
celPrice = "I"
Application.ScreenUpdating = False
thisIdx = ListBox1.ListIndex + 2
With Worksheets(shtCabinetKey) 'shtCabinetKey = "CabinetKey" is Public
.Range(celSuppliers & CStr(thisIdx)).Select
.Range(celSuppliers & CStr(thisIdx)).Value = cboSuppliers.Value
.Range(celProducts & CStr(thisIdx)).Select
.Range(celProducts & CStr(thisIdx)).Value = cboMaterials.Value
.Range(celUnits & CStr(thisIdx)).Select
.Range(celUnits & CStr(thisIdx)).Value = cboUnits.Value
.Range(celQty & CStr(thisIdx)).Select
.Range(celQty & CStr(thisIdx)).Value = txtQty.Value
.Range(celPrice & CStr(thisIdx)).Select
.Range(celPrice & CStr(thisIdx)).Value = txtPrice.Value
End With
Application.ScreenUpdating = True
end sub
Does anyone know why the following wont save the current values of the comboboxes to the worksheet?
<Reposted Code>
Private Sub btnSave_Click()
Dim thisIdx As Long
Dim celSuppliers as String
Dim celProducts as String
Dim celUnits as String
Dim celQty as String
Dim celPrice as String
celSuppliers = "E"
celProducts = "F"
celUnits = "G"
celQty = "H"
celPrice = "I"
Application.ScreenUpdating = False
thisIdx = ListBox1.ListIndex + 2
With Worksheets(shtCabinetKey) 'shtCabinetKey = "CabinetKey" is Public
.Range(celSuppliers & CStr(thisIdx)).Select
.Range(celSuppliers & CStr(thisIdx)).Value = cboSuppliers.Value
.Range(celProducts & CStr(thisIdx)).Select
.Range(celProducts & CStr(thisIdx)).Value = cboMaterials.Value
.Range(celUnits & CStr(thisIdx)).Select
.Range(celUnits & CStr(thisIdx)).Value = cboUnits.Value
.Range(celQty & CStr(thisIdx)).Select
.Range(celQty & CStr(thisIdx)).Value = txtQty.Value
.Range(celPrice & CStr(thisIdx)).Select
.Range(celPrice & CStr(thisIdx)).Value = txtPrice.Value
End With
Application.ScreenUpdating = True
end sub