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.
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
sPath & ctl.TopLeftCell.Offset(0, 1).Value
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.