Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
comboboxes, userforms and class modules
I have been reading through the forum history on these topics, and am
beginning to understand how to use a class module to trap events on a userform, but as a pretty raw beginner i am sure there is a lot i am missing. so i hope someone can help me. I have multiple userforms, with multiple comboboxes (between 9 and 33, i think but the specific number shouldn't matter). each userform also has a single textbox, and a single commandbutton. without getting into specifics, the textbox shows the total price of all the selections from the comboboxes, and the commandbutton writes a receipt of all the purchases. so right now i have a procedure called recalc, which takes one argument, which is the userform itself. the code in recalc look likes this: Public Sub recalc(ByVal MyUserForm As Object) Dim customerprice As Integer Dim ctl As Control MyUserForm.TextBox1.Value = 0 customerprice = 0 For Each ctl In MyUserForm.Controls If TypeName(ctl) = "ComboBox" Then customerprice = customerprice + Price(ctl.Tag, (2 + ctl.ListIndex)) End If Next ctl MyUserForm.TextBox1.Value = Round((1.05 * customerprice), 0) End Sub and my code for each "change" event in the comboboxes looks like this: Private Sub ComboBox1_Change() Call recalc(Me) End Sub what i want to do in the class module is something like this: Option Explicit Public WithEvents mCBGroup As msforms.ComboBox Private Sub mCBGroup_Change() Call recalc(Me) End Sub but i understand that the (Me) is not working, because that will only work from the "userform" code module itself. so after all that, the question is how to write the code in the class module so that after the event procedure ALL the comboboxes are grabbed, not just the one that changed. I guess i also understand that there may be a more efficient way to write this code, then looping through all of the comboboxes every time. That worked for me, because it allowed the user to change her mind midstream and change a previous selection. Any and all suggestions are greatly appreciated. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
comboboxes, userforms and class modules
Assuming your control is not in a frame, to refer to the form try .Parent
Public WithEvents mCBGroup As msforms.ComboBox Private Sub mCBGroup_Change() Call recalc(Me) End Sub Private Sub mCBGroup_Change() Call recalc(mCBGroup.Parent) End Sub However, you could probably update directly in the class change event. Assuming you have a Public reference to your class's in a normal module as an array named clsCombos() Private Sub mCBGroup_Change() Dim customerprice As Integer dim i as long For i = lbound(clsCombos) to ubound(clsCombos) With clsCombos(i).mCBGroup customerprice = customerprice + Price(Tag, (2 + .ListIndex) end with next mCBGroup.Parent.TextBox1.Value = Round((1.05 * customerprice), 0) End Sub I havn't tested this and expect something wrong, but hope you get the idea. If the array is public in the Userform then refer to it as With mCBGroup.Parent.clsCombos(i).mCBGroup Regards, Peter T "natanz" wrote in message oups.com... I have been reading through the forum history on these topics, and am beginning to understand how to use a class module to trap events on a userform, but as a pretty raw beginner i am sure there is a lot i am missing. so i hope someone can help me. I have multiple userforms, with multiple comboboxes (between 9 and 33, i think but the specific number shouldn't matter). each userform also has a single textbox, and a single commandbutton. without getting into specifics, the textbox shows the total price of all the selections from the comboboxes, and the commandbutton writes a receipt of all the purchases. so right now i have a procedure called recalc, which takes one argument, which is the userform itself. the code in recalc look likes this: Public Sub recalc(ByVal MyUserForm As Object) Dim customerprice As Integer Dim ctl As Control MyUserForm.TextBox1.Value = 0 customerprice = 0 For Each ctl In MyUserForm.Controls If TypeName(ctl) = "ComboBox" Then customerprice = customerprice + Price(ctl.Tag, (2 + ctl.ListIndex)) End If Next ctl MyUserForm.TextBox1.Value = Round((1.05 * customerprice), 0) End Sub and my code for each "change" event in the comboboxes looks like this: Private Sub ComboBox1_Change() Call recalc(Me) End Sub what i want to do in the class module is something like this: Option Explicit Public WithEvents mCBGroup As msforms.ComboBox Private Sub mCBGroup_Change() Call recalc(Me) End Sub but i understand that the (Me) is not working, because that will only work from the "userform" code module itself. so after all that, the question is how to write the code in the class module so that after the event procedure ALL the comboboxes are grabbed, not just the one that changed. I guess i also understand that there may be a more efficient way to write this code, then looping through all of the comboboxes every time. That worked for me, because it allowed the user to change her mind midstream and change a previous selection. Any and all suggestions are greatly appreciated. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
comboboxes, userforms and class modules
thanks for this help. How would it be different if my comboboxes are
in frames? do comboboxes automatically become the children of frames, just by drawing them that way in design mode? also, can you tell me what the array declaration would look like? dim clsCombos(34) as combobox? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
comboboxes, userforms and class modules
thanks for this help. How would it be different if my comboboxes are
in frames? do comboboxes automatically become the children of frames, just by drawing them that way in design mode? A control can be "in" a frame or merely physically over it. To put a control "in" a frame, frist select the frame then add the control. It's parent is then the frame, grandparent the form. So, to refer to the form of a control in a frame myInFrameControl.Parent.Parent Some reasons to use frames - presentation, grouping sets of OptionButtons (one can be true in each set), to process "each" control in Frame1 also, can you tell me what the array declaration would look like? dim clsCombos(34) as combobox? Public clsCombos(0 to 34) as Class1 ' zero bound by default, 0-33 ? in say the form's initialize or acitvate event i = 0 set ctrl = Me.myFirstCombo Set clsCombos(i) = New Class1 Set clsCombos(i) .cmbo = ctrl i + i + 1 normally in some sort of loop where ctrl is set to the next combobox to add to the array If you have addded your combos sequentially at design time, and know their index numbers, simply loop by index number. Eg you have added 10 controls (index of the first is 0) and then added 35 combos n = 0 for i = 10 to 44 Set clsCombos(n) = New Class1 Set clsCombos(n) .cmbo = me.controls(i) n = n + 1 Next At the top of Class1 Public WithEvents cmbo As MSForms.ComboBox add events from the top right dropdown (you may need to select "cmbo" in the middle dropdown first) Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Basic question - modules and class modules - what's the difference? | Excel Programming | |||
Class Modules | Excel Programming | |||
Class Modules | Excel Programming | |||
Class Modules vs Modules | Excel Programming | |||
Delete Modules and Userforms | Excel Programming |