Click Event Not Working. SOLVED

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
I'm having problems with a Click event that's not working.

Here's the scenario:
I have a frame on a MultiPage tab control within a userform.
I also have a frame on another userform.

At run-time a number of check box controls are created within these frames (between 1 and 45 check boxes at present) and a Click event is created within a Class module to handle the for event each box.

The class module is called cChkBoxEvents and the code within is:
Code:
Option Explicit

Public WithEvents chkBox As MSForms.CheckBox

Private Sub chkBox_Click()

    GV.cn.Execute "UPDATE tblTeamManagement " & _
                  "SET tblTeamManagement.fldIgnore = " & chkBox.Value & " " & _
                  "WHERE (([tblTeamManagement]![fldTeamName] = """ & chkBox.Tag & """));" _
                  , , adExecuteNoRecords
    
End Sub

GV.cn points to an Access database. chkBox.Value and chkBox.Tag are all correct and the SQL statement works perfectly.
The Click event doesn't fire though!

The code which adds the events looks like this:
Code:
Option Explicit

Dim chkBoxColct     As New Collection
Dim txtBoxColct     As New Collection

Public Sub PopulateTeamNames(fraDestination As Variant, _
                             strSQL As String)

    Dim rsTeam              As New ADODB.Recordset
    Dim tmpCtrl(4)          As Control
    Dim dltCtrl             As Control
    Dim chkBoxEvent         As cChkBoxEvents
    Dim txtBoxEvent         As cTxtBoxEvents
.
.
.
        Set tmpCtrl(1) = fraDestination.Controls.Add("Forms.CheckBox.1", _
                            "Ignore" & rsTeam.AbsolutePosition)
        tmpCtrl(1) = rsTeam.Fields(1).Value
        With tmpCtrl(1)
            .Left = 171
            .Top = (rsTeam.AbsolutePosition - 2) * 18 + 24
            .Tag = tmpCtrl(0).Value
        End With
        '//Set event property for check box.
        Set chkBoxEvent = New cChkBoxEvents
        Set chkBoxEvent.chkBox = tmpCtrl(1)
        chkBoxColct.Add chkBoxEvent.chkBox
.
.
.
End Sub
The controls are added correctly, the correct value is taken from the database to populate the control. It's just when I went to change the value of the control it doesn't update within the database - putting a breakpoint within the class modules shows it isn't firing.

Now the annoying bit is that I've also got text boxes added in the same manner and these all work - they use a different class module but it's written in the same way to fire on a double-click event.
Both classes are copied from an earlier copy of the spreadsheet - and in the earlier version everything worked fine. I just changed some variable names - anything that was just chk in the first copy is now chkBox

Stepping through the code also show's that the chkBoxEvent is being added to the chkBoxColct collection.

Hopefully I've given enough information for someone to spot what I'm doing wrong - anyone, please help!!!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Finally found my error:

The last line of code reads:
Code:
chkBoxColct.Add chkBoxEvent.chkBox

whereas it should read:
Code:
chkBoxColct.Add chkBoxEvent

And now each checkbox that's added at runtime updates the database (y)
 
Solution

Forum statistics

Threads
1,136,649
Messages
5,676,990
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top