updating combobox data range with new row of several columns of data

[nmk34]

I am trying to add new product specs that fills row of coloumns in excel sheet. Summary of what I am trying to do:

• Select next empty row in a ComboBoItemNumber which a combobox.
• fill in the textboxes on a userform with the new specs.
• click the button cmdNewProduct.

The textboxes values should move to the excel sheet in the “combobox range of data”.

The problem I am having is after the first text box gets transferred to the combobox range, the execution of the code move to the combobox change procedure which makes the next columns blank.

Please help me fix this logic?

Private Sub cmdNewProduct_Click()

Sheets("Sheet1").Select

'Finds the last non-blank cell on a sheet/range.

Dim lRow As Long

Dim lCol As Long

'Find the last non-blank cell in column A( actually find next blank after last non blank row)

lRow = Cells(Rows.Count, 27).End(xlUp).Row + 1

'Find the last non-blank cell in row 1

'Last_Col = Cells(1, Columns.Count).End(xlToLeft).Column

Worksheets("sheet1").Range("AA" & lRow) = txtProductName.Text ' code jumps from here to the combobox change procedure, and becuse there was no data in the excel sheet at this row, next data will be over written

Worksheets("sheet1").Range("AB" & lRow) = txtProductMin.Text

Worksheets("sheet1").Range("AC" & lRow) = txtProductMax.Text

End Sub

‘…………………………………………..

Private Sub ComboBoItemNumber_Change()

itemindex = ComboBoItemNumber.ListIndex

'itemindex = itemindex + 1

Select Case itemindex

Case 0

txtProductName.Text = Worksheets("sheet1").Range("AA2")



txtProductMin.Text = Worksheets("sheet1").Range("AB2")



txtProductMax.Text = Worksheets("sheet1").Range("AC2")

End Select

 

[severynm]

The problem I am having is after the first text box gets transferred to the combobox range, the execution of the code move to the combobox change procedure which makes the next columns blank.

Try wrapping Application.EnableEvents = False/True around the code that would trigger the event.