Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addition code for 110 TextBoxes
Todd,
Another option.................. This would be easier if what you wanted to add were already sequentially numbered. If it's possible to rename the TextBoxes from let's say TextBox1 to TB1, TB2, etc. I'm sure this probably isn't feasable if you already have code linked to the TextBoxes, but for future reference..................... You could then use a loop to add them up: Example: Sub TestMe() Dim x As Integer Dim mysum As Integer mysum = 0 On Error Resume Next For x = 1 To 110 mysum = mysum + Val(UserForm1.Controls("TB" & x).Value) Next x On Error GoTo 0 MsgBox mysum End Sub John "Todd Huttenstine" wrote in message ... The below code adds up all the values of the specified TextBoxes. I need this code to run after any value in any of the specified textboxes change in addition to another 88 TextBoxes that I have not listed. Instead of having to put this code in all 110 TextBoxes, is there an easier way to get this code to run? TextBox9.Value TextBox14.Value + TextBox19.Value + TextBox24.Value + TextBox29.Value + TextBox34.Value + _ TextBox39.Value + TextBox44.Value + TextBox49.Value + TextBox54.Value + TextBox59.Value + TextBox64.Value + _ TextBox69.Value + TextBox74.Value + TextBox79.Value + TextBox84.Value + TextBox89.Value + TextBox94.Value + _ TextBox99.Value + TextBox104.Value + TextBox109.Value + TextBox114 Thank you Todd Huttenstine |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addition code for 110 TextBoxes
But what will fire the macro - 110 event macros?
-- Regards, Tom Ogilvy John Wilson wrote in message ... Todd, Another option.................. This would be easier if what you wanted to add were already sequentially numbered. If it's possible to rename the TextBoxes from let's say TextBox1 to TB1, TB2, etc. I'm sure this probably isn't feasable if you already have code linked to the TextBoxes, but for future reference..................... You could then use a loop to add them up: Example: Sub TestMe() Dim x As Integer Dim mysum As Integer mysum = 0 On Error Resume Next For x = 1 To 110 mysum = mysum + Val(UserForm1.Controls("TB" & x).Value) Next x On Error GoTo 0 MsgBox mysum End Sub John "Todd Huttenstine" wrote in message ... The below code adds up all the values of the specified TextBoxes. I need this code to run after any value in any of the specified textboxes change in addition to another 88 TextBoxes that I have not listed. Instead of having to put this code in all 110 TextBoxes, is there an easier way to get this code to run? TextBox9.Value TextBox14.Value + TextBox19.Value + TextBox24.Value + TextBox29.Value + TextBox34.Value + _ TextBox39.Value + TextBox44.Value + TextBox49.Value + TextBox54.Value + TextBox59.Value + TextBox64.Value + _ TextBox69.Value + TextBox74.Value + TextBox79.Value + TextBox84.Value + TextBox89.Value + TextBox94.Value + _ TextBox99.Value + TextBox104.Value + TextBox109.Value + TextBox114 Thank you Todd Huttenstine |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addition code for 110 TextBoxes
Oh that is very helpful. Thanx. Yeah I cannot do that now, but for future
its nice. Todd Huttenstine "John Wilson" wrote in message ... Todd, Another option.................. This would be easier if what you wanted to add were already sequentially numbered. If it's possible to rename the TextBoxes from let's say TextBox1 to TB1, TB2, etc. I'm sure this probably isn't feasable if you already have code linked to the TextBoxes, but for future reference..................... You could then use a loop to add them up: Example: Sub TestMe() Dim x As Integer Dim mysum As Integer mysum = 0 On Error Resume Next For x = 1 To 110 mysum = mysum + Val(UserForm1.Controls("TB" & x).Value) Next x On Error GoTo 0 MsgBox mysum End Sub John "Todd Huttenstine" wrote in message ... The below code adds up all the values of the specified TextBoxes. I need this code to run after any value in any of the specified textboxes change in addition to another 88 TextBoxes that I have not listed. Instead of having to put this code in all 110 TextBoxes, is there an easier way to get this code to run? TextBox9.Value TextBox14.Value + TextBox19.Value + TextBox24.Value + TextBox29.Value + TextBox34.Value + _ TextBox39.Value + TextBox44.Value + TextBox49.Value + TextBox54.Value + TextBox59.Value + TextBox64.Value + _ TextBox69.Value + TextBox74.Value + TextBox79.Value + TextBox84.Value + TextBox89.Value + TextBox94.Value + _ TextBox99.Value + TextBox104.Value + TextBox109.Value + TextBox114 Thank you Todd Huttenstine |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addition code for 110 TextBoxes
Tom,
Seems that I missed that part about Todd wanting the code to fire whenever *any* one of the TextBoxes changed. Yes, you would need 110 event macros. Wouldn't be all that hard to do though if you just called a sub within each of them and the sub did the calculation. I have, in the past, copied and pasted many and then just went along and changed the references on each of them. Gets the job done, but not the best method. Your suggestion to link them all to cells with a sum function would work too, but you'd still have to go back and create the links. Would probably be just as time consuming as the 100 events. Personally, I'd just have a button call the macro when you wanted to do the calculation. John "Tom Ogilvy" wrote in message ... But what will fire the macro - 110 event macros? -- Regards, Tom Ogilvy John Wilson wrote in message ... Todd, Another option.................. This would be easier if what you wanted to add were already sequentially numbered. If it's possible to rename the TextBoxes from let's say TextBox1 to TB1, TB2, etc. I'm sure this probably isn't feasable if you already have code linked to the TextBoxes, but for future reference..................... You could then use a loop to add them up: Example: Sub TestMe() Dim x As Integer Dim mysum As Integer mysum = 0 On Error Resume Next For x = 1 To 110 mysum = mysum + Val(UserForm1.Controls("TB" & x).Value) Next x On Error GoTo 0 MsgBox mysum End Sub John "Todd Huttenstine" wrote in message ... The below code adds up all the values of the specified TextBoxes. I need this code to run after any value in any of the specified textboxes change in addition to another 88 TextBoxes that I have not listed. Instead of having to put this code in all 110 TextBoxes, is there an easier way to get this code to run? TextBox9.Value TextBox14.Value + TextBox19.Value + TextBox24.Value + TextBox29.Value + TextBox34.Value + _ TextBox39.Value + TextBox44.Value + TextBox49.Value + TextBox54.Value + TextBox59.Value + TextBox64.Value + _ TextBox69.Value + TextBox74.Value + TextBox79.Value + TextBox84.Value + TextBox89.Value + TextBox94.Value + _ TextBox99.Value + TextBox104.Value + TextBox109.Value + TextBox114 Thank you Todd Huttenstine |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addition code for 110 TextBoxes
I set the type to MSforms.Textbox. Here is my new code.
MODULE1: Option Explicit Dim Boxes() As New Class1 Sub ShowDialog() 'Buttons to Boxes 'ButtonCount to TextCount 'ButtonGroup to TextGroup Dim TextCount As Integer Dim ctl As Control ' Create the Button objects TextCount = 0 For Each ctl In UserForm1.Controls 'If TypeName(ctl) = "MSforms.Textbox" Then 'If ctl.Name < "OKButton" Then TextCount = TextCount + 1 ReDim Preserve Boxes(1 To TextCount) Set Boxes(TextCount).ButtonGroup = ctl 'End If 'End If Next ctl UserForm1.Show End Sub CLASS1: Public WithEvents ButtonGroup As MSforms.TextBox Private Sub ButtonGroup_Click() MsgBox "Hello from " & ButtonGroup.Name End Sub Now the form pops up but nothing happens when I click any textbox. "Tom Ogilvy" wrote in message ... But what will fire the macro - 110 event macros? -- Regards, Tom Ogilvy John Wilson wrote in message ... Todd, Another option.................. This would be easier if what you wanted to add were already sequentially numbered. If it's possible to rename the TextBoxes from let's say TextBox1 to TB1, TB2, etc. I'm sure this probably isn't feasable if you already have code linked to the TextBoxes, but for future reference..................... You could then use a loop to add them up: Example: Sub TestMe() Dim x As Integer Dim mysum As Integer mysum = 0 On Error Resume Next For x = 1 To 110 mysum = mysum + Val(UserForm1.Controls("TB" & x).Value) Next x On Error GoTo 0 MsgBox mysum End Sub John "Todd Huttenstine" wrote in message ... The below code adds up all the values of the specified TextBoxes. I need this code to run after any value in any of the specified textboxes change in addition to another 88 TextBoxes that I have not listed. Instead of having to put this code in all 110 TextBoxes, is there an easier way to get this code to run? TextBox9.Value TextBox14.Value + TextBox19.Value + TextBox24.Value + TextBox29.Value + TextBox34.Value + _ TextBox39.Value + TextBox44.Value + TextBox49.Value + TextBox54.Value + TextBox59.Value + TextBox64.Value + _ TextBox69.Value + TextBox74.Value + TextBox79.Value + TextBox84.Value + TextBox89.Value + TextBox94.Value + _ TextBox99.Value + TextBox104.Value + TextBox109.Value + TextBox114 Thank you Todd Huttenstine |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
addition error code is 1E+05. What does that mean? | Excel Discussion (Misc queries) | |||
Textboxes | Excel Discussion (Misc queries) | |||
Reference Tab name to two textboxes | Excel Discussion (Misc queries) | |||
tab between several textboxes | Excel Worksheet Functions | |||
Userfrom textboxes | Excel Programming |