ExcelBanter - Combobox Change

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Combobox Change_Event (https://www.excelbanter.com/excel-programming/386897-combobox-change_event.html)

Combobox Change_Event

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

Combobox Change_Event

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

--
Jim
"JimRWR" wrote in message
...
| 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

Combobox Change_Event

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" wrote in message
...
| 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

Combobox Change_Event

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" wrote in message
...
| 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" wrote in message
| ...
| | 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
|
|
|

Combobox Change_Event

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" wrote in message
...
| 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" wrote in message
| ...
| | 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
|
|
|

Combobox Change_Event

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" wrote in message
...
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" wrote in message
...
| 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" wrote in message
| ...
| | 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
|
|
|

Combobox Change_Event

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" wrote in message
...
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" wrote in message
...
| 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" wrote in message
| ...
| | 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
|
|
|

Combobox Change_Event

Combobox Change_Event

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

Combobox Change_Event

Jim:

Please disregard my last post. I screwed on my head and looked beyond my
nose and now understand what you were saying. So now I've got the event to
trigger - Thanks so much for your help!

One follow-up question: Now that I can trigger the ctrlnew4_change event
from the class module, how do I reference the corresponding textbox
(ctrlnew6) which contains the corresponding price/ billing rate? In other
words, if at runtime I select "Supervisor" as the ctrlnew4 value, how do I
access and update the ctrlnew6 value? I've tried the following:

1. passing ctrlnew6 as an arguement from the standard module (where it's
created) to the class module (where it's changed)

2. Declaring ctrlnew6 as a public textbox at the top of the standard module
and as an event class at the top of the class module

In both cases, I get errors.

Any thoughts?

Thanks for your help and patience!

Jim

"JimRWR" wrote:

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