I was able to get the "ORD#" to appear in the "txtOrderNo" textbox but my problem is I can't get it to start at "ORD001" and to start at the first empty cell on the worksheet "Customer_Order_History". It appears on the worksheet but not numbered correctly and not starting at the correct empty cell. Also need to know do I have the code in the correct module so I need to know if that is my problem also.
Private Sub UserForm_Initialize()
Dim ws As Worksheet, CT As Shape
Dim sh As Worksheet
Dim iRow As Long
Dim myRange As Range, f As Range ' ### aded these
Set sh = ThisWorkbook.Sheets("Customer_Order_History")
'populate Customer No.
frmGunParts.txtCustupdate.Text = frmForm.cboCustNo.Text
iRow = [Counta(Customer_Order_History!B3:B)] + 1 ' moved this
' ' ### added this
Set myRange = Worksheets("Customer_Order_History").Range("B3:B500")
' ' changed this to add form number
Set f = myRange.Find(What:=frmGunParts.cmbGunSel.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) '<--| try and find combobox selected value
If Not f Is Nothing Then
iRow = f.Row
End If
' loop through sheets and set data sources
With sh
' replaced this
.Cells(iRow, 3) = "ORD" & Format(iRow, "000") 'New Dynamic Serial Number
End With
'Show on userform
Me.txtOrderNo.Text = sh.Cells(iRow, 3) 'New Dynamic Serial Number
For Each ws In ThisWorkbook.Worksheets
' check sheet name starts with root
If InStr(ws.Name, Root) = 1 Then
' add name (less Root) to ComboBox list
Me.cmbGunSel.AddItem Split(ws.Name, Root)(1)
End If
Next ws
End Sub
I attached a file so please let me know if you get the file. I have been running into issues to add files to my questions. I figured it out. Thank you.