Hi. I am trying to use a sub routine called Reset which clears all controls and then sets the data range for each combo box ready for a new submission.
The Intention is to clear all text in each control including the combo boxes, but if a combo box, reassign the range that the combo box needs to display as options for next input. I can use. Clear if my options are coded as add.Item but not when I select the range the combo box ctrl needs to source the data from. I have multiple combo boxes in my userform. The .txt*Value all seem to reset OK
Here is the VBA code:
Sub Reset()
Dim iRow As Long
iRow = [countA(Database!A:A)]
With frmForm
.txtReqID.Value = "" 'clears the .txtReqID
.cmbBrand.Clear 'Clears data from the Brand Combo Box (but doesn't work)
shSettings.Range("D2", shSettings.Range("D" & Application.Rows.Count).End(xlUp)).Name = "Brand" ' assigns dynamic range for the combo box "Brand"
.cmbBrand.RowSource = "Brand"
.cmbBrand.Value = "" 'set the value t show in combo box after reset
.cmbRP.Clear
shSettings.Range("A2", shSettings.Range("A" & Application.Rows.Count).End(xlUp)).Name = "RP"
.cmbRP.RowSource = "RP"
.cmbRP.Value = ""
.cmbBF.Clear
shSettings.Range("K2", shSettings.Range("K" & Application.Rows.Count).End(xlUp)).Name = "BF"
.cmbBF.RowSource = "BF"
.cmbBF.Value = ""
.cmbLocation.Clear
shSettings.Range("I2", shSettings.Range("I" & Application.Rows.Count).End(xlUp)).Name = "Loc"
.cmbLocation.RowSource = "Loc"
.cmbLocation.Value = ""
.txtRole.Value = ""
.txtSalary.Value = ""
I have seen that you could use NUll but not show how to write that? Any help greatly appreciated.
Bookmarks