200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Supposed this code is looking for the last column in the worksheet data of the table (code #) but I want this txtCode is look for the last number in listbox after filtering with the second combox calling (CbSubCategory)
Private Sub UserForm_Activate()
Me.txtCode.Value = Application.WorksheetFunction.Max(Sheets("Data").Range("AN7:AN20000")) + 1
Application.Calculation = xlCalculationAutomatic
Private Sub UserForm_Activate()
'Me.txtCode.Value = Application.WorksheetFunction.Max(Sheets("Data").Range("AN7:AN20000")) + 1
Application.Calculation = xlCalculationAutomatic
End SubAmin
It seems your question was referring to the Userform "frmCodeItem".(please say so next time!).
In the attached revoised file, I've chnaged your UserForm code as as follows:
To get the item number (for the last item in the LB_)1 list, if any) showing, I modified this code, as shown in bold:
Private Sub CbSubCategory_Change()
<< existing code>>
End If
' show the item number for the last item in list (if any)
If s > 0 Then Me.txtCode.Value = Me.LB_01.List(s - 1, 0)
End SubAlso, when the Depatment is changed, the current code left theUserform showing selections from the old subcategory (so items NOT in the new Department). I changed this code as follows (in bold):
Private Sub comDepartment_Change()
<< existing code>>
End If
' clear the SubCategory and other boxes to allow new selection
Me.CbSubCategory = ""
Me.txtItemName = ""
Me.txtPrice = ""
Me.TxtSalePrice = ""
Me.comUnit = ""
Me.LB_01.RowSource = ""
End SubHope this fixes your problem. If so, please be sure to mark this Answer as Selected.
If s > 0 Then Me.txtCode.Value = Me.LB_01.List(s - 1, 0) +1