+ Reply to Thread
Results 1 to 7 of 7

Multiple checkboxes, one macro?

  1. #1
    Registered User
    Join Date
    04-29-2004
    Posts
    4

    Red face Multiple checkboxes, one macro?

    Okay,

    So I'm at a loss. I am trying to create a project checklist (why we don't use MS Project is a mystery to me).

    The problem is that I need to put a checkbox for each item (Row) on the sheet, and when that checkbox is clicked I need it to time stamp the neighboring cell. I have no problem doing it one cell at a time. The issue is that using my current method I would have to write a macro for each checkbox (over 500 total).

    I am using the checkbox from the forms toolbar not the control toolbar. Any suggestions?

    Thanks in advance!!!

  2. #2
    Registered User
    Join Date
    03-21-2005
    Location
    UK
    Posts
    19
    May be you could program your macro using a "For each.....Next loop"

    I don't know how is your sheet but let me know if any help is needed for the programming

    S.

  3. #3
    Registered User
    Join Date
    04-29-2004
    Posts
    4
    I guess what I am trying to do is have 500+ checkboxes tied to the same macro. So I want the macro to capture what cell the checkbox that was most recently checked was in.

    Do you think an on_change event for the linked cell would work?

  4. #4
    Damon Longworth
    Guest

    Re: Multiple checkboxes, one macro?

    If your checkboxes are linked to a cell, you should be able to use a
    worksheet change event for the time stamp.

    --
    Damon Longworth

    Don't miss out on the 2005 Excel User Conference
    Sept 16th and 17th
    Stockyards Hotel - Ft. Worth, Texas
    www.ExcelUserConference.com


    "pkohler" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Okay,
    >
    > So I'm at a loss. I am trying to create a project checklist (why we
    > don't use MS Project is a mystery to me).
    >
    > The problem is that I need to put a checkbox for each item (Row) on the
    > sheet, and when that checkbox is clicked I need it to time stamp the
    > neighboring cell. I have no problem doing it one cell at a time. The
    > issue is that using my current method I would have to write a macro for
    > each checkbox (over 500 total).
    >
    > I am using the checkbox from the forms toolbar not the control toolbar.
    > Any suggestions?
    >
    > Thanks in advance!!!
    >
    >
    > --
    > pkohler
    > ------------------------------------------------------------------------
    > pkohler's Profile:
    > http://www.excelforum.com/member.php...fo&userid=8919
    > View this thread: http://www.excelforum.com/showthread...hreadid=383862
    >




  5. #5
    Roman
    Guest

    Re: Multiple checkboxes, one macro?

    What about to use cells in column A as "buttons":

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("a2:a500")) Is Nothing Then 'a2:a500
    - the button cells
    If Cells(ActiveCell.Row, 2).Value = "" Then
    Cells(ActiveCell.Row, 2).Value = Now()
    End If
    End If
    End Sub


  6. #6
    Roman
    Guest

    Re: Multiple checkboxes, one macro?

    You'd better not lose time by finding what is the position of each of
    500 checkboxes. Change column A fon to Wingdings 2 and try this:

    Private Sub Worksheet_SelectionChange(ByVa=ADl Target As Range)
    If Not Intersect(Target, Me.Range("a2:a500")) Is Nothing Then 'a2:a500
    - the button cells
    If Cells(ActiveCell.Row, 2).Value =3D "" Then
    activecell.value =3D "R"
    Cells(ActiveCell.Row, 2).Value =3D Now()=20
    End If=20
    End If=20
    End Sub


  7. #7
    Dave Peterson
    Guest

    Re: Multiple checkboxes, one macro?

    You can use the same macro if you use the checkbox from the forms toolbar:

    Option Explicit
    Sub CBXClick()

    Dim myCBX As CheckBox

    Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
    If myCBX.Value = xlOn Then
    With myCBX.TopLeftCell.Offset(0, 1)
    .Value = Now
    .NumberFormat = "mm/dd/yyyy hh:mm:ss"
    End With

    End If

    End Sub

    ===
    And if you want to add those checkboxes (500 is a lot) via a macro, this may
    help.

    Option Explicit
    Sub addCBX()
    Dim myCBX As CheckBox
    Dim myRng As Range
    Dim myCell As Range

    With ActiveSheet
    .CheckBoxes.Delete 'nice for testing

    Set myRng = .Range("a1:a" & .Cells(.Rows.Count, "C").End(xlUp).Row)

    For Each myCell In myRng.Cells
    With myCell
    Set myCBX = .Parent.CheckBoxes.Add _
    (Top:=.Top, Width:=.Width, _
    Left:=.Left, Height:=.Height)
    With myCBX
    .LinkedCell = myCell.Address(external:=True)
    .Caption = "" 'or whatever you want
    .Name = "CBX_" & myCell.Address(0, 0)
    If myCell.Address = myRng.Cells(1).Address Then
    .OnAction = "CBXClick"
    End If
    End With
    .NumberFormat = ";;;"
    End With
    Next myCell
    End With
    End Sub



    pkohler wrote:
    >
    > Okay,
    >
    > So I'm at a loss. I am trying to create a project checklist (why we
    > don't use MS Project is a mystery to me).
    >
    > The problem is that I need to put a checkbox for each item (Row) on the
    > sheet, and when that checkbox is clicked I need it to time stamp the
    > neighboring cell. I have no problem doing it one cell at a time. The
    > issue is that using my current method I would have to write a macro for
    > each checkbox (over 500 total).
    >
    > I am using the checkbox from the forms toolbar not the control toolbar.
    > Any suggestions?
    >
    > Thanks in advance!!!
    >
    > --
    > pkohler
    > ------------------------------------------------------------------------
    > pkohler's Profile: http://www.excelforum.com/member.php...fo&userid=8919
    > View this thread: http://www.excelforum.com/showthread...hreadid=383862


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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