Hi.

I'm creating a product order form in VBA 6.3 for Excel 2000 that contains a
combobox for product id and textboxes for unit price, quantity and total.
The form is designed so that the customer can add lines for additional
products. In so doing, I need to be able to:
1. raise the change event of the product id combobox, so that the
corresponding price appears in the unit price textbox
2. raise the change event of the quantity textbox so that the total
calculates (quantity*price)

As instances of these objects are created on the fly by the customer, I
think I have to create class modules to raise and trigger change events.
However, class modules are brand new to me so any help would be greatly
appreciated.

TIA,

JimRWR

I may not be following you completely but if you want to trigger event
handler code just call it.

--
Jim
"JimRWR" <(E-Mail Removed)> wrote in message
news:1A0B9402-C316-435E-9CC7-(E-Mail Removed)...
| Hi.
|
| I'm creating a product order form in VBA 6.3 for Excel 2000 that contains
a
| combobox for product id and textboxes for unit price, quantity and total.
| The form is designed so that the customer can add lines for additional
| products. In so doing, I need to be able to:
| 1. raise the change event of the product id combobox, so that the
| corresponding price appears in the unit price textbox
| 2. raise the change event of the quantity textbox so that the total
| calculates (quantity*price)
|
| As instances of these objects are created on the fly by the customer, I
| think I have to create class modules to raise and trigger change events.
| However, class modules are brand new to me so any help would be greatly
| appreciated.
|
| TIA,
|
| JimRWR

How do you trigger the handling code when the instance of the object is
controlled by the user? Here is what I have written so far for my class
module, clmodAssignPrice, based on VBA help:

Public WithEvents ComboBoxEvent As ComboBox
Public Sub AssignPrice(ByVal ctrldynnew4 As ComboBox)
Dim strProductName As String
Set ComboBoxEvent = ctrldynnew4
strProductName = ctrldynnew4.Value
End Sub

Public Sub ComboBoxEvent_Change(ByVal ctrldynnew4 As ComboBox)
strProductName = ctrldynnew4.Value
End Sub

Thanks,

Jim

"Jim Rech" wrote:

> I may not be following you completely but if you want to trigger event
> handler code just call it.
>
> --
> Jim
> "JimRWR" <(E-Mail Removed)> wrote in message
> news:1A0B9402-C316-435E-9CC7-(E-Mail Removed)...
> | Hi.
> |
> | I'm creating a product order form in VBA 6.3 for Excel 2000 that contains
> a
> | combobox for product id and textboxes for unit price, quantity and total.
> | The form is designed so that the customer can add lines for additional
> | products. In so doing, I need to be able to:
> | 1. raise the change event of the product id combobox, so that the
> | corresponding price appears in the unit price textbox
> | 2. raise the change event of the quantity textbox so that the total
> | calculates (quantity*price)
> |
> | As instances of these objects are created on the fly by the customer, I
> | think I have to create class modules to raise and trigger change events.
> | However, class modules are brand new to me so any help would be greatly
> | appreciated.
> |
> | TIA,
> |
> | JimRWR
>
>
>

Well I guess I was right that I do not follow what you're doing. The
concept "the instance of the object is controlled by the user" doesn't mean
a thing to me. I guess my userform needs have always been simpler. Sorry I
couldn't help.

