Unique Reference Number with VBA Userform

cdub27455

New Member
Joined
Mar 22, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
2-Part Question:

Part 1:
I am fairly new to VBA and attempting to create code in my Userform VBA that will add +1 to each subsequent reference number. I'm working in m "Data" worksheet and column "A". My reference numbers start with "52-" and contain four digits after (ex: 52-0001. 52-0002, etc.). I have tried several different codes using the following:

VBA Code:
Dim TargetRow As Integer
Dim LstRow As Long 'Figure out last value
Dim OVal As String 'Old Value
Dim NVal As String 'New Value


OTargetRow = Sheets("Engine").Range("B3").Value
TargetRow = Sheets("Engine").Range("B3").Value + 1

Part 2
In addition, I would like a MsgBox to appear after selecting "Submit" that will show the new reference ID (ex: "Sample added to log as Sample #: 52-0001"

Thank you!
 

Attachments

  • ExcelExample_RefNum.jpg
    ExcelExample_RefNum.jpg
    35.4 KB · Views: 12

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
From my understanding this can't work because you try to add a value (1) onto a string (text).
This can be solved very easy in you keep your values in column A as a number (520001) and set it's custom number format to 00-0000.
Or if you want it to do in VBA you split up your values into a string and number, add 1, rejoin both into 1 string.
 
Upvote 0
Welcome to the Board!

Assuming it always follows that pattern, try this:
VBA Code:
Sub AddNewNumber()

    Dim lr As Long
    Dim oldNum As String
    Dim newNum As String
    
'   Find last row in column B with entry
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Calculate new number from old number
    oldNum = Cells(lr, "B")
    newNum = Left(oldNum, 3) & Format(Right(oldNum, 4) + 1, "0000")
    
'   Place new number on next row in sheet
    Cells(lr + 1, "B") = newNum
    
'   Return Message Box with new value
    MsgBox "Sample added to log as Sample #: " & newNum

End Sub
 
Upvote 0
Solution
Thank you so much Joe4!! That worked perfectly. I've been searching and running multiple trials to get this to work and this worked on the first try. Also, thank you for the welcome! I'm enjoying this so far!
 
Upvote 0
You are welcome!
Glad I was able to help.
:)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top