Userform to increment line item numbers with textboxes-spinbuttons

[owlnevada]

I use the following code with a userform to renumber line item numbers that
get changed. My UF has two spinbuttons with two textboxes that update each
other for a starting and ending line item number. I need help with just this
part that looks at those two entries to change and increment the line number
by one from the starting line no. to the ending line number. These can be
something like "1" to "8" or "9" to "16" etc. H becomes the starting line
number and the other variables that should increment but don't. I am
modifying older code that works but is not as efficient that does not use the
loop routine.

Any help is much appreciated.

Private Sub cmdOK_Click()
Dim H As Integer, lineno As Integer, newno As Integer

ActiveCell.Activate
ActiveCell.FormulaR1C1 = TextBox1.Value
ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Activate
Selection.ClearContents
H = TextBox1.Value 'from textbox1 on userform1
Do Until lineno = TextBox2.Value 'value in textbox2 on userform1
lineno = H
ActiveCell.Offset(RowOffset:=4, ColumnOffset:=0).Activate
ActiveCell.FormulaR1C1 = H + newno
newno = 1
ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Activate
Selection.ClearContents
newno = H + newno
Loop
Exit Sub


End Sub

 

[Joel]

See if this works, if not, it should be simplier for you to figure out what
is wrong with this code.

Private Sub cmdOK_Click()
Dim H As Integer, lineno As Integer, newno As Integer

ActiveCell.FormulaR1C1 = TextBox1.Value

Set FirstCell = ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0)
H = TextBox1.Value 'from textbox1 on userform1
RowOffset = 0
newno = 1
Do
lineno = H
FirstCell.Offset(RowOffset:=RowOffset, ColumnOffset:=0).ClearContents
FirstCell.Offset(RowOffset:=RowOffset + 4, ColumnOffset:=0) _
.FormulaR1C1 = H + newno
newno = newno + H
RowOffset = RowOffset + 5
Loop While lineno < TextBox2.Value 'value in textbox2 on userform1
Exit Sub


End Sub

 

[owlnevada]

We'll we tried your solution but ran into other problems. I came up with this
option and it works as long as the first textbox is a "1". If 2 or higher it
adds it and doubles the second item then continues to increment by one. . ..
So with this code, can you find an elegant solution that will work whenever
the first number is 2 or higher?


Private Sub cmdOK_Click()
Dim startno As Integer ', lineno As Integer, newno As Integer
Dim i As Integer

ActiveCell.Activate
ActiveCell.FormulaR1C1 = TextBox1.Value

ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Activate
Selection.ClearContents

For i = TextBox1.Value To (TextBox2.Value - 1)
startno = 0
ActiveCell.Activate
startno = TextBox1.Value + i 'this gets doubled if other than a
one in textbox1

ActiveCell.Offset(RowOffset:=4, ColumnOffset:=0).Activate
ActiveCell.FormulaR1C1 = TextBox1.Value + i
ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Activate
Selection.ClearContents

Next i

End Sub

 

[Dave Peterson]

I'm confused at what you're doing, but maybe...

Option Explicit
Private Sub cmdOK_Click()
Dim startno As Long
Dim iRow As Long
Dim StartCell As Range

'some validation???
If IsNumeric(Me.TextBox1.Value) = False _
Or IsNumeric(Me.TextBox2.Value) = False Then
MsgBox "Non-numbers in textboxes!"
Exit Sub
End If

If CLng(Me.TextBox1.Value) >= CLng(Me.TextBox2.Value) Then
MsgBox "Numbers not in right order!"
Exit Sub
End If

Set StartCell = ActiveCell

For iRow = CLng(Me.TextBox1.Value) To CLng(Me.TextBox2.Value)
StartCell.Value = iRow
Set StartCell = StartCell.Offset(1, 0)
Next iRow

End Sub

This just fills a column/range of cells with consecutive values starting with
textbox1 and finishing with textbox2--and places the first number in the
activecell.

If this doesn't help, you may want to describe (in plain old words) what you're
trying to accomplish.

 

[owlnevada]

That worked fine with a slight addition at the end to delete the older line
numbers that this macro revises. Here's the final code below this next
question FYI.

Now, I need this to be activated from my xlam file. Is there something I'm
missing that it takes to activate this form from a ribbon button? I get the
"Cannot run the macro "UserForm_Initialize . . . " error. There's several
files behind the form of course but this one seems to be called first and
should probably have the (optional control as iribboncontrol) statement
following the procedure name.

Private Sub UserForm_Initialize(Optional control As IRibbonControl)

The other forms are:

Private Sub AutoReNumber_Click()
Load UserForm1

End Sub

Private Sub cmdCancel_Click()
End
End Sub

Private Sub SpinButton1_Change()
TextBox1.Text = SpinButton1.Value

End Sub

Private Sub SpinButton2_Change()
TextBox2.Text = SpinButton2.Value
End Sub

Private Sub TextBox1_Change()
Dim NewVal As Integer
NewVal = Val(TextBox1.Text)
If NewVal >= SpinButton1.Min And NewVal <= SpinButton1.Max Then
SpinButton1.Value = NewVal
End If
End Sub

Private Sub TextBox2_Change()
Dim NewVal As Integer
NewVal = Val(TextBox2.Text)
If NewVal >= SpinButton2.Min And NewVal <= SpinButton2.Max Then
SpinButton2.Value = NewVal
End If
End Sub
End Sub

This is the final version that works from the VBE only:

Private Sub cmdOK_Click()

Dim startno As Long
Dim iRow As Long
Dim StartCell As Range

'some validation???
If IsNumeric(Me.TextBox1.Value) = False _
Or IsNumeric(Me.TextBox2.Value) = False Then
MsgBox "Non-numbers in textboxes!"
Exit Sub
End If

If CLng(Me.TextBox1.Value) >= CLng(Me.TextBox2.Value) Then
MsgBox "Numbers not in right order!"
Exit Sub
End If

Set StartCell = ActiveCell

For iRow = CLng(Me.TextBox1.Value) To CLng(Me.TextBox2.Value)
StartCell.Value = iRow
Set StartCell = StartCell.Offset(5, 0)
Next iRow

ActiveCell.Offset(1, 0).Activate
Selection.ClearContents
ActiveCell.Offset(5, 0).Activate
Selection.ClearContents
ActiveCell.Offset(5, 0).Activate
Selection.ClearContents
ActiveCell.Offset(5, 0).Activate
Selection.ClearContents
ActiveCell.Offset(5, 0).Activate
Selection.ClearContents
ActiveCell.Offset(5, 0).Activate
Selection.ClearContents
ActiveCell.Offset(5, 0).Activate
Selection.ClearContents
ActiveCell.Offset(5, 0).Activate
Selection.ClearContents

Range("N2").Activate
End

End Sub

 

[owlnevada]

I was able to figure out the correct placement of the Userform_Initialize
that needs to be in the standard module and not the form module to get this
to work with the help of a colleague. And that the form procedures do not
need the Optional control as iribboncontrol statements inside the parentheses.

The one thing I need now would be how to incorporate the last lines that
clear the contents from the previous line numbers in the For Next loop from
their original location which is one row below where this now puts the new
line item numbers. . . for a slightly more elegant procedure. Any
suggestions?

Thanks again in advance.