Wow, that particular code is a bit over my head and I could possibly have
issues debugging it if a problem should arrised in the future from it. I
appreciate your responses. I will use your function idea, remove my
CalculateButton (frees up space, which I need, sweet!), and just call the
function from each Textbox_Change Event.
Thanks for your help!
Ryan
"Bob Phillips" wrote:
> You could try this approach, but it is more setup.
>
> It also fires the summation code after every change in the textbox, not
> after the update, as this method doesn't support AfterUpdate
>
> In the userform
>
> Option Explicit
>
> Dim mcolEvents As Collection
>
> Private Sub UserForm_Initialize()
> Dim cTBEvents As clsUserFormEvents
> Dim ctl As MSForms.Control
>
> Set mcolEvents = New Collection
>
> For Each ctl In Me.Controls
> If TypeName(ctl) = "TextBox" Then
> Set cTBEvents = New clsUserFormEvents
> Set cTBEvents.mTBGroup = ctl
> mcolEvents.Add cTBEvents
> End If
> Next
>
> End Sub
>
> and add a class module called clsUserFormEvents with this code
>
> Option Explicit
>
> Public WithEvents mTBGroup As MSForms.TextBox
>
> Private Sub mTBGroup_Change()
> 'your summation code in here
> End Sub
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "RyanH" <(E-Mail Removed)> wrote in message
> news:F4643BA5-B9EB-4724-B5E9-(E-Mail Removed)...
> > Thanks for replying so quickly. I failed to mention that I have a
> > CommandButton_Click Event that contains all my code for the calculations.
> > I
> > am new to VBA so I could be wrong, but I think your function idea is
> > essentially the same thing, right? Your way may be better because I would
> > not have to have a command button to do the calculations, right?
> >
> > I was trying to avoid having to call the CommandButton_Click Event or
> > Function from each and every TextBox. Is there a simply loop that can be
> > called if any of the TextBoxes are changed on the UserForm?
> >
> > "Bob Phillips" wrote:
> >
> >> Simplest way is to have a simple sub to add them
> >>
> >> Private Function SumValues()
> >> Dim i As Long
> >>
> >> With Me
> >>
> >> .txtSum1.Text = Val(.TextBox1.Text) + Val(.TextBox2.Text)
> >>
> >> .txtSum2.Text = Val(.TextBox1.Text) - Val(.TextBox12.Text) +
> >> Val(.TextBox19.Text)
> >>
> >> .txtSum3.Text = Val(.TextBox1.Text) * Val(.TextBox2.Text)
> >> End With
> >> End Function
> >>
> >> Obviously you would need to change it to the correct sum calulations.
> >>
> >> Then call it from each of the 35 textboxes like so
> >>
> >>
> >> Private Sub TextBox1_AfterUpdate()
> >> Call SumValues
> >> End Sub
> >>
> >> --
> >> HTH
> >>
> >> Bob
> >>
> >> (there's no email, no snail mail, but somewhere should be gmail in my
> >> addy)
> >>
> >> "RyanH" <(E-Mail Removed)> wrote in message
> >> news:AE5F03E3-2AC6-4BDF-8A0A-(E-Mail Removed)...
> >> >I have a Userform with about 35 Textboxes. I have 3 other Textboxes
> >> >that
> >> > show calculation results from those 35 textboxes. I want those 3
> >> > textboxes
> >> > to refresh there results whenever any of the 35 textboxes are changed.
> >> > Instead of putting a Change Event in every single textbox is there a
> >> > shorter,
> >> > quicker way of doing this?
> >>
> >>
> >>
>
>
>
|
Similar Threads
