How autonumber in textbox on userform based on click three buttons

[Omar M]

Hi
I have numbering in cell G12 for each sheet , I have four sheets(DATA,OUTPUT,INPUT,EXTRACTION).
and I have userform contains textbox1 to show autonumbering basd on G12 and combobox1 contains sheets names. also I have commandbutton1,commandbutton2,commandbutton3 .
so when running the userform and select sheet name from combobox1 then will populate sheet name and autonumbering in textbox1 and G12 .
for instance
combobox1= DATA
then textbox1= "DATA INV NO DA 0000001" and G12 will be="DATA INV NO DA 0000001"
so should write INV NO , as to DA should be based on two letters for sheet name from left "DA" and write number "0000001"
when click commandbutton1 then will be in textbox1,G12="DATA INV NO DA 0000002" should increment the invoice number.
if I click commandbutton2 then will decrease invoice number to become from "DATA INV NO DA 0000002" to "DATA INV NO DA 0000001"
but if I click commandbutton3 then should return the original invoice number is existed in G12 without increase or decrease number.
the same thing for any sheet is selected from combobox1 should poulate the sheet name and write INV NO and two letters for the sheet from left and write 0000001
I hope to find help from experts.
thanks

 

[Akuini]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

 

[Omar M]

Hi
thanks for your interesting .
here is the file.
I hope this help.
autonumbering.xlsm

 

[Artik]

To begin with, such a code proposal. Full code in Userform1 module:

Option Explicit


Private Sub ComboBox1_Change()
    Dim wks As Worksheet
    Dim InvNo As String
   
    Set wks = ThisWorkbook.Worksheets(Me.ComboBox1.Value)
    InvNo = wks.Range("G12").Value
   
    If Len(InvNo) = 0 Then
        InvNo = "DATA INV NO " & Left(Me.ComboBox1.Value, 2) & " 0000001"
    End If
   
    Me.TextBox1.Value = InvNo
End Sub


Private Sub CommandButton1_Click()
    'Add
    Call Increment(True)
End Sub


Private Sub CommandButton2_Click()
    'Subtract
    Call Increment(False)
End Sub


Private Sub CommandButton3_Click()
    'Restore
    Call ComboBox1_Change
End Sub


Private Sub UserForm_Initialize()
    Dim ws As Worksheet
   
    For Each ws In Worksheets(Array("data", "extraction", "output", "input"))
        Me.ComboBox1.AddItem ws.Name
    Next

End Sub


Private Sub Increment(Add As Boolean)
    Dim v As Variant
    Dim InvNo As String
   
    InvNo = Me.TextBox1.Value
    v = Split(InvNo)
    v(UBound(v)) = Format(v(UBound(v)) + IIf(Add, 1, -1), "0000000")
    If v(UBound(v)) < 1 Then Exit Sub
    InvNo = Join(v)
    Me.TextBox1.Value = InvNo

End Sub

You should think independently when the new invoice number should be written to the selected sheet.

Artik

 

[Omar M]

To begin with, such a code proposal. Full code in Userform1 module:

should be in G12 as I said
it will depend on G12 when save file and open then should show in textbox1
for instance if I open file and G12=DATA INV NO DA 0000002" in DATA sheet and select DATA sheet from combobox then will be DATA INV NO DA 0000002 in textbox1 and if I click commandbutton1 then will be DATA INV NO DA 0000003 in textbox2 and if I click commandbutton2 then will be DATA INV NO DA 0000001 in textbox1 and if I commandbutton 3 also will be DATA INV NO DA 0000001 in textbox1

every time incrementing should show in G12

 

[Artik]

The problem is that it is not clear which state of the G12 cell(s) to consider primary. Is it the one from the moment the workbook is opened, or from some other moment, such as after the form is closed and reopened.

Artik

 

[Omar M]

Is it the one from the moment the workbook is opened, or from some other moment, such as after the form is closed and reopened.

what's the difference,please?
actually I'm confused !
example:
when open file from the first time the G12 is empty and when userform and when running form and select sheet DATA and click commandbutton1 then autonumbering in textbox1 "DATA INV NO DA 0000001 " so I save and close file .
when open file then the G12 will be "DATA INV NO DA 0000001 and when running form and select sheet DATA and click commandbutton1 then autonumbering in textbox1 "DATA INV NO DA 0000002 . so I save and close.
when open file then the G12 will be "DATA INV NO DA 0000002 and when running form and select sheet DATA and click commandbutton1 then autonumbering in textbox1 "DATA INV NO DA 0000003 . and if I click commandbutton3 then will return to DATA INV NO DA 0000002 and I save and close file.
I hope this help you.

 

[Artik]

So, I consider the original state of the G12 cells to be the state I found when I opened the file or the state after saving the file.

In ThisWorkbook module:

Option Explicit


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call InitializeArr
End Sub

In standard module (e.g. Module1):

Option Explicit

Public g_vArrSheets As Variant
Public g_vArrInvNmbrs As Variant


Sub InitializeArr()
    Dim i As Long

    g_vArrSheets = Array("data", "extraction", "output", "input")
    ReDim g_vArrInvNmbrs(0 To UBound(g_vArrSheets))

    For i = 0 To UBound(g_vArrSheets)
        g_vArrInvNmbrs(i) = ThisWorkbook.Worksheets(g_vArrSheets(i)).Range("G12").Value
    Next i

