More Checkboxes on Worksheets

In Adding ActiveX Controls, I described how add a bunch of checkboxes to a sheet. James has a slightly different requirement dealing with checkboxes. He has a list of files each with a checkbox next to it. He wants to put checks next to those files to open, then click a button to open them.

Here’s the initial setup with some test files.

Chkbox1

In the buttons click event, I loop through all the OLEObjects on the sheet. If the object is a CheckBox, I open the file using the FollowHyperlink method. All my test files are in the same directory, so I declare a variable to hold the path. Whatever the case, you need to build a string that represents the full path to the file and pass that to FollowHyperlink.

Private Sub CommandButton1_Click()

    Dim ctl As OLEObject
    Dim sPath As String
    
    sPath = “C:Documents and SettingsDickMy DocumentsTester”
    
    For Each ctl In Sheet1.OLEObjects
        If TypeName(ctl.Object) = “CheckBox” Then
            If ctl.Object.Value = True Then
                ThisWorkbook.FollowHyperlink _
                    sPath & ctl.TopLeftCell.Offset(0, 1).Value
            End If
        End If
    Next ctl
        
End Sub

If you know all of your files in the list will be Excel files, you could just as easily use Workbooks.Open instead of FollowHyperlink. Also, never leave the names of your controls as the default name, e.g. CommandButton1. Do as I say, not as I do.

Posted in Uncategorized

4 thoughts on “More Checkboxes on Worksheets

  1. For an Excel application you have written, do you ever find yourself using something like

    ThisWorkbook.VBProject.VBComponents(“MyModule”).CodeModule

    parsing the lines in the module and using CallByName, just to run a procedure? Nope, neither do I. I usually already know the names of the procedures in my application :-)

    By the same token, I’ve always found code such as

    For Each ctl In Sheet1.OLEObjects

    to be a little, well, odd to say the least. It’s the code equivalent of short term memory loss: ‘What, those controls I put on the worksheet that time? You actually want to use them? Give me a moment. Where did I put them? Remind me, what type of controls were they?’

    Looping through a collection provided by the container object, looking for a match by name or type name strikes me as relying on being able to exploit a series of coincidences e.g. ‘I know the ones I want, I’ll jet nip in and get them. I’ll know one when I see one because they all have names beginning with ‘txtAmnesia_’.

    Because I adopt a OOP approach, if I put a control on a worksheet and I know I might need to refer to it later, it becomes a member of my application. So if It’s an entity in its own right and fairly non-dynamic, I give it a name, otherwise it gets associated with a collection class using a meaningful key. If the workbook is saved or closed I can persist this meta for later use e.g. when the workbook is re-opened.

    Allowing for e.g. the user hitting the VCR stop button in the VBE, I may have a ‘failover’ procedure to query the OLEObjects collection in case my objects get wiped but I don’t use this approach every time I need to access a member of my application.

    Jamie.

    –

  2. I need to get the checkbox names and derive the checkbox number (checkBox10 and 10, etc)
    Here is what I tried

    Private Sub TestCheckBoxes() ‘ CommandButton1_Click()

    Dim ctl As OLEObject
    Dim chkName As String
    Dim chkNum As Integer

    For Each ctl In Sheet1.OLEObjects
    If TypeName(ctl.Object) = “CheckBox” Then
    MsgBox “Got checkbox”
    chkName = ctl.Object.Name
    chkNum = Mid(chkName, 9)
    temp = MsgBox(“CheckBox Name ” & chkName & ” Number ” & chkNum, vbOKCancel)
    If temp = 2 Then Exit Sub
    End If
    Next ctl

    End Sub

    This does not work, I have 1 checkboxes…

    Any help appreciated, I could send more code if so needed

  3. I am looping thru a group of Active X CheckBox controls on a worksheet. I want to refer to their name ina Select Case Statement. I am unable to do so how ever. How do I check the name of the checkbox?

    Also, I can not debug.print teh name of the checkbox?

    Dim ctl As OLEObject ‘

    For Each ctl In GUI.OLEObjects ‘

  4. How do I group checkbox’s so when one is checked the others are not. I’ve gone into the properties of each check box and set the needed groups, but by default checkboxs don’t look to the group, how would I make it do this?

    Regards,

    Trevor Stuart


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.