Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple macro for Checkbox info (2003)

    I am trying to create a macro that returns a True/False value in a cell based on a checkbox. I know that the easiest way to do this is to link a checkbox to a cell but I would need to do this about 300 times in 50 existing workbooks. So, if I can set up one macro on a sheet that I add into the 50 workbooks I could save some time. Alternatively, if there is a way to link all of the info from the 50 sheets into one sheet, that would be even better.

    This vba is working for Checkbox 1.. but I don't know how to modify this so that it repeats for 300 checkboxes.

    Private Sub CommandButton1_Click()

    If CheckBox1 = True Then
    Sheets("Sheet1").Range("B2").Value = "TRUE"
    End If

    End Sub

    I'd like to put Checkbox2 value in B3, Checkbox3 value in B4, etc.
    Thanks for any suggestions.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple macro for Checkbox info (2003)

    This code will set the linked cell for 50 check boxes named CheckBox1 through CheckBox50 on a sheet named MySheet

    Dim i As Integer
    For i = 1 To 50
    Worksheets("MySheet").OLEObjects("CheckBox" & i).LinkedCell = "B" & (i + 1)
    Next i

  3. #3
    New Lounger
    Join Date
    May 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple macro for Checkbox info (2003)

    Hans - thanks very much for your help! I was able to reproduce your code and make it work. I was unable to get it to work in my original document and it took me a while to figure out why. Apparently all of the checkboxes in the original were made using the option on the "Forms" toolbar rather than the visual basic toolbar. This is driving me crazy!! Ha. The macro you wrote works great for checkboxes made using VB but not Forms. Any suggestions on how to get around this? Either somehow converting the checkboxes to VB checkboxes or modifying the macro to recognize these other checkboxes? Thanks again for your time. Brian

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple macro for Checkbox info (2003)

    For check boxes from the Forms toolbar, the default name is "Check Box 1" etc.
    These objects are Shapes, and their control properties are contained in the ControlFormat property. You can use the following code:
    <code>
    Dim i As Integer
    For i = 1 To 50
    Worksheets("MySheet").Shapes("Check Box " & i).ControlFormat.LinkedCell = "B" & (i + 1)
    Next i</code>

  5. #5
    New Lounger
    Join Date
    May 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple macro for Checkbox info (2003)

    Works great! Thanks for saving the day!! Brian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •