Sequential numbering in a userform

[kevindorward_dfsdanf]

So I have a userform with comboboxes feeding on to a label to create a document name. I need to tag sequential numbering to the end based on all the criteria that makes up the document name e.g the number following should only ever increase if document name preceeding it is identical.

Have done this using formulas but having trouble translating that to the userform.

I want to be able to look at the document name that comboboxes have produced without the number added on, see if any cells match have that document name as the start with a number after it in the sheet, find the max value of that number and +1

My thoughts about how I might achieve it:

Define start number as 000001

Define Dummy string
Find the document name the comboboxes produce up to last character and define that as a string i.e. Dname

Define last row and look at the string before the numbers
For x=1 to last row

Doc number
If leftmost 25 characters = dname (I. e. It exists in the range) then extract max rightmost 6 numbers and define that as last used number else number is 000001

New number = last number +1

Document name = Dname & "-" & new number


The document name is not a set length though, sometimes I can be 22,24,26 characters and so looking at the leftmost characters I don't think will work.

 

[DanteAmor]

Here is an example with 3 combo and a label1.
The number on the sheet is made up of combo1 & combo2 & combo3 & - & 000000

Change "Sheet5" for the name of your sheet. The macro assumes that the file names are in column A.

Private Sub CommandButton1_Click()
 
  Label1.Caption = ""
 
  If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Or _
     ComboBox2.Value = "" Or ComboBox2.ListIndex = -1 Or _
     ComboBox3.Value = "" Or ComboBox3.ListIndex = -1 Then
      MsgBox "Fill combos"
      Exit Sub
  End If
 
  Dim c As Range, sh As Worksheet, sStr As String, n As Long, nMax As Long
 
  Set sh = Sheets("Sheet5")
  sStr = ComboBox1.Value & ComboBox2.Value & ComboBox3.Value & "-"
  nMax = 0
  For Each c In sh.Range("A1", sh.Range("A" & Rows.Count).End(xlUp))
    If UCase(Left(c.Value, Len(sStr))) = UCase(sStr) Then
      n = Val(Mid(c.Value, Len(sStr) + 1))
      If n > nMax Then nMax = n
    End If
  Next
  nMax = nMax + 1
 
  Label1.Caption = sStr & Format(nMax, "000000")
 
End Sub

 

[kevindorward_dfsdanf]

Perfect that worked as intended when I changed it to 6 comboboxes

My label that displays the document number though wont show the number until I click on it though.
Is there a way to say when the last combobox is not empty or when all 6 have a value from the dropdown, then update the label?

I had tried Me.Repaint and DoEvents to no avail. Is it worth setting it to a textbox, does that have better properties to do what I need because I can lock it I suppose.

 

[DanteAmor]

Is there a way to say when the last combobox is not empty or when all 6 have a value from the dropdown, then update the label?

Try something like this:

Private Sub ComboBox1_Change()
  CommandButton1_Click
End Sub
Private Sub ComboBox2_Change()
  CommandButton1_Click
End Sub
Private Sub ComboBox3_Change()
  CommandButton1_Click
End Sub
Private Sub ComboBox4_Change()
  CommandButton1_Click
End Sub
Private Sub ComboBox5_Change()
  CommandButton1_Click
End Sub
Private Sub ComboBox6_Change()
  CommandButton1_Click
End Sub