+ Reply to Thread
Results 1 to 3 of 3

Can I access a checkbox control using a cell reference to get the name?

Hybrid View

  1. #1
    Registered User
    Join Date
    12-05-2006
    Location
    Philadelphia PA
    MS-Off Ver
    Office 365
    Posts
    81

    Can I access a checkbox control using a cell reference to get the name?

    I have a form (frmMain) with 20 Check boxes (probably more later), all with names that are prefixed with "ck". These names, without the ck, are also column headers in row 1 of a worksheet named "Data". For each header I want to test whether the corresponding Check box is checked (value = True) and take action accordingly. And I want to do it in a loop, in this case For ... Next. I devised the following code using a cell reference to construct the name of the checkbox, but it fails with a compile error: Method or data member not found.

    Worksheets("Data").Activate
    Dim CB As CheckBox             'check boxes are on frmMain
    Dim Cntr As Integer
    For Cntr = 1 To 24                'loop thru column headers
       CB = "ck" & Cells(1, Cntr)  'row 1 is column headers
       If frmMain.CB.Value Then	 
    	'do something
       End If
    Next
    Is there a way to access a checkbox (or any other form control), the name of which is constructed using a reference to text in a worksheet cell? Or am I asking for the impossible? Thanks for any advice or words of wisdom.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Can I access a checkbox control using a cell reference to get the name?

    Hello batman,

    You can access your UserForm CheckBoxes by name like this. Change the Range statement to match the cell you are using.
        With Me.Controls("ck" & Range("A1"))
            If .Value = True Then
                ' Code to execute when Checked goes here.
            End If
        End With
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    12-05-2006
    Location
    Philadelphia PA
    MS-Off Ver
    Office 365
    Posts
    81

    Re: Can I access a checkbox control using a cell reference to get the name?

    Thanks Leith, your solution worked like a charm - problem solved.
    I did modify your code slightly in my application to be able to select the header column numerically. Here's the revised code.
    Dim Headers As Range             'col headers that match checkbox names
    Set Headers = Range("Headers_")  'Data!G1:AF1
    For Indx = 1 To 24               'loop thru column headers
       With frmMain.Controls("ck" & Headers(1, Indx))  'build checkbox name
          If .Value = True Then arFields(Indx) = True
       End With
    Next

+ 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. Checkbox using Format Control and copying control values
    By bigapple14 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2014, 09:28 AM
  2. [SOLVED] Forms Control checkbox and code to unlock a cell
    By JimDandy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-21-2013, 04:46 PM
  3. [SOLVED] Excel 2007 : Checkbox in cell -not Form Control
    By bowtiepride in forum Excel General
    Replies: 15
    Last Post: 03-29-2013, 11:22 PM
  4. Form Control Checkbox - multiple cell links?
    By brokenbiscuits in forum Excel General
    Replies: 2
    Last Post: 06-16-2011, 10:57 AM
  5. Access a cell based on CheckBox position
    By franferns in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-18-2009, 09:40 AM

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