Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform to increment line item numbers with textboxes-spinbuttons
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform to increment line item numbers with textboxes-spinbuttons
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform to increment line item numbers with textboxes-spinbut
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 "Joel" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform to increment line item numbers with textboxes-spinbut
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 wrote: 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 "Joel" wrote: 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" wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform to increment line item numbers with textboxes-spinbut
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 a 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 "Dave Peterson" wrote: 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 wrote: 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 "Joel" wrote: 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" wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform to increment line item numbers with textboxes-spinbut
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. "owlnevada" wrote: 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 a 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 "Dave Peterson" wrote: 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 wrote: 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 "Joel" wrote: 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" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
Userform- Formating numbers in Textboxes | Excel Programming | |||
userform & textboxes | Excel Programming | |||
userform textboxes again | Excel Programming | |||
add numbers in textboxes on userform | Excel Programming |