Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Dynamically added controls not firing linked class module event handler

  1. #1
    Join Date
    10th April 2012
    Posts
    64

    Dynamically added controls not firing linked class module event handler

    At runtime userform is populated with serise of checkboxes

    VB:
    For Each c In Range("Weapons") 
        Set Weap = MultiPage1.Pages(0).Controls.Add("Forms.CheckBox.1", Visible) 
        Weap.ControlTipText = c.Offset(0, 1) 
        Weap.Top = CtrlTop 
        Weap.Height = 18 
        Weap.Left = 6 
        Weap.Width = 108 
        Weap.WordWrap = False 
        Weap.Locked = False 
        Weap.TextAlign = fmTextAlignLeft 
        Weap.Caption = c.Offset(0, 0) 
        CtrlTop = CtrlTop + CtrlHeight + CtrlGap 
    Next c 
    
    
    I have a classmodule to handel checkbox event change
    VB:
    Option Explicit 
    Public WithEvents CheckBoxEvents As MSForms.CheckBox 
    Property Get CheckBox() As MSForms.CheckBox 
        Set CheckBox = CheckBoxEvents 
    End Property 
    Property Set CheckBox(ChkHd As MSForms.CheckBox) 
    Set CheckBoxEvents = ChkHd 
    End Property 
    Private Sub CheckBoxEvents_Change() 
        With CheckBoxEvents 
            If .Value Then 
                MsgBox "Item picked" 
            End If 
        End With 
    End Sub 
    
    
    With call to class to module
    VB:
    Dim AobjCheckBoxes() As New clsLegCheck 
    Private Sub UserForm_Initialize() 
        Dim intCtlCnt As Integer, objControl As Control 
         
        For Each objControl In Me.Controls 
            If TypeOf objControl Is MSForms.CheckBox Then 
                intCtlCnt = intCtlCnt + 1 
                Redim Preserve AobjCheckBoxes(1 To intCtlCnt) 
                Set AobjCheckBoxes(intCtlCnt).CheckBoxEvents = objControl 
            End If 
        Next objControl 
        Set objControl = Nothing 
    
    
    The userform loads properly, however when a checkbox is clicked (thus changing its value) nothing happens; expecting message box.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    7,891

    Re: Dynamically added controls not firing linked class module event handeler.

    Possibly because the array in the class module only knows about (any) checkboxes that exist when the userform loads.

    If you load a new checkbox, you have to add it to the class.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    23rd April 2007
    Posts
    3,445

    Re: Dynamically added controls not firing linked class module event handeler.

    The .Add is executed after the Initialize event has run.
    Restated, the userform has to be Initialized before a control can be added to it.

    If the loop adding objects to the array is moved to the userform's Activate event, it should work as expected.

    Alternatly,
    Have you considered a multi-select list box instead of creating check-boxes on the fly.
    (? .ListStyle = fmListSyleOption ?)

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    10th April 2012
    Posts
    64

    Re: Dynamically added controls not firing linked class module event handeler.

    I did consider a list box, however there is additional data for each item that the user will want to review before making a selection so I ruled out the list box option as not being able to handle all requirements for the page. The page needs to have check boxes loaded on initialize because customer will be able to add new items or modify existing items in spreadsheet upon ownership of base product. Without code to create userform at initialize the product would break down if spreadsheet is modified.
    I'll give the userform Activate event suggestion a try.
    Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    10th April 2012
    Posts
    64

    Re: Dynamically added controls not firing linked class module event handeler.

    Placing handler loop in Activate event worked beautifully.
    Thank you, Thank you, Thank you!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    1,887

    Re: Dynamically added controls not firing linked class module event handler

    Just FYI, I'd suggest you use Typename(objControl) = "CheckBox", rather than TypeOf, since togglebuttons, optionbuttons and checkboxes all implement the CheckBox interface and would all be picked up by your TypeOf check.
    Rory
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

  7. #7
    Join Date
    10th April 2012
    Posts
    64

    Re: Dynamically added controls not firing linked class module event handler

    Thanks for the tip Rory.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Class Module For UserForm Controls
    By mattj1080 in forum EXCEL HELP
    Replies: 6
    Last Post: December 23rd, 2009, 07:06
  2. Class Module Change Event For Controls
    By prsthlm in forum EXCEL HELP
    Replies: 10
    Last Post: November 22nd, 2006, 00:54
  3. Stop Controls Change Event Firing
    By IF WILSON in forum EXCEL HELP
    Replies: 7
    Last Post: November 6th, 2006, 18:28
  4. Change event on class of comboboxes not firing
    By jmhans in forum EXCEL HELP
    Replies: 2
    Last Post: March 12th, 2005, 11:35

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno