Hi, Guys!
Once more, asking for help!
I'd like that VBA generated a sequencial number based on inputs of users. For instance, I have the following sequencial catalog format:
Item-number-type-year
These are the following variables for item input:
Screw, flange, winch, crane, wheel, motor
And these for type input:
lay, top, bab, cat, lok, kip
For example this is the first item that I want to catalog, so it would be:
Screw-001-lay-2016
So if I enter screw(item) for lay (type) again VBA would create screw-002-lay-2016. If I created the same item for top, it would be screw-001-top-2016.
Note that all these variable are input by the user on a dialog box, except the Author name that should be copied for column B.
Could you guys help me achieving this?
Find the worksheet print and VBA.
Excel sheet:
http://postimg.org/image/lc1mlf3eh/
Option Explicit
Private Sub Label3_Click()
End Sub
Private Sub Label5_Click()
End Sub
Private Sub UserForm_Initialize()
Dim emptyRow As Long
'Make Sheet1 active
Sheet2.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
idNum.Value = Format(emptyRow, "0000")
fname.SetFocus
End Sub
Private Sub CommandButton1_Click()
Dim lngMyRow As Long
lngMyRow = Val(Trim(Mid(idNum.Value, 3, Len(idNum) - 2)))
'Transfer information
Cells(lngMyRow, 1).Value = idNum.Value
Cells(lngMyRow, 2).Value = fname.Value
Cells(lngMyRow, 3).Value = lname.Value
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Thanks!
Bookmarks