I am trying to add new product specs that fills row of coloumns in excel sheet. Summary of what I am trying to do:
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
- 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 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