MS Excel - VB: how can I add an autonumber label to my userform?

Question

Hi, I am a newbie in VB and I have this userform to create a database and the first column in my database is the entry number which is generated automatically by the code:
ActiveCell.Value = Application.WorksheetFunction.Max(Range("A:A")) + 1

Now I would also like to have the new entry number appear in my userform as well in a label or text box. I have tried many things but could not get it work.

formnumberlabel.Caption = Application.WorksheetFunction.Max(Range("A:A")) + 1
surely does not work.

I would appreciate if you could help me with this. Thank you in advance!

Answer 1

Try this event handler. This assumes the 'first column' in your workbook is column 'A'.



Private Sub UserForm_Initialize()

Dim LastRow

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

If IsNumeric(ActiveSheet.Range("A" & LastRow)) Then

ActiveSheet.Range("A" & LastRow).Offset(1).Value = _

ActiveSheet.Range("A" & LastRow).Value + 1

Else

ActiveSheet.Range("A" & LastRow).Offset(1) = 1

End If

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Me.formnumberlabel.Caption = ActiveSheet.Range("A" & LastRow)

End Sub



This assumes that there is a column header in cell A1. I would suggest that you specify the worksheet name instead of 'ActiveSheet' in the code above. If you were to call your userform from another worksheet, bad things could happen. For example, if your worksheet is named "New Entry", change the lines thusly:



Private Sub UserForm_Initialize()

Dim LastRow

LastRow = Sheets("New Entry").Range("A" & Rows.Count).End(xlUp).Row

If IsNumeric( Sheets("New Entry").Range("A" & LastRow)) Then

Sheets("New Entry").Range("A" & LastRow).Offset(1).Value = _

Sheets("New Entry").Range("A" & LastRow).Value + 1

Else

Sheets("New Entry").Range("A" & LastRow).Offset(1) = 1

End If

LastRow = Sheets("New Entry"). Range("A" & Rows.Count).End(xlUp).Row

Me.formnumberlabel.Caption = Sheets("New Entry").Range("A" & LastRow)

End Sub

Answer 2

You formula was cut off slightly, so assuming your function looks like:



formnumberlabel.Caption = Application.WorksheetFunction.Max(Range( "A:A")) + 1



then it should work, but the max worksheet function will not work if there is any sort of error (#DIV/0, #N/A, #NAME?, etc) in the range being evaluated.



to trap the error you'll need to do something like:



On Error Resume Next

formnumberlabel.Caption = Application.WorksheetFunction.Max(Range( "A:A")) + 1

if err.number <> 0 then

msgbox "Error encountered"

exit sub

end if

on error goto 0 'or reset your normal error handler

Answer 3

that's no longer sparkling in case you want to print the at present open Excel record or another unopened record. right it is the code to print the energetic sheet of the present Excel record ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=actual