this is my current code for my button click event, I have a number of things
I want to happen when the button is clicked and one protion is giving me
trouble. The part that creates the MSDS#. I want the value of CboDept (a list
of up to 3 letters) to be converted to an alphanumeric number (i.e. xx001)
and store it into a string so I can call it at the end of the code to be
inserted into sheet "Lists" column 'M'. Everytime the button click event
fires I want the code to search column 'M' for the left 2 or 3 letters that
match the CboDept selection and then add onto it the next sequencial number
starting at 001 and so on. If it doesn't find a match then it will just add
it to the column.
#1) I cannot seem to get the 001 at the end of the 2 letters I just get a 1
and it won't add to the count.
#2) I am unable to get it to work in conjuction with the current code
#3) I do believe I am going about it all wrong, please help to steer me in
the right direction

Private Sub BtnAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim dept As String
Dim c As Range

Set ws = Worksheets("ProCode")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'creates the MSDS#
dept = Me.CboDept.Value & "0" + 1
For Each c In Worksheets("Lists").Range("M2:M1000")
If c.Value < dept Then
c.Value = dept
End If
If c.Value = dept Then
MsgBox "number matched"
Exit Sub
End If
Next c

'check for the product name
If Trim(Me.TxtProd.Value) = "" Then
Me.TxtProd.SetFocus
MsgBox "Please enter the product name"
Exit Sub
End If

'copy the data to the database
Application.EnableEvents = False
ws.Cells(iRow, 2).Value = Me.TxtProd.Value
ws.Cells(iRow, 3).Value = IIf(Me.CkBox1.Value, "Yes", "No")
ws.Cells(iRow, 4).Value = IIf(Me.CkBox2.Value, "Yes", "No")
ws.Cells(iRow, 5).Value = IIf(Me.CkBox3.Value, "Yes", "No")
ws.Cells(iRow, 6).Value = Me.CboFire.Value
ws.Cells(iRow, 7).Value = Me.CboHealth.Value
ws.Cells(iRow, 8).Value = Me.CboReact.Value
ws.Cells(iRow, 9).Value = Me.CboSpec.Value
ws.Cells(iRow, 10).Value = Me.CboDisp.Value
ws.Cells(iRow, 11).Value = Me.TxtQuan.Value
ws.Cells(iRow, 12).Value = Me.TxtDate.Value
ws.Cells(iRow, 13).Value = dept
Application.EnableEvents = True

'the sort will fire with this line.
ws.Cells(iRow, 1).Value = Me.CboMan.Value
FrmProduct.CboMan.Value = Me.CboMan.Value

'clear the data
Me.CboMan.Value = ""
Me.TxtProd.Value = ""
Me.CkBox1.Value = False
Me.CkBox2.Value = False
Me.CkBox3.Value = False
Me.CboFire.Value = ""
Me.CboHealth.Value = ""
Me.CboReact.Value = ""
Me.CboSpec.Value = ""
Me.CboDisp.Value = ""
Me.TxtQuan.Value = ""
Me.TxtDate.Value = ""
End Sub