Steve,
I would like to thank you for all your help, I still have more issues I am
dealing with however I try to limit them to one a day. I will post it
tomorrow or Monday.

Thanks again

"Steve Yandl" wrote:

I think you just left out two lines from my code. Copy and paste to update
your section creating the MSDS# from below

__________________________________

'creates the MSDS#
For R = 1 To intMtoprow
strCell = ws.Cells(R, 13).Value
If InStr(strCell, dept) = 1 And _
IsNumeric(Mid(strCell, Len(dept) + 1)) Then
X = CInt(Mid(strCell, Len(dept) + 1))
If X Y Then
Y = X
End If
End If
Next R

_________________________________

Steve



"Mekinnik" wrote in message
...
I tried that also without any luck, so I thought if you saw the add button
code maybe it would help. I would gladly accept any changes you think
would
be needed to make this work or if I have been unclear on explaining what
it
is I am trying to do, please let me know and I will try to clear it up the
best I can. Thank you very much for all you help in correcting this
problem.

Private Sub BtnAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim intMtoprow As Integer
Dim dept As String
Dim x As Integer
Dim y As Integer

dept = Me.CboDept.Text
y = 0
Set ws = Worksheets("ProCode")
'find first empty row in database
intMtoprow = ws.Range("M1000").End(xlUp).Row
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

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

'creates the MSDS#
For r = 2 To intMtoprow
strCell = ws.Cells(r, 13).Value
If InStr(strCell, dept) = 1 And _
IsNumeric(Mid(strCell, Len(dept) + 1)) Then
x = CInt(Mid(strCell, Len(dept) + 1))
y = x
End If
Next r

'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 & Format(y + 1, "00#")

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

"Steve Yandl" wrote:

Change
For R = 1 To intMMtop
to
For R = 2 To intMMtop

Steve


"Mekinnik" wrote in message
...
I did make thoughs changes, however it is putting the number in the
first
row
of column 'M' only which happens to be my heading row and if I try to
add
another it will not update it at all?

"Steve Yandl" wrote:

In the two locations where I used Worksheets("Lists"), edit to
Worksheets("Procode") and it should work fine. Reading your initial
question, I made the incorrect assumption that the column M where the
MSDS#s
were place was on the "Lists" sheet.

Steve



"Mekinnik" wrote in message
...
Well, I tried your code and it works only partly. When you add the
first
entry it will assign the number XX001, however when you go to inset
the
second one it will not assign a number at all. The way I am trying
to
make
it
work is the number as I call it will be a 2 letter alphanumeric text
with
a 3
digit number counter at the end starting with number 000 and
counting
up.
I
am trying to get it to compare what is selected in the combobox
'CboDept'
with the first 2 letters of the data in column 'M' of the sheet
named
'ProCode' and either add 1 number to what matches 'CboDept' or
create a
new
number starting back at 000 with the new 2 letters selected and then
apply
it
to the added data when the add button is clicked.

"Steve Yandl" wrote:

On giving it some more thought, there is an error condition I
didn't
consider when writing the original sub. Suppose you already have
values
xyz001, xyz002, xyz003 and then instead of entering xyz, you simpy
entered
xy. Ideally, this would generate xy001 but since there are already
matches
for xy, the sub will try to treat z003 as a number and generate an
error.

The corrected version below should handle that. I also changed
things
so
the final result is assigning an appropriate alpha numeric value to
your
variable dept rather than insert the value at the bottom of column
M.
I
think the code below should integrate easily with your existing
code.

_______________________________________

Dim intMMtop As Integer
Dim dept As String
Dim Y As Integer
Dim X As Integer

intMMtop = Worksheets("Lists").Range("M65536").End(xlUp).Row
dept = Me.CboDept.Text
Y = 0

For R = 1 To intMMtop
strCell = Worksheets("Lists").Cells(R, 13).Value
If InStr(strCell, dept) = 1 And _
IsNumeric(Mid(strCell, Len(dept) + 1)) Then
X = CInt(Mid(strCell, Len(dept) + 1))
If X Y Then
Y = X
End If
End If
Next R

dept = dept & Format(Y + 1, "00#")
_______________________________________

Steve