PDA

View Full Version : Using Comboboxes to Update a Worksheet??



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

Simon Lloyd
04-23-2008, 12:47 PM
websmythe, we have no way of knowing what celPrice etc. is, ideally post the whole code you are using as it is only your assumption that its that portion of code that isn't giving you the results.

websmythe
04-23-2008, 01:00 PM
Sorry. As it was the Range() function I thougtht it would be assumed what it was. Maybe it is another event somewhere causing the problem. Thot I'd covered for that.

<UPDATE>
Ya, just trapped for any other events, and sure enough it seems like a hidden click event or something was repopulating the list prior to writing the value to the record.

Thanx.