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
| 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
| > | > > 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
| > >> | > >> | 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
| > >> | > | > >> | > | 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
| > >> | >
| > >> | >
| > >> | >
| > >>
| > >>
| > >>
| >
| >