--
Jim
"JimRWR" <(E-Mail Removed)> wrote in message
news:6871F92B-FEA7-4FCA-A168-(E-Mail Removed)...
| How do you trigger the handling code when the instance of the object is
| controlled by the user? Here is what I have written so far for my class
| module, clmodAssignPrice, based on VBA help:
|
| Public WithEvents ComboBoxEvent As ComboBox
| Public Sub AssignPrice(ByVal ctrldynnew4 As ComboBox)
| Dim strProductName As String
| Set ComboBoxEvent = ctrldynnew4
| strProductName = ctrldynnew4.Value
| End Sub
|
| Public Sub ComboBoxEvent_Change(ByVal ctrldynnew4 As ComboBox)
| strProductName = ctrldynnew4.Value
| End Sub
|
| Thanks,
|
| Jim
|
| "Jim Rech" wrote:
|
| > I may not be following you completely but if you want to trigger event
| > handler code just call it.
| >
| > --
| > Jim
| > "JimRWR" <(E-Mail Removed)> wrote in message
| > news:1A0B9402-C316-435E-9CC7-(E-Mail Removed)...
| > | Hi.
| > |
| > | I'm creating a product order form in VBA 6.3 for Excel 2000 that
contains
| > a
| > | combobox for product id and textboxes for unit price, quantity and
total.
| > | The form is designed so that the customer can add lines for additional
| > | products. In so doing, I need to be able to:
| > | 1. raise the change event of the product id combobox, so that the
| > | corresponding price appears in the unit price textbox
| > | 2. raise the change event of the quantity textbox so that the total
| > | calculates (quantity*price)
| > |
| > | As instances of these objects are created on the fly by the customer,
I
| > | think I have to create class modules to raise and trigger change
events.
| > | However, class modules are brand new to me so any help would be
greatly
| > | appreciated.
| > |
| > | TIA,
| > |
| > | JimRWR
| >
| >
| >

My code is written so that as the user adds new lines to the order form, new
comboxes and text boxes are created, each of which is given a name and index
that corresponds to its position in the form. For example, ctrldynnew4
corresponds to a new 'product name' combobox as the 4th object in that row.
Creating the combobox is easy.

My question is, how do I fire its click_event so that the appropriate price
will appear in a textbox? Right now, if I select a product from the
ctrldynew4 combobox, nothing happens in the price textbox.

Thanks,

Jim
"Jim Rech" wrote:

> Well I guess I was right that I do not follow what you're doing. The
> concept "the instance of the object is controlled by the user" doesn't mean
> a thing to me. I guess my userform needs have always been simpler. Sorry I
> couldn't help.
>
> --
> Jim
> "JimRWR" <(E-Mail Removed)> wrote in message
> news:6871F92B-FEA7-4FCA-A168-(E-Mail Removed)...
> | How do you trigger the handling code when the instance of the object is
> | controlled by the user? Here is what I have written so far for my class
> | module, clmodAssignPrice, based on VBA help:
> |
> | Public WithEvents ComboBoxEvent As ComboBox
> | Public Sub AssignPrice(ByVal ctrldynnew4 As ComboBox)
> | Dim strProductName As String
> | Set ComboBoxEvent = ctrldynnew4
> | strProductName = ctrldynnew4.Value
> | End Sub
> |
> | Public Sub ComboBoxEvent_Change(ByVal ctrldynnew4 As ComboBox)
> | strProductName = ctrldynnew4.Value
> | End Sub
> |
> | Thanks,
> |
> | Jim
> |
> | "Jim Rech" wrote:
> |
> | > I may not be following you completely but if you want to trigger event
> | > handler code just call it.
> | >
> | > --
> | > Jim
> | > "JimRWR" <(E-Mail Removed)> wrote in message
> | > news:1A0B9402-C316-435E-9CC7-(E-Mail Removed)...
> | > | Hi.
> | > |
> | > | I'm creating a product order form in VBA 6.3 for Excel 2000 that
> contains
> | > a
> | > | combobox for product id and textboxes for unit price, quantity and
> total.
> | > | The form is designed so that the customer can add lines for additional
> | > | products. In so doing, I need to be able to:
> | > | 1. raise the change event of the product id combobox, so that the
> | > | corresponding price appears in the unit price textbox
> | > | 2. raise the change event of the quantity textbox so that the total
> | > | calculates (quantity*price)
> | > |
> | > | As instances of these objects are created on the fly by the customer,
> I
> | > | think I have to create class modules to raise and trigger change
> events.
> | > | However, class modules are brand new to me so any help would be
> greatly
> | > | appreciated.
> | > |
> | > | TIA,
> | > |
> | > | JimRWR
> | >
> | >
> | >
>
>
>

