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

nmk34

New Member
Joined
Apr 12, 2022
Messages
45
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,011
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top