+ Reply to Thread
Results 1 to 3 of 3

Dynamic Checkboxes within a userform - I'm missing something...

  1. #1
    Registered User
    Join Date
    01-19-2015
    Location
    SD
    MS-Off Ver
    2013
    Posts
    9

    Dynamic Checkboxes within a userform - I'm missing something...

    I'm new to VBA so please be gentle when reviewing my code. When I run the code - the checkboxes display as they should - but nothing happens when I click on them. I've been reading just about every internet article on this subject - hopefully what I have is "close" and I'm on the right track. Once I have this test working I can get more specific on what each checkbox does.

    Thanks!

    Here is my CLASS (clsBoxEvent) code:
    Option Explicit

    Public WithEvents ckbEvent1 As MSForms.CheckBox
    Public WithEvents cbEvent1 As MSForms.CommandButton

    Private Sub cbEvent1_click()

    MsgBox "Yes"

    End Sub


    Private Sub ckbEvent1_click()

    MsgBox "YesYes"

    End Sub

    Here is my userform code:

    Option Explicit

    Private Sub UserForm_Initialize()
    Dim cmdB1 As MSForms.CommandButton
    Dim chkB1 As MSForms.CheckBox
    Dim chkBoxColl As New Collection
    Dim cmdBoxColl As New Collection
    Dim chkBoxEvent As clsBoxEvent
    Dim cmdBoxEvent As clsBoxEvent
    Dim cmdB_loc As Integer
    Dim i As Integer
    Dim j As Integer
    Dim x_array(50) As String
    Dim index As Integer
    Dim array_size As Integer
    Dim ws As Worksheet
    Dim rowcnt As Integer
    '===================================================

    index = 1
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Forms" And ws.Name <> "Marks SQL" And ws.Name <> "Recap" And ws.Name <> "FormData" And ws.Name <> "Decommissioned" And ws.Name <> "template" Then
    x_array(index) = ws.Name
    index = index + 1
    End If
    Next ws

    rowcnt = index / 3
    array_size = index
    index = 1
    For j = 1 To 3
    For i = 1 To rowcnt
    If index = array_size Then
    Exit For
    Exit For
    End If
    Set chkB1 = Controls.Add("Forms.CheckBox.1")
    chkB1.Name = "chk" & x_array(index): chkB1.Top = (i * 25) + 15: chkB1.Left = (j * 80) - 50: chkB1.Font = "Arial": chkB1.Caption = x_array(index)
    Set chkBoxEvent = New clsBoxEvent
    Set chkBoxEvent.ckbEvent1 = Me.Controls(chkB1.Name)
    chkBoxColl.Add chkBoxEvent
    index = index + 1
    Next i
    Next j

    cmdB_loc = rowcnt * 33
    Set cmdB1 = Me.Controls.Add("Forms.CommandButton.1")
    cmdB1.Name = "btnContinue"
    cmdB1.Caption = "Continue?"
    cmdB1.Top = cmdB_loc
    cmdB1.Left = 10
    Set cmdBoxEvent = New clsBoxEvent
    Set cmdBoxEvent.cbEvent1 = Me.Controls(cmdB1.Name)
    cmdBoxColl.Add cmdBoxEvent

    Set cmdB1 = Me.Controls.Add("Forms.CommandButton.1")
    cmdB1.Name = "btnCancel"
    cmdB1.Caption = "Cancel"
    cmdB1.Top = cmdB_loc
    cmdB1.Left = 120
    Set cmdBoxEvent = New clsBoxEvent
    Set cmdBoxEvent.cbEvent1 = Me.Controls(cmdB1.Name)
    cmdBoxColl.Add cmdBoxEvent


    End Sub

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,481

    Re: Dynamic Checkboxes within a userform - I'm missing something...

    I don't know what your code is doing, but to get some code running from checkbox1 then,
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-19-2015
    Location
    SD
    MS-Off Ver
    2013
    Posts
    9

    Re: Dynamic Checkboxes within a userform - I'm missing something...

    The code looks at all the worksheet "names" and builds check boxes dynamically with the name of the worksheet in the "caption". Once all the worksheets have been scanned the checkboxes are displayed to the user and they have the option to select which worksheets to work on (modify, delete, etc).

    The displaying of the checkboxes along with their captions works fine - it's just when I test by checking the boxes - nothing happens. In reviewing other articles on this subject I built a class module that contains:

    ==========
    Option Explicit
    Public WithEvents ckbEvent1 As MSForms.CheckBox
    Public WithEvents cbEvent1 As MSForms.CommandButton

    Private Sub cbEvent1_click()
    MsgBox "Yes"
    End Sub


    Private Sub ckbEvent1_click()
    MsgBox "YesYes"
    End Sub
    ============

    One for checkboxes - one for command buttons.

    The meat of the userform code is:

    Set chkB1 = Controls.Add("Forms.CheckBox.1")
    chkB1.Name = "chk_" & index: chkB1.Top = (i * 25) + 15: chkB1.Left = (j * 80) - 50: chkB1.Font = "Arial": chkB1.Caption = x_array(index)
    Set chkBoxEvent = New clsBoxEvent
    Set chkBoxEvent.ckbEvent1 = Me.Controls(chkB1.Name)
    chkBoxColl.Add chkBoxEvent


    ssexercize.jpg

    Here I add the checkbox, modify it's properties. The code highlighed in RED is where something isn't working. When I set a "watch" on the collection chkBoxColl & cmdBoxColl I can see these are being populated with information I would expect to see.

    ssexercize2.jpg

    Thanks for getting back to me. Can anyone see why this bit of code isn't allowing an event to trigger when I check the checkbox? Am I supposed to do something with the collections chkBoxColl & cmdBoxColl to get the events to function??
    Last edited by jcross; 01-20-2015 at 04:03 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. dynamic generated mousemove of checkboxes in userform to update a single label
    By mynameisnfs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-22-2014, 04:18 PM
  2. VBA Userform: Warn user if all checkboxes on a userform are unticked.
    By MrMiyagi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2014, 12:08 AM
  3. [SOLVED] Dynamic Number of Checkboxes
    By Bob1980 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2013, 01:07 PM
  4. Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro
    By krackaberr in forum Excel Programming / VBA / Macros
    Replies: 34
    Last Post: 03-05-2013, 11:12 AM
  5. Creating Dynamic Checkboxes
    By moosenator in forum Excel General
    Replies: 1
    Last Post: 10-02-2012, 09:32 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1