Microsoft Office Forums - View Single Post - Another Sequential Numbering Question (1,2,3a,3b,3c,4,5,6)

highrise955 · 03-19-2016, 04:14 AM

Hi All,

Another sequential numbering question.

I did a search for sequential numbering in this forum but none of the threads seemed to focus on what I needed.

If I am mistaken, please direct me to the appropriate thread.

I have a table and the first cell in each row starts with a number. Most of the time simple sequential numbering works just fine. However, there are times that instead of using the next number in the sequence the users need to use #a, #b, #c..

For example...

1
2
3a
3b
3c
4
5
6

Below is the code I am using (borrowed

) to create each new row.

Code:

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)

Dim i As Long, j As Long, Prot As Variant
Dim tRows As Integer
Const Pwd As String = "" 'Insert password (if any) here

With CCtrl
  'Check that the Content Control is within our table.
  If .Range.InRange(ActiveDocument.Tables(1).Range) = False Then Exit Sub
  'Get the number of ContentControls in the table
  i = .Range.Tables(1).Range.ContentControls.Count - 1
  'Get our ContentControl's index # in the table
  j = ActiveDocument.Range(.Range.Tables(1).Range.Start, .Range.End).ContentControls.Count
  'Check that we're using the last content control
  If i <> j Then Exit Sub
End With


'Solicit user input
If MsgBox("Add a new dimension?", vbQuestion + vbYesNo, "Inspection Report") <> vbYes Then Exit Sub

With ActiveDocument
  ' Un-protect the document, if applicable
  Prot = .ProtectionType
  If .ProtectionType <> wdNoProtection Then
    Prot = .ProtectionType
    .Unprotect Password:=Pwd
  End If
  With Selection.Tables(1).Rows
    'Insert an empty paragraph after our table, then replace it with a replica of the last row
    With .Last.Range
      .Next.InsertBefore vbCr
      .Next.FormattedText = .FormattedText
    End With
    'Reset all content controls in the new last row
    For Each CCtrl In .Last.Range.ContentControls
      With CCtrl
        If .Type = wdContentControlCheckBox Then .Checked = False
        If .Type = wdContentControlRichText Or .Type = wdContentControlText Then .Range.Text = " "
        If .Type = wdContentControlDropdownList Then .DropdownListEntries(1).Select
        If .Type = wdContentControlComboBox Then .DropdownListEntries(1).Select
        If .Type = wdContentControlDate Then .Range.Text = " "
      End With
    Next
  End With
  
Below is the current method I am using to add the next number in sequence. 
FYI: "8" is the amount of rows in the header which needs to be ignored in
determining the amount of data input rows.
(I have no doubt the VBA experts will cringe at the inefficiency of this code.)
   Selection.EndKey Unit:=wdColumn
    Selection.StartOf Unit:=wdRow
    tRows = Selection.Information(wdMaximumNumberOfRows)
    tRows = tRows - 8
    Selection.Delete
    Selection.TypeText tRows
    
  ' Re-protect the document, if applicable
  .Protect Type:=Prot, Password:=Pwd
End With
End Sub

Obviously, I could just let the users type the numbers manually but I am trying to make this form as automated as possible. I'm not sure if I need to use a content control in the cell in question or if there is a way to do this without one.

Any input on how to proceed would be greatly appreciated.