|
Forms - Loop thru text boxes
Hi All,
I have a userform with some 100 text boxes on it. On initialization l apply the values to the text boxes using this code (extract only) Private Sub UserForm_Initialize() Sheets("List_CellsLids").Select Range("N2").Select With frmAccom tbUnit1.Text = ActiveCell.Value tbUnit2.Text = ActiveCell.Offset(1, 0).Value tbUnit3.Text = ActiveCell.Offset(2, 0).Value tbUnit4.Text = ActiveCell.Offset(3, 0).Value tbUnit5.Text = ActiveCell.Offset(4, 0).Value tbUnit6.Text = ActiveCell.Offset(5, 0).Value tbUnit7.Text = ActiveCell.Offset(6, 0).Value tbUnit8.Text = ActiveCell.Offset(7, 0).Value tbUnit9.Text = ActiveCell.Offset(8, 0).Value tbUnit10.Text = ActiveCell.Offset(9, 0).Value End With End Sub On 'Enter' l would normally enter the following code (extract only) so that if the user 'edits' a value it is written to the appropriate cells ActiveCell.Value = tbUnit1.Text ActiveCell.Offset(1, 0).Value = tbUnit2.Text ActiveCell.Offset(2, 0).Value = tbUnit3.Text ActiveCell.Offset(3, 0).Value = tbUnit4.Text ActiveCell.Offset(4, 0).Value = tbUnit5.Text ActiveCell.Offset(5, 0).Value = tbUnit6.Text ActiveCell.Offset(6, 0).Value = tbUnit7.Text ActiveCell.Offset(7, 0).Value = tbUnit8.Text ActiveCell.Offset(8, 0).Value = tbUnit9.Text ActiveCell.Offset(9, 0).Value = tbUnit10.Text However because l have to do this for so many text boxes and l have several more to write l was hoping to apply the values using a vba / loop code something like the following Dim tbni As Integer Private Sub cmdEnter_Click() 'Sheets("List_CellsLids").Select 'Range("N2").Select tbni = 1 For i = 1 To 10 ActiveCell.Value = "tbUnit" & tbni & ".Value" ActiveCell.Offset(1, 0).Activate tbni = tbni + 1 Next I think the answer is somewhere in the syntax. Can anybody help please? Regards Michael Beckinsale |
Forms - Loop thru text boxes
For i = 1 To 10
ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" & i).Value Next -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have a userform with some 100 text boxes on it. On initialization l apply the values to the text boxes using this code (extract only) Private Sub UserForm_Initialize() Sheets("List_CellsLids").Select Range("N2").Select With frmAccom tbUnit1.Text = ActiveCell.Value tbUnit2.Text = ActiveCell.Offset(1, 0).Value tbUnit3.Text = ActiveCell.Offset(2, 0).Value tbUnit4.Text = ActiveCell.Offset(3, 0).Value tbUnit5.Text = ActiveCell.Offset(4, 0).Value tbUnit6.Text = ActiveCell.Offset(5, 0).Value tbUnit7.Text = ActiveCell.Offset(6, 0).Value tbUnit8.Text = ActiveCell.Offset(7, 0).Value tbUnit9.Text = ActiveCell.Offset(8, 0).Value tbUnit10.Text = ActiveCell.Offset(9, 0).Value End With End Sub On 'Enter' l would normally enter the following code (extract only) so that if the user 'edits' a value it is written to the appropriate cells ActiveCell.Value = tbUnit1.Text ActiveCell.Offset(1, 0).Value = tbUnit2.Text ActiveCell.Offset(2, 0).Value = tbUnit3.Text ActiveCell.Offset(3, 0).Value = tbUnit4.Text ActiveCell.Offset(4, 0).Value = tbUnit5.Text ActiveCell.Offset(5, 0).Value = tbUnit6.Text ActiveCell.Offset(6, 0).Value = tbUnit7.Text ActiveCell.Offset(7, 0).Value = tbUnit8.Text ActiveCell.Offset(8, 0).Value = tbUnit9.Text ActiveCell.Offset(9, 0).Value = tbUnit10.Text However because l have to do this for so many text boxes and l have several more to write l was hoping to apply the values using a vba / loop code something like the following Dim tbni As Integer Private Sub cmdEnter_Click() 'Sheets("List_CellsLids").Select 'Range("N2").Select tbni = 1 For i = 1 To 10 ActiveCell.Value = "tbUnit" & tbni & ".Value" ActiveCell.Offset(1, 0).Activate tbni = tbni + 1 Next I think the answer is somewhere in the syntax. Can anybody help please? Regards Michael Beckinsale |
Forms - Loop thru text boxes
Tom,
Many thanks. Works fine. Another quick question re the same form - if you dont mind When initializing the form l add up the values in the textboxes named tbUnit1 to tbUnit20 and put the result in tbUnittotal. However if the user 'edits' a textbox the revised total does not show. Is there a way to show the correct totals dynamically ? Regards Michael beckinsale "Tom Ogilvy" wrote in message ... For i = 1 To 10 ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" & i).Value Next -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have a userform with some 100 text boxes on it. On initialization l apply the values to the text boxes using this code (extract only) Private Sub UserForm_Initialize() Sheets("List_CellsLids").Select Range("N2").Select With frmAccom tbUnit1.Text = ActiveCell.Value tbUnit2.Text = ActiveCell.Offset(1, 0).Value tbUnit3.Text = ActiveCell.Offset(2, 0).Value tbUnit4.Text = ActiveCell.Offset(3, 0).Value tbUnit5.Text = ActiveCell.Offset(4, 0).Value tbUnit6.Text = ActiveCell.Offset(5, 0).Value tbUnit7.Text = ActiveCell.Offset(6, 0).Value tbUnit8.Text = ActiveCell.Offset(7, 0).Value tbUnit9.Text = ActiveCell.Offset(8, 0).Value tbUnit10.Text = ActiveCell.Offset(9, 0).Value End With End Sub On 'Enter' l would normally enter the following code (extract only) so that if the user 'edits' a value it is written to the appropriate cells ActiveCell.Value = tbUnit1.Text ActiveCell.Offset(1, 0).Value = tbUnit2.Text ActiveCell.Offset(2, 0).Value = tbUnit3.Text ActiveCell.Offset(3, 0).Value = tbUnit4.Text ActiveCell.Offset(4, 0).Value = tbUnit5.Text ActiveCell.Offset(5, 0).Value = tbUnit6.Text ActiveCell.Offset(6, 0).Value = tbUnit7.Text ActiveCell.Offset(7, 0).Value = tbUnit8.Text ActiveCell.Offset(8, 0).Value = tbUnit9.Text ActiveCell.Offset(9, 0).Value = tbUnit10.Text However because l have to do this for so many text boxes and l have several more to write l was hoping to apply the values using a vba / loop code something like the following Dim tbni As Integer Private Sub cmdEnter_Click() 'Sheets("List_CellsLids").Select 'Range("N2").Select tbni = 1 For i = 1 To 10 ActiveCell.Value = "tbUnit" & tbni & ".Value" ActiveCell.Offset(1, 0).Activate tbni = tbni + 1 Next I think the answer is somewhere in the syntax. Can anybody help please? Regards Michael Beckinsale |
Forms - Loop thru text boxes
You would have to trap the entry and perform the calculation. There is not
way to link a sum formula to it. Even if you did it with cell links, the sum formula would get overwritten. You can use a technique documented by John Walkenbach to handle multiple controls with a single event by using a class module. Although written for command buttons, it works for textboxes as well. The only constraint is that they must be events native to the control (such as click, change, keydown, etc) rather than those provided by the containter (such as Enter, BeforeUpdate, Exit, etc) http://www.j-walk.com/ss/excel/tips/tip44.htm Handle Multiple UserForm Buttons With One Subroutine If you have already created individual events for each textbox, you could have them call a common procedure to do the calculation. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, Many thanks. Works fine. Another quick question re the same form - if you dont mind When initializing the form l add up the values in the textboxes named tbUnit1 to tbUnit20 and put the result in tbUnittotal. However if the user 'edits' a textbox the revised total does not show. Is there a way to show the correct totals dynamically ? Regards Michael beckinsale "Tom Ogilvy" wrote in message ... For i = 1 To 10 ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" & i).Value Next -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have a userform with some 100 text boxes on it. On initialization l apply the values to the text boxes using this code (extract only) Private Sub UserForm_Initialize() Sheets("List_CellsLids").Select Range("N2").Select With frmAccom tbUnit1.Text = ActiveCell.Value tbUnit2.Text = ActiveCell.Offset(1, 0).Value tbUnit3.Text = ActiveCell.Offset(2, 0).Value tbUnit4.Text = ActiveCell.Offset(3, 0).Value tbUnit5.Text = ActiveCell.Offset(4, 0).Value tbUnit6.Text = ActiveCell.Offset(5, 0).Value tbUnit7.Text = ActiveCell.Offset(6, 0).Value tbUnit8.Text = ActiveCell.Offset(7, 0).Value tbUnit9.Text = ActiveCell.Offset(8, 0).Value tbUnit10.Text = ActiveCell.Offset(9, 0).Value End With End Sub On 'Enter' l would normally enter the following code (extract only) so that if the user 'edits' a value it is written to the appropriate cells ActiveCell.Value = tbUnit1.Text ActiveCell.Offset(1, 0).Value = tbUnit2.Text ActiveCell.Offset(2, 0).Value = tbUnit3.Text ActiveCell.Offset(3, 0).Value = tbUnit4.Text ActiveCell.Offset(4, 0).Value = tbUnit5.Text ActiveCell.Offset(5, 0).Value = tbUnit6.Text ActiveCell.Offset(6, 0).Value = tbUnit7.Text ActiveCell.Offset(7, 0).Value = tbUnit8.Text ActiveCell.Offset(8, 0).Value = tbUnit9.Text ActiveCell.Offset(9, 0).Value = tbUnit10.Text However because l have to do this for so many text boxes and l have several more to write l was hoping to apply the values using a vba / loop code something like the following Dim tbni As Integer Private Sub cmdEnter_Click() 'Sheets("List_CellsLids").Select 'Range("N2").Select tbni = 1 For i = 1 To 10 ActiveCell.Value = "tbUnit" & tbni & ".Value" ActiveCell.Offset(1, 0).Activate tbni = tbni + 1 Next I think the answer is somewhere in the syntax. Can anybody help please? Regards Michael Beckinsale |
Forms - Loop thru text boxes
Tom,
I was afraid that you were going to say that ! So l would have to call a procedure to sum all the tbUnit1 to tbUnit2 textboxes in each of tbUnit1x_Change events? Seems a bit of an overkill to me but again many thanks for your help Regards Michael "Tom Ogilvy" wrote in message ... You would have to trap the entry and perform the calculation. There is not way to link a sum formula to it. Even if you did it with cell links, the sum formula would get overwritten. You can use a technique documented by John Walkenbach to handle multiple controls with a single event by using a class module. Although written for command buttons, it works for textboxes as well. The only constraint is that they must be events native to the control (such as click, change, keydown, etc) rather than those provided by the containter (such as Enter, BeforeUpdate, Exit, etc) http://www.j-walk.com/ss/excel/tips/tip44.htm Handle Multiple UserForm Buttons With One Subroutine If you have already created individual events for each textbox, you could have them call a common procedure to do the calculation. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, Many thanks. Works fine. Another quick question re the same form - if you dont mind When initializing the form l add up the values in the textboxes named tbUnit1 to tbUnit20 and put the result in tbUnittotal. However if the user 'edits' a textbox the revised total does not show. Is there a way to show the correct totals dynamically ? Regards Michael beckinsale "Tom Ogilvy" wrote in message ... For i = 1 To 10 ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" & i).Value Next -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have a userform with some 100 text boxes on it. On initialization l apply the values to the text boxes using this code (extract only) Private Sub UserForm_Initialize() Sheets("List_CellsLids").Select Range("N2").Select With frmAccom tbUnit1.Text = ActiveCell.Value tbUnit2.Text = ActiveCell.Offset(1, 0).Value tbUnit3.Text = ActiveCell.Offset(2, 0).Value tbUnit4.Text = ActiveCell.Offset(3, 0).Value tbUnit5.Text = ActiveCell.Offset(4, 0).Value tbUnit6.Text = ActiveCell.Offset(5, 0).Value tbUnit7.Text = ActiveCell.Offset(6, 0).Value tbUnit8.Text = ActiveCell.Offset(7, 0).Value tbUnit9.Text = ActiveCell.Offset(8, 0).Value tbUnit10.Text = ActiveCell.Offset(9, 0).Value End With End Sub On 'Enter' l would normally enter the following code (extract only) so that if the user 'edits' a value it is written to the appropriate cells ActiveCell.Value = tbUnit1.Text ActiveCell.Offset(1, 0).Value = tbUnit2.Text ActiveCell.Offset(2, 0).Value = tbUnit3.Text ActiveCell.Offset(3, 0).Value = tbUnit4.Text ActiveCell.Offset(4, 0).Value = tbUnit5.Text ActiveCell.Offset(5, 0).Value = tbUnit6.Text ActiveCell.Offset(6, 0).Value = tbUnit7.Text ActiveCell.Offset(7, 0).Value = tbUnit8.Text ActiveCell.Offset(8, 0).Value = tbUnit9.Text ActiveCell.Offset(9, 0).Value = tbUnit10.Text However because l have to do this for so many text boxes and l have several more to write l was hoping to apply the values using a vba / loop code something like the following Dim tbni As Integer Private Sub cmdEnter_Click() 'Sheets("List_CellsLids").Select 'Range("N2").Select tbni = 1 For i = 1 To 10 ActiveCell.Value = "tbUnit" & tbni & ".Value" ActiveCell.Offset(1, 0).Activate tbni = tbni + 1 Next I think the answer is somewhere in the syntax. Can anybody help please? Regards Michael Beckinsale |
Forms - Loop thru text boxes
That is one solution I suggested. The other was the John Walkenbach
approach. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, I was afraid that you were going to say that ! So l would have to call a procedure to sum all the tbUnit1 to tbUnit2 textboxes in each of tbUnit1x_Change events? Seems a bit of an overkill to me but again many thanks for your help Regards Michael "Tom Ogilvy" wrote in message ... You would have to trap the entry and perform the calculation. There is not way to link a sum formula to it. Even if you did it with cell links, the sum formula would get overwritten. You can use a technique documented by John Walkenbach to handle multiple controls with a single event by using a class module. Although written for command buttons, it works for textboxes as well. The only constraint is that they must be events native to the control (such as click, change, keydown, etc) rather than those provided by the containter (such as Enter, BeforeUpdate, Exit, etc) http://www.j-walk.com/ss/excel/tips/tip44.htm Handle Multiple UserForm Buttons With One Subroutine If you have already created individual events for each textbox, you could have them call a common procedure to do the calculation. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, Many thanks. Works fine. Another quick question re the same form - if you dont mind When initializing the form l add up the values in the textboxes named tbUnit1 to tbUnit20 and put the result in tbUnittotal. However if the user 'edits' a textbox the revised total does not show. Is there a way to show the correct totals dynamically ? Regards Michael beckinsale "Tom Ogilvy" wrote in message ... For i = 1 To 10 ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" & i).Value Next -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have a userform with some 100 text boxes on it. On initialization l apply the values to the text boxes using this code (extract only) Private Sub UserForm_Initialize() Sheets("List_CellsLids").Select Range("N2").Select With frmAccom tbUnit1.Text = ActiveCell.Value tbUnit2.Text = ActiveCell.Offset(1, 0).Value tbUnit3.Text = ActiveCell.Offset(2, 0).Value tbUnit4.Text = ActiveCell.Offset(3, 0).Value tbUnit5.Text = ActiveCell.Offset(4, 0).Value tbUnit6.Text = ActiveCell.Offset(5, 0).Value tbUnit7.Text = ActiveCell.Offset(6, 0).Value tbUnit8.Text = ActiveCell.Offset(7, 0).Value tbUnit9.Text = ActiveCell.Offset(8, 0).Value tbUnit10.Text = ActiveCell.Offset(9, 0).Value End With End Sub On 'Enter' l would normally enter the following code (extract only) so that if the user 'edits' a value it is written to the appropriate cells ActiveCell.Value = tbUnit1.Text ActiveCell.Offset(1, 0).Value = tbUnit2.Text ActiveCell.Offset(2, 0).Value = tbUnit3.Text ActiveCell.Offset(3, 0).Value = tbUnit4.Text ActiveCell.Offset(4, 0).Value = tbUnit5.Text ActiveCell.Offset(5, 0).Value = tbUnit6.Text ActiveCell.Offset(6, 0).Value = tbUnit7.Text ActiveCell.Offset(7, 0).Value = tbUnit8.Text ActiveCell.Offset(8, 0).Value = tbUnit9.Text ActiveCell.Offset(9, 0).Value = tbUnit10.Text However because l have to do this for so many text boxes and l have several more to write l was hoping to apply the values using a vba / loop code something like the following Dim tbni As Integer Private Sub cmdEnter_Click() 'Sheets("List_CellsLids").Select 'Range("N2").Select tbni = 1 For i = 1 To 10 ActiveCell.Value = "tbUnit" & tbni & ".Value" ActiveCell.Offset(1, 0).Activate tbni = tbni + 1 Next I think the answer is somewhere in the syntax. Can anybody help please? Regards Michael Beckinsale |
Forms - Loop thru text boxes
I am looping through all the controls on a form and testing for "TextBox". If
true, I am adding a cell value to the textbox. However, I cannot make it loop through the controls in the proper order. It seems to process them in the order they were created in. changing the tab order does not help. Is there some way that I could arrange the order that the controls are in? "Tom Ogilvy" wrote: That is one solution I suggested. The other was the John Walkenbach approach. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, I was afraid that you were going to say that ! So l would have to call a procedure to sum all the tbUnit1 to tbUnit2 textboxes in each of tbUnit1x_Change events? Seems a bit of an overkill to me but again many thanks for your help Regards Michael "Tom Ogilvy" wrote in message ... You would have to trap the entry and perform the calculation. There is not way to link a sum formula to it. Even if you did it with cell links, the sum formula would get overwritten. You can use a technique documented by John Walkenbach to handle multiple controls with a single event by using a class module. Although written for command buttons, it works for textboxes as well. The only constraint is that they must be events native to the control (such as click, change, keydown, etc) rather than those provided by the containter (such as Enter, BeforeUpdate, Exit, etc) http://www.j-walk.com/ss/excel/tips/tip44.htm Handle Multiple UserForm Buttons With One Subroutine If you have already created individual events for each textbox, you could have them call a common procedure to do the calculation. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, Many thanks. Works fine. Another quick question re the same form - if you dont mind When initializing the form l add up the values in the textboxes named tbUnit1 to tbUnit20 and put the result in tbUnittotal. However if the user 'edits' a textbox the revised total does not show. Is there a way to show the correct totals dynamically ? Regards Michael beckinsale "Tom Ogilvy" wrote in message ... For i = 1 To 10 ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" & i).Value Next -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have a userform with some 100 text boxes on it. On initialization l apply the values to the text boxes using this code (extract only) Private Sub UserForm_Initialize() Sheets("List_CellsLids").Select Range("N2").Select With frmAccom tbUnit1.Text = ActiveCell.Value tbUnit2.Text = ActiveCell.Offset(1, 0).Value tbUnit3.Text = ActiveCell.Offset(2, 0).Value tbUnit4.Text = ActiveCell.Offset(3, 0).Value tbUnit5.Text = ActiveCell.Offset(4, 0).Value tbUnit6.Text = ActiveCell.Offset(5, 0).Value tbUnit7.Text = ActiveCell.Offset(6, 0).Value tbUnit8.Text = ActiveCell.Offset(7, 0).Value tbUnit9.Text = ActiveCell.Offset(8, 0).Value tbUnit10.Text = ActiveCell.Offset(9, 0).Value End With End Sub On 'Enter' l would normally enter the following code (extract only) so that if the user 'edits' a value it is written to the appropriate cells ActiveCell.Value = tbUnit1.Text ActiveCell.Offset(1, 0).Value = tbUnit2.Text ActiveCell.Offset(2, 0).Value = tbUnit3.Text ActiveCell.Offset(3, 0).Value = tbUnit4.Text ActiveCell.Offset(4, 0).Value = tbUnit5.Text ActiveCell.Offset(5, 0).Value = tbUnit6.Text ActiveCell.Offset(6, 0).Value = tbUnit7.Text ActiveCell.Offset(7, 0).Value = tbUnit8.Text ActiveCell.Offset(8, 0).Value = tbUnit9.Text ActiveCell.Offset(9, 0).Value = tbUnit10.Text However because l have to do this for so many text boxes and l have several more to write l was hoping to apply the values using a vba / loop code something like the following Dim tbni As Integer Private Sub cmdEnter_Click() 'Sheets("List_CellsLids").Select 'Range("N2").Select tbni = 1 For i = 1 To 10 ActiveCell.Value = "tbUnit" & tbni & ".Value" ActiveCell.Offset(1, 0).Activate tbni = tbni + 1 Next I think the answer is somewhere in the syntax. Can anybody help please? Regards Michael Beckinsale |
Forms - Loop thru text boxes
Why not rename them to reflect the order then do
Assuming "form" means Userform Dim tbox as MsForms.TextBox for i = 1 to 10 tbox = userform1.Controls("Textbox" & i) tbox.Value = Worksheets("Sheet1").Cells(i,1).Value Next -- Regards, Tom Ogilvy "Patrick" wrote in message ... I am looping through all the controls on a form and testing for "TextBox". If true, I am adding a cell value to the textbox. However, I cannot make it loop through the controls in the proper order. It seems to process them in the order they were created in. changing the tab order does not help. Is there some way that I could arrange the order that the controls are in? "Tom Ogilvy" wrote: That is one solution I suggested. The other was the John Walkenbach approach. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, I was afraid that you were going to say that ! So l would have to call a procedure to sum all the tbUnit1 to tbUnit2 textboxes in each of tbUnit1x_Change events? Seems a bit of an overkill to me but again many thanks for your help Regards Michael "Tom Ogilvy" wrote in message ... You would have to trap the entry and perform the calculation. There is not way to link a sum formula to it. Even if you did it with cell links, the sum formula would get overwritten. You can use a technique documented by John Walkenbach to handle multiple controls with a single event by using a class module. Although written for command buttons, it works for textboxes as well. The only constraint is that they must be events native to the control (such as click, change, keydown, etc) rather than those provided by the containter (such as Enter, BeforeUpdate, Exit, etc) http://www.j-walk.com/ss/excel/tips/tip44.htm Handle Multiple UserForm Buttons With One Subroutine If you have already created individual events for each textbox, you could have them call a common procedure to do the calculation. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, Many thanks. Works fine. Another quick question re the same form - if you dont mind When initializing the form l add up the values in the textboxes named tbUnit1 to tbUnit20 and put the result in tbUnittotal. However if the user 'edits' a textbox the revised total does not show. Is there a way to show the correct totals dynamically ? Regards Michael beckinsale "Tom Ogilvy" wrote in message ... For i = 1 To 10 ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" & i).Value Next -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have a userform with some 100 text boxes on it. On initialization l apply the values to the text boxes using this code (extract only) Private Sub UserForm_Initialize() Sheets("List_CellsLids").Select Range("N2").Select With frmAccom tbUnit1.Text = ActiveCell.Value tbUnit2.Text = ActiveCell.Offset(1, 0).Value tbUnit3.Text = ActiveCell.Offset(2, 0).Value tbUnit4.Text = ActiveCell.Offset(3, 0).Value tbUnit5.Text = ActiveCell.Offset(4, 0).Value tbUnit6.Text = ActiveCell.Offset(5, 0).Value tbUnit7.Text = ActiveCell.Offset(6, 0).Value tbUnit8.Text = ActiveCell.Offset(7, 0).Value tbUnit9.Text = ActiveCell.Offset(8, 0).Value tbUnit10.Text = ActiveCell.Offset(9, 0).Value End With End Sub On 'Enter' l would normally enter the following code (extract only) so that if the user 'edits' a value it is written to the appropriate cells ActiveCell.Value = tbUnit1.Text ActiveCell.Offset(1, 0).Value = tbUnit2.Text ActiveCell.Offset(2, 0).Value = tbUnit3.Text ActiveCell.Offset(3, 0).Value = tbUnit4.Text ActiveCell.Offset(4, 0).Value = tbUnit5.Text ActiveCell.Offset(5, 0).Value = tbUnit6.Text ActiveCell.Offset(6, 0).Value = tbUnit7.Text ActiveCell.Offset(7, 0).Value = tbUnit8.Text ActiveCell.Offset(8, 0).Value = tbUnit9.Text ActiveCell.Offset(9, 0).Value = tbUnit10.Text However because l have to do this for so many text boxes and l have several more to write l was hoping to apply the values using a vba / loop code something like the following Dim tbni As Integer Private Sub cmdEnter_Click() 'Sheets("List_CellsLids").Select 'Range("N2").Select tbni = 1 For i = 1 To 10 ActiveCell.Value = "tbUnit" & tbni & ".Value" ActiveCell.Offset(1, 0).Activate tbni = tbni + 1 Next I think the answer is somewhere in the syntax. Can anybody help please? Regards Michael Beckinsale |
Forms - Loop thru text boxes
I end-up having so many textboxes getting used in so many different
subroutines that I get way too confused unless I give them descriptive names. I did find a solution, however. Sub Test2() Dim ob As String For i = 2 To 7 For Each ob In UserForm1.Controls With ob If TypeName(Control) = "TextBox" Then ob.Value = Cells(i, ob.TabIndex + 1) End If End With Next UserForm1.Show Next i End Sub Since I set the form tab indexes to match the columns on the worksheet this works very well. "Tom Ogilvy" wrote: Why not rename them to reflect the order then do Assuming "form" means Userform Dim tbox as MsForms.TextBox for i = 1 to 10 tbox = userform1.Controls("Textbox" & i) tbox.Value = Worksheets("Sheet1").Cells(i,1).Value Next -- Regards, Tom Ogilvy "Patrick" wrote in message ... I am looping through all the controls on a form and testing for "TextBox". If true, I am adding a cell value to the textbox. However, I cannot make it loop through the controls in the proper order. It seems to process them in the order they were created in. changing the tab order does not help. Is there some way that I could arrange the order that the controls are in? "Tom Ogilvy" wrote: That is one solution I suggested. The other was the John Walkenbach approach. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, I was afraid that you were going to say that ! So l would have to call a procedure to sum all the tbUnit1 to tbUnit2 textboxes in each of tbUnit1x_Change events? Seems a bit of an overkill to me but again many thanks for your help Regards Michael "Tom Ogilvy" wrote in message ... You would have to trap the entry and perform the calculation. There is not way to link a sum formula to it. Even if you did it with cell links, the sum formula would get overwritten. You can use a technique documented by John Walkenbach to handle multiple controls with a single event by using a class module. Although written for command buttons, it works for textboxes as well. The only constraint is that they must be events native to the control (such as click, change, keydown, etc) rather than those provided by the containter (such as Enter, BeforeUpdate, Exit, etc) http://www.j-walk.com/ss/excel/tips/tip44.htm Handle Multiple UserForm Buttons With One Subroutine If you have already created individual events for each textbox, you could have them call a common procedure to do the calculation. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, Many thanks. Works fine. Another quick question re the same form - if you dont mind When initializing the form l add up the values in the textboxes named tbUnit1 to tbUnit20 and put the result in tbUnittotal. However if the user 'edits' a textbox the revised total does not show. Is there a way to show the correct totals dynamically ? Regards Michael beckinsale "Tom Ogilvy" wrote in message ... For i = 1 To 10 ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" & i).Value Next -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have a userform with some 100 text boxes on it. On initialization l apply the values to the text boxes using this code (extract only) Private Sub UserForm_Initialize() Sheets("List_CellsLids").Select Range("N2").Select With frmAccom tbUnit1.Text = ActiveCell.Value tbUnit2.Text = ActiveCell.Offset(1, 0).Value tbUnit3.Text = ActiveCell.Offset(2, 0).Value tbUnit4.Text = ActiveCell.Offset(3, 0).Value tbUnit5.Text = ActiveCell.Offset(4, 0).Value tbUnit6.Text = ActiveCell.Offset(5, 0).Value tbUnit7.Text = ActiveCell.Offset(6, 0).Value tbUnit8.Text = ActiveCell.Offset(7, 0).Value tbUnit9.Text = ActiveCell.Offset(8, 0).Value tbUnit10.Text = ActiveCell.Offset(9, 0).Value End With End Sub On 'Enter' l would normally enter the following code (extract only) so that if the user 'edits' a value it is written to the appropriate cells ActiveCell.Value = tbUnit1.Text ActiveCell.Offset(1, 0).Value = tbUnit2.Text ActiveCell.Offset(2, 0).Value = tbUnit3.Text ActiveCell.Offset(3, 0).Value = tbUnit4.Text ActiveCell.Offset(4, 0).Value = tbUnit5.Text ActiveCell.Offset(5, 0).Value = tbUnit6.Text ActiveCell.Offset(6, 0).Value = tbUnit7.Text ActiveCell.Offset(7, 0).Value = tbUnit8.Text ActiveCell.Offset(8, 0).Value = tbUnit9.Text ActiveCell.Offset(9, 0).Value = tbUnit10.Text However because l have to do this for so many text boxes and l have several more to write l was hoping to apply the values using a vba / loop code something like the following Dim tbni As Integer Private Sub cmdEnter_Click() 'Sheets("List_CellsLids").Select 'Range("N2").Select tbni = 1 For i = 1 To 10 ActiveCell.Value = "tbUnit" & tbni & ".Value" ActiveCell.Offset(1, 0).Activate tbni = tbni + 1 Next I think the answer is somewhere in the syntax. Can anybody help please? Regards Michael Beckinsale |
Forms - Loop thru text boxes
Oops... sorry... ob is NOT a string...
Sub Test2() Dim ob As Object 'or variant For i = 2 To 7 For Each ob In frm1.Controls With ob If TypeName(Control) = "TextBox" Then ob.Value = Cells(i, ob.TabIndex + 1) End If End With Next frm1.Show Next i End Sub "Patrick" wrote: I end-up having so many textboxes getting used in so many different subroutines that I get way too confused unless I give them descriptive names. I did find a solution, however. Sub Test2() Dim ob As String For i = 2 To 7 For Each ob In UserForm1.Controls With ob If TypeName(Control) = "TextBox" Then ob.Value = Cells(i, ob.TabIndex + 1) End If End With Next UserForm1.Show Next i End Sub Since I set the form tab indexes to match the columns on the worksheet this works very well. "Tom Ogilvy" wrote: Why not rename them to reflect the order then do Assuming "form" means Userform Dim tbox as MsForms.TextBox for i = 1 to 10 tbox = userform1.Controls("Textbox" & i) tbox.Value = Worksheets("Sheet1").Cells(i,1).Value Next -- Regards, Tom Ogilvy "Patrick" wrote in message ... I am looping through all the controls on a form and testing for "TextBox". If true, I am adding a cell value to the textbox. However, I cannot make it loop through the controls in the proper order. It seems to process them in the order they were created in. changing the tab order does not help. Is there some way that I could arrange the order that the controls are in? "Tom Ogilvy" wrote: That is one solution I suggested. The other was the John Walkenbach approach. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, I was afraid that you were going to say that ! So l would have to call a procedure to sum all the tbUnit1 to tbUnit2 textboxes in each of tbUnit1x_Change events? Seems a bit of an overkill to me but again many thanks for your help Regards Michael "Tom Ogilvy" wrote in message ... You would have to trap the entry and perform the calculation. There is not way to link a sum formula to it. Even if you did it with cell links, the sum formula would get overwritten. You can use a technique documented by John Walkenbach to handle multiple controls with a single event by using a class module. Although written for command buttons, it works for textboxes as well. The only constraint is that they must be events native to the control (such as click, change, keydown, etc) rather than those provided by the containter (such as Enter, BeforeUpdate, Exit, etc) http://www.j-walk.com/ss/excel/tips/tip44.htm Handle Multiple UserForm Buttons With One Subroutine If you have already created individual events for each textbox, you could have them call a common procedure to do the calculation. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, Many thanks. Works fine. Another quick question re the same form - if you dont mind When initializing the form l add up the values in the textboxes named tbUnit1 to tbUnit20 and put the result in tbUnittotal. However if the user 'edits' a textbox the revised total does not show. Is there a way to show the correct totals dynamically ? Regards Michael beckinsale "Tom Ogilvy" wrote in message ... For i = 1 To 10 ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" & i).Value Next -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have a userform with some 100 text boxes on it. On initialization l apply the values to the text boxes using this code (extract only) Private Sub UserForm_Initialize() Sheets("List_CellsLids").Select Range("N2").Select With frmAccom tbUnit1.Text = ActiveCell.Value tbUnit2.Text = ActiveCell.Offset(1, 0).Value tbUnit3.Text = ActiveCell.Offset(2, 0).Value tbUnit4.Text = ActiveCell.Offset(3, 0).Value tbUnit5.Text = ActiveCell.Offset(4, 0).Value tbUnit6.Text = ActiveCell.Offset(5, 0).Value tbUnit7.Text = ActiveCell.Offset(6, 0).Value tbUnit8.Text = ActiveCell.Offset(7, 0).Value tbUnit9.Text = ActiveCell.Offset(8, 0).Value tbUnit10.Text = ActiveCell.Offset(9, 0).Value End With End Sub On 'Enter' l would normally enter the following code (extract only) so that if the user 'edits' a value it is written to the appropriate cells ActiveCell.Value = tbUnit1.Text ActiveCell.Offset(1, 0).Value = tbUnit2.Text ActiveCell.Offset(2, 0).Value = tbUnit3.Text ActiveCell.Offset(3, 0).Value = tbUnit4.Text ActiveCell.Offset(4, 0).Value = tbUnit5.Text ActiveCell.Offset(5, 0).Value = tbUnit6.Text ActiveCell.Offset(6, 0).Value = tbUnit7.Text ActiveCell.Offset(7, 0).Value = tbUnit8.Text ActiveCell.Offset(8, 0).Value = tbUnit9.Text ActiveCell.Offset(9, 0).Value = tbUnit10.Text However because l have to do this for so many text boxes and l have several more to write l was hoping to apply the values using a vba / loop code something like the following Dim tbni As Integer Private Sub cmdEnter_Click() 'Sheets("List_CellsLids").Select 'Range("N2").Select tbni = 1 For i = 1 To 10 ActiveCell.Value = "tbUnit" & tbni & ".Value" ActiveCell.Offset(1, 0).Activate tbni = tbni + 1 Next I think the answer is somewhere in the syntax. Can anybody help please? Regards Michael Beckinsale |
| All times are GMT +1. The time now is 10:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com