VBA Code to Auto Populate Text Box with Sequential Record #

[Parebody]

I am setting up a form for my excel spreadsheet. When I click on my form button, I want the first text box in my form to auto populate with the next available record # from my spreadsheet. My record # format is as follows: FP000001, FP000002, FP000003, etc... Any suggestions on how to write the code to auto populate the record # text box when the form is opened? I'm getting hung up on the format of the record # (letters & numbers).

 

[lrobbo314]

How about this? You may need to change 'Me.TextBox1' to the name of your record # textbox.

Private Sub UserForm_Activate()
Me.TextBox1.Text = "FP" & Format(Replace(Range("A" & Rows.Count).End(xlUp).Text, "FP", "") + 1, "000000")
End Sub

 

[DanteAmor]

If in cell A1 you have the header, and the first value will be in cell A2, try the following code, it even works for the first record.

Private Sub UserForm_Activate()
  TextBox1.Value = "FP" & Format(Range("A" & Rows.Count).End(3).Row, "000000")
End Sub

 

[Parebody]

That worked perfectly! And now I have better insight on how to combine text and numbers for formatting. Thanks again!

 

[DanteAmor]

Glad we could help & thanks for the feedback