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

 

[Omar M]

Hi Dave,
seem there is problem !
see the picture.

 

[dmt32]

Hi Dave,
seem there is problem !
see the picture.

Not a problem - you just need to increase the worksheet column width

Also, spotted a minor mistake after posting

Replace this line of code

Me.cmdUndo.Enabled = Direction <> xlStartValue

with this

Me.cmdUndo.Enabled = Direction < 1

16-05-2024.xls
	G
9
10
11	INVOICE NUMBER
12	DATA INV NO DA 0000002
13
DATA

 

[Omar M]

doesn't work well
I suppose when click commandbutton1 should incrememt the numbers , but doesn't do that!

 

[dmt32]

doesn't work well
I suppose when click commandbutton1 should incrememt the numbers , but doesn't do that!

code works fine for me & as my code did not have a commandbutton1 suggests you may not have fully followed my instructions.

Try this sample file: Autonumber

 

[Omar M]

as my code did not have a commandbutton1 suggests you may not have fully followed my instructions.

no I call tools as you suggest , just write wrong expression.
What I meant when click cmdUp
I will check my bad again !
by the way about your attaching is error link , can't download.

 

[dmt32]

by the way about your attaching is error link , can't download.

It would seem my account subscription has expired - sorry about that. Try this link from basic account Autonumber

Dave

 

[Omar M]

Hi Dave,

I've found out my bad !

about commandbutton1 when rename I did that twice cmdUpcmdUp that's why doesn't incrementing .

just request if you don't mind .

can activate sheet based selected from combobox1,please?

 

[dmt32]

can activate sheet based selected from combobox1,please?

Hi,
glad found the issue & resolved

Try this updated version of the Increment code

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)
        .Activate
        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"
            .Columns.AutoFit
            Me.txtInvoiceNo.Value = .Text
        End With
    End With
    
    Me.cmdDown.Enabled = InvNo > 1
    Me.cmdUndo.Enabled = Direction < 1
End Sub

Dave

 

[Omar M]

really excellent ,Dave .
thank you so much for your assistance .

 

[dmt32]

really excellent ,Dave .
thank you so much for your assistance .

most welcome - glad we were able to help & appreciate your feedback

Dave