Textbox Value +1

[gilly01625]

Hey. I am developing a spreadsheet using user form's for the purpose of tracking freelance work including shifts, clients, income, expense, and invoices.

I am trying to generate a new reference number for each new data entry into a table. I currently have a user form which when completed, populates a table on a worksheet - I want to generate a reference number in the following format - OLUK-0000, and OLAUS-0000. I want the 0000 to increase by 1 every time a new entry is made (OLUK-0001, OLUK-0002 etc). There are 2 types of reference, OLUK (referring to work carried out in the UK), and OLAUS (referring to work carried out in Australia). I have check boxes labelled as UK and AUS -when one of these options is selected, I want a text box to display a new reference number with the corresponding OLUK or OLAUS plus a number.

Is this possible?

Thanks

 

[sxhall]

On the worksheet with the data table can you add a small table like the below to increment the number by 1 from and then save it back too?

Otherwise you'll need to write some code to go through the column with the reference numbers, look at the last for digits and find the highest number in the table!

Fromula in cell D1...

=CONCATENATE("OL" & B5 & "-" & TEXT(IF(B5="UK",B2,B3),"0000"))

When my selection is changed in cell B5 for UK or AUS it references the correct value and adds 1 in the formula.

When saved update your code in the userform to overwrite the entry in the table under ref value for the next time.

 

[gilly01625]

That's worked perfectly! Thank you so much!

 

[MikeVol]

Hi @gilly01625.
Another option since I understood that you have a user form.

Option Explicit

' UKCheckBox Click
Private Sub UKCheckBox_Click()

    ' If UKCheckBox is selected, generate OLUK reference number
    If UKCheckBox.Value = True Then
        GenerateReferenceNumber "OLUK"
        AUSCheckBox.Value = False  ' Make sure only one checkbox is selected
    End If

End Sub

' AUSCheckBox Click
Private Sub AUSCheckBox_Click()

    ' If AUSCheckBox is selected, generate OLAUS reference number
    If AUSCheckBox.Value = True Then
        GenerateReferenceNumber "OLAUS"
        UKCheckBox.Value = False  ' Make sure only one checkbox is selected
    End If

End Sub

' The procedure for generating a unique number
Private Sub GenerateReferenceNumber(ByVal prefix As String)
    Dim cell        As Range

    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")   ' Replace "Sheet1" with the name of your sheet

    Dim lastRow     As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Find the last number for a given prefix
    Dim lastNumber  As Long
    lastNumber = 0

    For Each cell In ws.Range("A2:A" & lastRow)    ' I assume that the links are stored in column A, starting from the second row

        If InStr(cell.Value, prefix) > 0 Then

            ' We extract the numeric part and convert it into a number
            lastNumber = Application.WorksheetFunction.Max(lastNumber, CInt(Mid(cell.Value, Len(prefix) + 2)))
        End If

    Next cell

    ' Increase by +1 for new entry
    Dim newNumber   As Long
    newNumber = lastNumber + 1

    Dim newReference As String
    newReference = prefix & "-" & Format(newNumber, "0000")

    ' Display the new reference number in the textbox on UserForm1
    ReferenceTextBox.Value = newReference
End Sub

' I assume you have a button to save the data from the form to the table
Private Sub SubmitButton_Click()
    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")  ' Replace "Sheet1" with the name of your sheet

    ' Find the next blank row on the worksheet
    Dim newRow      As Long
    newRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    ' Save data from the textbox with the reference number
    ws.Cells(newRow, 1).Value = ReferenceTextBox.Value

    ' Here add your existing code for writing other data from the form to the desired columns, I don't know it, so add it yourself

    ' Clearing the textbox and checkboxes after writing data
    ReferenceTextBox.Value = ""
    UKCheckBox.Value = False
    AUSCheckBox.Value = False
End Sub

Private Sub UserForm_Initialize()

    ' Clearing the textbox when the form loads
    ReferenceTextBox.Value = ""
End Sub

Here is the result of running this code.

I hope this is what you needed and it was a pleasure to help you. Good luck.

 

[gilly01625]

Hi @gilly01625.
Another option since I understood that you have a user form. View attachment 119063

Option Explicit

' UKCheckBox Click
Private Sub UKCheckBox_Click()

    ' If UKCheckBox is selected, generate OLUK reference number
    If UKCheckBox.Value = True Then
        GenerateReferenceNumber "OLUK"
        AUSCheckBox.Value = False  ' Make sure only one checkbox is selected
    End If

End Sub

' AUSCheckBox Click
Private Sub AUSCheckBox_Click()

    ' If AUSCheckBox is selected, generate OLAUS reference number
    If AUSCheckBox.Value = True Then
        GenerateReferenceNumber "OLAUS"
        UKCheckBox.Value = False  ' Make sure only one checkbox is selected
    End If

End Sub

' The procedure for generating a unique number
Private Sub GenerateReferenceNumber(ByVal prefix As String)
    Dim cell        As Range

    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")   ' Replace "Sheet1" with the name of your sheet

    Dim lastRow     As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Find the last number for a given prefix
    Dim lastNumber  As Long
    lastNumber = 0

    For Each cell In ws.Range("A2:A" & lastRow)    ' I assume that the links are stored in column A, starting from the second row

        If InStr(cell.Value, prefix) > 0 Then

            ' We extract the numeric part and convert it into a number
            lastNumber = Application.WorksheetFunction.Max(lastNumber, CInt(Mid(cell.Value, Len(prefix) + 2)))
        End If

    Next cell

    ' Increase by +1 for new entry
    Dim newNumber   As Long
    newNumber = lastNumber + 1

    Dim newReference As String
    newReference = prefix & "-" & Format(newNumber, "0000")

    ' Display the new reference number in the textbox on UserForm1
    ReferenceTextBox.Value = newReference
End Sub

' I assume you have a button to save the data from the form to the table
Private Sub SubmitButton_Click()
    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")  ' Replace "Sheet1" with the name of your sheet

    ' Find the next blank row on the worksheet
    Dim newRow      As Long
    newRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    ' Save data from the textbox with the reference number
    ws.Cells(newRow, 1).Value = ReferenceTextBox.Value

    ' Here add your existing code for writing other data from the form to the desired columns, I don't know it, so add it yourself

    ' Clearing the textbox and checkboxes after writing data
    ReferenceTextBox.Value = ""
    UKCheckBox.Value = False
    AUSCheckBox.Value = False
End Sub

Private Sub UserForm_Initialize()

    ' Clearing the textbox when the form loads
    ReferenceTextBox.Value = ""
End Sub

Here is the result of running this code. View attachment 119064View attachment 119065View attachment 119066I hope this is what you needed and it was a pleasure to help you. Good luck.

This is perfect! Thank you so much!