>>how do I fire its click_event

Your code shows you want the change event. Wouldn't setting the combobox's
LintIndex fire that?

--
Jim
"JimRWR" <(E-Mail Removed)> wrote in message
news:28860729-D3FE-47DF-9E29-(E-Mail Removed)...
> My code is written so that as the user adds new lines to the order form,
> new
> comboxes and text boxes are created, each of which is given a name and
> index
> that corresponds to its position in the form. For example, ctrldynnew4
> corresponds to a new 'product name' combobox as the 4th object in that
> row.
> Creating the combobox is easy.
>
> My question is, how do I fire its click_event so that the appropriate
> price
> will appear in a textbox? Right now, if I select a product from the
> ctrldynew4 combobox, nothing happens in the price textbox.
>
> Thanks,
>
> Jim
> "Jim Rech" wrote:
>
>> Well I guess I was right that I do not follow what you're doing. The
>> concept "the instance of the object is controlled by the user" doesn't
>> mean
>> a thing to me. I guess my userform needs have always been simpler.
>> Sorry I
>> couldn't help.
>>
>> --
>> Jim
>> "JimRWR" <(E-Mail Removed)> wrote in message
>> news:6871F92B-FEA7-4FCA-A168-(E-Mail Removed)...
>> | How do you trigger the handling code when the instance of the object is
>> | controlled by the user? Here is what I have written so far for my
>> class
>> | module, clmodAssignPrice, based on VBA help:
>> |
>> | Public WithEvents ComboBoxEvent As ComboBox
>> | Public Sub AssignPrice(ByVal ctrldynnew4 As ComboBox)
>> | Dim strProductName As String
>> | Set ComboBoxEvent = ctrldynnew4
>> | strProductName = ctrldynnew4.Value
>> | End Sub
>> |
>> | Public Sub ComboBoxEvent_Change(ByVal ctrldynnew4 As ComboBox)
>> | strProductName = ctrldynnew4.Value
>> | End Sub
>> |
>> | Thanks,
>> |
>> | Jim
>> |
>> | "Jim Rech" wrote:
>> |
>> | > I may not be following you completely but if you want to trigger
>> event
>> | > handler code just call it.
>> | >
>> | > --
>> | > Jim
>> | > "JimRWR" <(E-Mail Removed)> wrote in message
>> | > news:1A0B9402-C316-435E-9CC7-(E-Mail Removed)...
>> | > | Hi.
>> | > |
>> | > | I'm creating a product order form in VBA 6.3 for Excel 2000 that
>> contains
>> | > a
>> | > | combobox for product id and textboxes for unit price, quantity and
>> total.
>> | > | The form is designed so that the customer can add lines for
>> additional
>> | > | products. In so doing, I need to be able to:
>> | > | 1. raise the change event of the product id combobox, so that the
>> | > | corresponding price appears in the unit price textbox
>> | > | 2. raise the change event of the quantity textbox so that the total
>> | > | calculates (quantity*price)
>> | > |
>> | > | As instances of these objects are created on the fly by the
>> customer,
>> I
>> | > | think I have to create class modules to raise and trigger change
>> events.
>> | > | However, class modules are brand new to me so any help would be
>> greatly
>> | > | appreciated.
>> | > |
>> | > | TIA,
>> | > |
>> | > | JimRWR
>> | >
>> | >
>> | >
>>
>>
>>

