LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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.

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Basic question - modules and class modules - what's the difference? Mark Stephens[_3_] Excel Programming 9 May 8th 05 11:48 AM
Class Modules Pavlos Excel Programming 5 January 19th 05 05:31 PM
Class Modules ibeetb Excel Programming 1 January 5th 04 10:04 PM
Class Modules vs Modules Jeff Marshall Excel Programming 2 September 28th 03 07:57 PM
Delete Modules and Userforms Peter Pantus Excel Programming 1 September 26th 03 08:17 PM


All times are GMT +1. The time now is 05:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"