| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| User Form Text Boxes - Copy format of text boxes | Excel Discussion (Misc queries) | |||
| Boxes in Forms | Excel Discussion (Misc queries) | |||
| forms / text boxes | Excel Programming | |||
| Setting Property Values of Text Boxes within Forms | Excel Programming | |||
| Loop through Labels and Text Boxes within module code | Excel Programming | |||
Hybrid Mode