Having done a bit more research (see the "Capture Events in Dynamic Forms
Control" topic in Office Developer Programming) I can more clearly describe
my problem as follows: I am trying to create runtime comboboxes and then
capture events on these comboboxes.

I have a class module called clModAssignRate. The AddActivity procedure
invokes the clModAssignRate code. The problem is that the AssignRate
procedure inside the class module never fires.

Following are relevant lines of code:

Public WithEvents ctrlnew4 As MSForms.ComboBox

Private Sub AssignRate()
Sheet18.Select
MsgBox ctrlnew4.Value
Range("RATE_PROJECT_TITLE").Select
Selection.Find(what:=ProjectTitle, after:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Activate
Do While ActiveCell.Value = ProjectTitle
If ActiveCell.Offset(0, ofsRateBillingLevel).Value = ctrlnew4.Value
Then
CtrlNew6.Value = ActiveCell.Offset(0, ofsRate).Value
Exit Do
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

'Here is the code that invokes the class module
Public ctrlnew4 As ComboBox
Dim c() As clModAssignRate

Sub AddActivity()
'This procedure creates a new array (line) of controls that the user can
change
'to suit his project needs
....

'Create first Level combo boxes
k = 0
m = 0
strControlName4 = "cbxBillingLevel" & nbr & "4"
ReDim c(1)

Set c(0) = New clModAssignRate
Set c(0).ctrlnew4 = frmProjectCenter.Controls.Add("Forms.ComboBox.1",
strControlName4, True)

Thanks,

Jim


"Jim Rech" wrote:

> >>how do I fire its click_event
>
> Your code shows you want the change event. Wouldn't setting the combobox's
> LintIndex fire that?
>
> --
> Jim
> "JimRWR" <(E-Mail Removed)> wrote in message
> news:28860729-D3FE-47DF-9E29-(E-Mail Removed)...
> > My code is written so that as the user adds new lines to the order form,
> > new
> > comboxes and text boxes are created, each of which is given a name and
> > index
> > that corresponds to its position in the form. For example, ctrldynnew4
> > corresponds to a new 'product name' combobox as the 4th object in that
> > row.
> > Creating the combobox is easy.
> >
> > My question is, how do I fire its click_event so that the appropriate
> > price
> > will appear in a textbox? Right now, if I select a product from the
> > ctrldynew4 combobox, nothing happens in the price textbox.
> >
> > Thanks,
> >
> > Jim
> > "Jim Rech" wrote:
> >
> >> Well I guess I was right that I do not follow what you're doing. The
> >> concept "the instance of the object is controlled by the user" doesn't
> >> mean
> >> a thing to me. I guess my userform needs have always been simpler.
> >> Sorry I
> >> couldn't help.
> >>
> >> --
> >> Jim
> >> "JimRWR" <(E-Mail Removed)> wrote in message
> >> news:6871F92B-FEA7-4FCA-A168-(E-Mail Removed)...
> >> | How do you trigger the handling code when the instance of the object is
> >> | controlled by the user? Here is what I have written so far for my
> >> class
> >> | module, clmodAssignPrice, based on VBA help:
> >> |
> >> | Public WithEvents ComboBoxEvent As ComboBox
> >> | Public Sub AssignPrice(ByVal ctrldynnew4 As ComboBox)
> >> | Dim strProductName As String
> >> | Set ComboBoxEvent = ctrldynnew4
> >> | strProductName = ctrldynnew4.Value
> >> | End Sub
> >> |
> >> | Public Sub ComboBoxEvent_Change(ByVal ctrldynnew4 As ComboBox)
> >> | strProductName = ctrldynnew4.Value
> >> | End Sub
> >> |
> >> | Thanks,
> >> |
> >> | Jim
> >> |
> >> | "Jim Rech" wrote:
> >> |
> >> | > I may not be following you completely but if you want to trigger
> >> event
> >> | > handler code just call it.
> >> | >
> >> | > --
> >> | > Jim
> >> | > "JimRWR" <(E-Mail Removed)> wrote in message
> >> | > news:1A0B9402-C316-435E-9CC7-(E-Mail Removed)...
> >> | > | Hi.
> >> | > |
> >> | > | I'm creating a product order form in VBA 6.3 for Excel 2000 that
> >> contains
> >> | > a
> >> | > | combobox for product id and textboxes for unit price, quantity and
> >> total.
> >> | > | The form is designed so that the customer can add lines for
> >> additional
> >> | > | products. In so doing, I need to be able to:
> >> | > | 1. raise the change event of the product id combobox, so that the
> >> | > | corresponding price appears in the unit price textbox
> >> | > | 2. raise the change event of the quantity textbox so that the total
> >> | > | calculates (quantity*price)
> >> | > |
> >> | > | As instances of these objects are created on the fly by the
> >> customer,
> >> I
> >> | > | think I have to create class modules to raise and trigger change
> >> events.
> >> | > | However, class modules are brand new to me so any help would be
> >> greatly
> >> | > | appreciated.
> >> | > |
> >> | > | TIA,
> >> | > |
> >> | > | JimRWR
> >> | >
> >> | >
> >> | >
> >>
> >>
> >>
>
>

When you create as class as type XXX you have to create handlers for the
events object type XXX recognizes. So after you put this:

Public WithEvents ctrlnew4 As MSForms.ComboBox

at the top of a new class module you next create those handlers by first
selecting "ctrlnew4" from the left drop down at the top of the module window
and then the specific event you want to code from in the right drop down. I
don't see any evidence you're doing that from the code you posted.

--
Jim
"JimRWR" <(E-Mail Removed)> wrote in message
news:5AE399CF-DEC4-4126-83EC-(E-Mail Removed)...
| Having done a bit more research (see the "Capture Events in Dynamic Forms
| Control" topic in Office Developer Programming) I can more clearly
describe
| my problem as follows: I am trying to create runtime comboboxes and then
| capture events on these comboboxes.
|
| I have a class module called clModAssignRate. The AddActivity procedure
| invokes the clModAssignRate code. The problem is that the AssignRate
| procedure inside the class module never fires.
|
| Following are relevant lines of code:
|
| Public WithEvents ctrlnew4 As MSForms.ComboBox
|
| Private Sub AssignRate()
| Sheet18.Select
| MsgBox ctrlnew4.Value
| Range("RATE_PROJECT_TITLE").Select
| Selection.Find(what:=ProjectTitle, after:=ActiveCell, LookIn:= _
| xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=
_
| xlNext, MatchCase:=False).Activate
| Do While ActiveCell.Value = ProjectTitle
| If ActiveCell.Offset(0, ofsRateBillingLevel).Value = ctrlnew4.Value
| Then
| CtrlNew6.Value = ActiveCell.Offset(0, ofsRate).Value
| Exit Do
| End If
| ActiveCell.Offset(1, 0).Select
| Loop
| End Sub
|
| 'Here is the code that invokes the class module
| Public ctrlnew4 As ComboBox
| Dim c() As clModAssignRate
|
| Sub AddActivity()
| 'This procedure creates a new array (line) of controls that the user can
| change
| 'to suit his project needs
| ...
|
| 'Create first Level combo boxes
| k = 0
| m = 0
| strControlName4 = "cbxBillingLevel" & nbr & "4"
| ReDim c(1)
|
| Set c(0) = New clModAssignRate
| Set c(0).ctrlnew4 = frmProjectCenter.Controls.Add("Forms.ComboBox.1",
| strControlName4, True)
|
| Thanks,
|
| Jim
|
|
| "Jim Rech" wrote:
|
| > >>how do I fire its click_event
| >
| > Your code shows you want the change event. Wouldn't setting the
combobox's
| > LintIndex fire that?
| >
| > --
| > Jim
| > "JimRWR" <(E-Mail Removed)> wrote in message
| > news:28860729-D3FE-47DF-9E29-(E-Mail Removed)...
| > > My code is written so that as the user adds new lines to the order
form,
| > > new
| > > comboxes and text boxes are created, each of which is given a name and
| > > index
| > > that corresponds to its position in the form. For example,
ctrldynnew4
| > > corresponds to a new 'product name' combobox as the 4th object in that
| > > row.
| > > Creating the combobox is easy.
| > >
| > > My question is, how do I fire its click_event so that the appropriate
| > > price
| > > will appear in a textbox? Right now, if I select a product from the
| > > ctrldynew4 combobox, nothing happens in the price textbox.
| > >
| > > Thanks,
| > >
| > > Jim
| > > "Jim Rech" wrote:
| > >
| > >> Well I guess I was right that I do not follow what you're doing. The
| > >> concept "the instance of the object is controlled by the user"
doesn't
| > >> mean
| > >> a thing to me. I guess my userform needs have always been simpler.
| > >> Sorry I
| > >> couldn't help.
| > >>
| > >> --
| > >> Jim
| > >> "JimRWR" <(E-Mail Removed)> wrote in message
| > >> news:6871F92B-FEA7-4FCA-A168-(E-Mail Removed)...
| > >> | How do you trigger the handling code when the instance of the
object is
| > >> | controlled by the user? Here is what I have written so far for my
| > >> class
| > >> | module, clmodAssignPrice, based on VBA help:
| > >> |
| > >> | Public WithEvents ComboBoxEvent As ComboBox
| > >> | Public Sub AssignPrice(ByVal ctrldynnew4 As ComboBox)
| > >> | Dim strProductName As String
| > >> | Set ComboBoxEvent = ctrldynnew4
| > >> | strProductName = ctrldynnew4.Value
| > >> | End Sub
| > >> |
| > >> | Public Sub ComboBoxEvent_Change(ByVal ctrldynnew4 As ComboBox)
| > >> | strProductName = ctrldynnew4.Value
| > >> | End Sub
| > >> |
| > >> | Thanks,
| > >> |
| > >> | Jim
| > >> |
| > >> | "Jim Rech" wrote:
| > >> |
| > >> | > I may not be following you completely but if you want to trigger
| > >> event
| > >> | > handler code just call it.
| > >> | >
| > >> | > --
| > >> | > Jim
| > >> | > "JimRWR" <(E-Mail Removed)> wrote in message
| > >> | > news:1A0B9402-C316-435E-9CC7-(E-Mail Removed)...
| > >> | > | Hi.
| > >> | > |
| > >> | > | I'm creating a product order form in VBA 6.3 for Excel 2000
that
| > >> contains
| > >> | > a
| > >> | > | combobox for product id and textboxes for unit price, quantity
and
| > >> total.
| > >> | > | The form is designed so that the customer can add lines for
| > >> additional
| > >> | > | products. In so doing, I need to be able to:
| > >> | > | 1. raise the change event of the product id combobox, so that
the
| > >> | > | corresponding price appears in the unit price textbox
| > >> | > | 2. raise the change event of the quantity textbox so that the
total
| > >> | > | calculates (quantity*price)
| > >> | > |
| > >> | > | As instances of these objects are created on the fly by the
| > >> customer,
| > >> I
| > >> | > | think I have to create class modules to raise and trigger
change
| > >> events.
| > >> | > | However, class modules are brand new to me so any help would be
| > >> greatly
| > >> | > | appreciated.
| > >> | > |
| > >> | > | TIA,
| > >> | > |
| > >> | > | JimRWR
| > >> | >
| > >> | >
| > >> | >
| > >>
| > >>
| > >>
| >
| >

When you create as class as type XXX you have to create handlers for the
events object type XXX recognizes. So after you put this:

Public WithEvents ctrlnew4 As MSForms.ComboBox

at the top of a new class module you next create those handlers by first
selecting "ctrlnew4" from the left drop down at the top of the module window
and then the specific event you want to code from in the right drop down. I
don't see any evidence you're doing that from the code you posted.

--
Jim

Jim:

I put the following line above the module (not the class module), but I keep
getting errors:

Dim ctrlComboBoxHandler As New ComboBoxHandler

Two questions:

1. Why is this bombing
2. Should this go above the class module or the module that is invoking the
class?

Thanks!

Jim

"Jim Rech" wrote:

> When you create as class as type XXX you have to create handlers for the
> events object type XXX recognizes. So after you put this:
>
> Public WithEvents ctrlnew4 As MSForms.ComboBox
>
> at the top of a new class module you next create those handlers by first
> selecting "ctrlnew4" from the left drop down at the top of the module window
> and then the specific event you want to code from in the right drop down. I
> don't see any evidence you're doing that from the code you posted.
>
> --
> Jim
>
>
>