2 quick questions

[phreud]

Hi, my project is coming close to being finished. Just got 2 problems
cant solve:

1)

I have a UserForm with lots of text boxes. Whenever I change the valu
in one of them I'm updating a graph. Now I'm doing
txtWhatever_AfterUpdate() for every text box in the form, then callin
the same update function in every one of them. Shouldn't I be able t
catch a change in any text box?


2)

The worksheet has an equation that depends on several input cell
(connected to my UserForm). I want to test what happens if one of thes
input values vary between 2 integers. I've written a function tha
takes a start and a stop value (say, from 2-7), loops through them all
calculating the result. The method I'm using however is, to say th
least, ugly! I take the range (2-7). Find a free space in th
worksheet, paste every value between 2 and 7 into a column of cells
Then for each value, I calculate the result with the formula, enter th
result in a column one step to the right of the input values. Then
use those 2 columns to make a graph.
Shouldn't I be able to store everything in an array or something, the
in VBA create the graph, so that I don't have to write stuff to th
worksheet?

Any help apprechiated!

/ phreu

 

[Anders Silven]

Hi phreud,

1)
Looks like it's possible to adapt John Walkenbach's Class Module trick to
TextBox/_AfterUpdate:

http://j-walk.com/ss/excel/tips/tip44.htm

HTH
Anders Silven

 

[phreud]

Certainly looks promising. Thanks alot!

Anyone got an answer for number 2? :

 

[Tom Ogilvy]

It may look promising, but afterupdate can not be handled using John's
technique.

I believe Anders is mistaken. A textbox does not have an afterupdate event.
The control object has the afterupdate event. You can't handle container
events using John's Method - at least no one has ever posted a way to do it
that I am aware of. Or maybe Anders can come back and take us all to class.

 

[phreud]

You're right. I tried to adapt John's method to make it work in my case
but couldn't.

However, surely a TextBox has an AfterUpdate event? The code I'm usin
now is:


Code
-------------------

Private Sub txtSomeName1_AfterUpdate()
Update_Chart
End Sub

Private Sub txtSomeName2_AfterUpdate()
Update_Chart
End Sub

Private Sub txtSomeName3_AfterUpdate()
Update_Chart
End Sub

Private Sub txtSomeName4_AfterUpdate()
Update_Chart
End Sub

-------------------


and so on....

This was the reason for my post. It seems there should be a way to ge
around this. I have about 50 text boxes that need to be called lik
this :

 

[Anders Silven]

Tom is right, as always. Please disregard my post.

Or maybe Anders can come back and take us all to class.

Wouldn't dream of it. I can probably teach you nothing.

Best regards,
Anders Silven

P.S.
Johns method "works" with MsForms.TextBox/_Change but the event will be
triggered by every keystroke, so I don't think that is an option (XL2002).
'*****
Public WithEvents TextBoxGroup As MSForms.TextBox

Private Sub TextBoxGroup_Change()
Beep
End Sub
'*****

 

[Tom Ogilvy]

The afterupdate is "inherited" from the controls object. So no, the textbox
itself does not have the AfterUpdate event. If you put the same textbox
control on a worksheet, you will see there is no AfterUpdate event as an
illustration. Another example of "inheritance". On a userform, the
combobox has a rowsource, but on a worksheet it is ListFillRange. This is
because these properties belong to the control and oleobject objects
respectively.