End Sub

and in UserForm1 module:

Option Explicit


Private Sub ComboBox1_Change()
    Dim InvNo As String

    If Me.ComboBox1.ListIndex >= 0 Then

        InvNo = g_vArrInvNmbrs(Me.ComboBox1.ListIndex)

        Me.TextBox1.Value = InvNo

        ThisWorkbook.Worksheets(Me.ComboBox1.Value).Range("G12").Value = InvNo
    End If
End Sub


Private Sub CommandButton1_Click()
    'Add
    Call Increment(True)
End Sub


Private Sub CommandButton2_Click()
    'Subtract
    Call Increment(False)
End Sub


Private Sub CommandButton3_Click()
    'Restore
    If Me.ComboBox1.ListIndex >= 0 Then
        Call ComboBox1_Change
        Me.TextBox1.Value = g_vArrInvNmbrs(Me.ComboBox1.ListIndex)
    End If
End Sub


Private Sub UserForm_Initialize()
    Dim wks As Worksheet

    If IsEmpty(g_vArrSheets) Then
        Call InitializeArr
    End If

    For Each wks In Worksheets(g_vArrSheets)
        Me.ComboBox1.AddItem wks.Name
    Next wks

End Sub


Private Sub Increment(Add As Boolean)
    Dim v As Variant
    Dim InvNo As String

    InvNo = Me.TextBox1.Value
    If Len(InvNo) = 0 Then
        InvNo = "DATA INV NO " & Left(Me.ComboBox1.Value, 2) & " 0"
    End If

    v = Split(InvNo)
    v(UBound(v)) = Format(v(UBound(v)) + IIf(Add, 1, -1), "0000000")
    If v(UBound(v)) < 1 Then Exit Sub
    InvNo = Join(v)
    Me.TextBox1.Value = InvNo
    ThisWorkbook.Worksheets(Me.ComboBox1.Value).Range("G12").Value = InvNo

End Sub

The original state of the G12 cells is stored in the global array variable g_vArrInvNmbrs. Pressing CommandButton3 will retrieve the corresponding value from this array, display it in TextBox1 and in cell G12 of the selected sheet. CommandButton1 and 2 will change the invoice number by 1, display in TextBox1 and G12 of the selected sheet. CommandButton2 will not allow decrementation less than 1.
If you save the workbook (but do not close it), the original state of the cells changes from that point on.

Artik

 

[Omar M]

there is problem ,when auto numbering you use DATA word for all of sheets , but DATA is sheet name
so if I select EXTRACTION sheet then will be "EXTRACTION INV NO EX 0000001" .

 

[dmt32]

there is problem ,when auto numbering you use DATA word for all of sheets , but DATA is sheet name
so if I select EXTRACTION sheet then will be "EXTRACTION INV NO EX 0000001" .

Hi,

See if following approach will do what you want

Make backup of your workbook & then delete ALL the codes in your userform code page & replace then with the following codes

Option Explicit

Dim wsInvoice()         As Worksheet
Const xlUndo            As Long = 1, xlStartValue As Long = 2

Private Sub cboSheetName_Change()
    Increment xlStartValue
End Sub

Private Sub cmdUp_Click()
    Increment xlUp
End Sub

Private Sub cmdDown_Click()
    Increment xlDown
End Sub

Private Sub cmdUndo_Click()
    Increment xlUndo
End Sub

Private Sub Increment(ByVal Direction As XlDirection)
    Dim InvNo       As Long, i  As Long
    Dim Prefix      As String
    
    i = Me.cboSheetName.ListIndex + 1
    
    With wsInvoice(i)
        Prefix = .Name & " INV NO " & UCase(Left(.Name, 2))
        With .Range("G12")
            InvNo = IIf(Direction = xlUndo, Val(.ID), Val(.Value))
            InvNo = IIf(InvNo = 0, 1, IIf(Direction = xlUp, InvNo + 1, IIf(Direction = xlDown, InvNo - 1, InvNo)))
            .Value = InvNo
            If Direction = xlStartValue Then .ID = .Value
            .NumberFormat = """" & Prefix & """" & " 0000000"
            Me.txtInvoiceNo.Value = .Text
        End With
    End With
    
    Me.cmdDown.Enabled = InvNo > 1
    Me.cmdUndo.Enabled = Direction <> xlStartValue
End Sub

Private Sub UserForm_Initialize()
    Dim i           As Long
    Dim arr         As String
    Dim ws          As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets(Array("data", "extraction", "output", "input"))
        i = i + 1
        ReDim Preserve wsInvoice(1 To i): Set wsInvoice(i) = ws: arr = arr & ws.Name & ","
    Next ws
    
    arr = Left(arr, Len(arr) - 1)
    With Me.cboSheetName
        .List = Split(arr, ",")
        .ListIndex = 0
        .Style = fmStyleDropDownList
    End With
End Sub

You should note that I have given all the Controls meaningful names & you will need to rename your existing controls accordingly

• txtInvoiceNo
• cboSheetName
• cmdUp
• cmdDown
• cmdUndo

Solution on lightly tested but hopefully, will do what you want

Dave