Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checkbox Madness (Excel 97 SR2)

    I am currently using WingDings in my spreadsheet to create a series of checkboxes. "o" for the empty, and "
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checkbox Madness (Excel 97 SR2)

    The end result here would be, the user clicks on a cell, and the contents of that cell would change from an unchecked box to a checked box, and vise-versa. Meaning the code would check the active cell, if the active cell is "o" it would replace it with "
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Checkbox Madness (Excel 97 SR2)

    Here it a bit of code that does what I think you want.
    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (ActiveCell.Value = "o") Then
    ActiveCell.Value = "b"
    ElseIf (ActiveCell.Value = "b") Then
    ActiveCell.Value = "o"
    End If

    End Sub

    It changes the cell either when you click on it, or when you make it active with the arrow keys.
    </pre>

    Regards
    John



  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checkbox Madness (Excel 97 SR2)

    John: Your code will change every cell on the worksheet.
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Checkbox Madness (Excel 97 SR2)

    John was assuming that you had the cell formatted for WingDings. From the looks of your post (the stuff in quotes is unclear), I assume that you wanted to use the dingbats section of your normal Unicode font. I used Word to Insert | Symbol a square and a black square, then copied them to Excel. Then I wrote a macro to see what the ASCII code was:
    <pre>Sub Decode()
    MsgBox AscW(ActiveCell)
    End Sub</pre>


    From that info I wrote the following:
    <pre>Option Explicit
    Const BLACK_SQUARE = 9632
    Const WHITE_SQUARE = 9633
    '
    Sub Decode()
    MsgBox AscW(ActiveCell)
    End Sub
    '
    Sub blackSquare(r As Range)
    r = ChrW(BLACK_SQUARE)
    End Sub
    '
    Sub whiteSquare(r As Range)
    r = ChrW(WHITE_SQUARE)
    End Sub
    '
    Function isBlack(r As Range)
    If IsEmpty® Then
    isBlack = False
    Else
    isBlack = (r = ChrW(BLACK_SQUARE))
    End If
    End Function
    '
    Function isWhite(r As Range)
    If IsEmpty® Then
    isWhite = False
    Else
    isWhite = (r = ChrW(WHITE_SQUARE))
    End If
    End Function
    '
    Sub checkIt()
    If isBlack(ActiveCell) Then
    whiteSquare ActiveCell
    ElseIf isWhite(ActiveCell) Then
    blackSquare ActiveCell
    End If
    End Sub
    '
    Sub leadBlackSquare(r As Range)
    r = ChrW(BLACK_SQUARE) & Right(r, Len® - 1)
    End Sub
    '
    Sub leadWhiteSquare(r As Range)
    r = ChrW(WHITE_SQUARE) & Right(r, Len® - 1)
    End Sub
    '
    Function isLeadingBlack(r As Range)
    If IsEmpty® Then
    isLeadingBlack = False
    Else
    isLeadingBlack = (AscW® = BLACK_SQUARE)
    End If
    End Function
    '
    Function isLeadingWhite(r As Range)
    If IsEmpty® Then
    isLeadingWhite = False
    Else
    isLeadingWhite = (AscW® = WHITE_SQUARE)
    End If
    End Function
    '
    Sub checkLeading()
    If isLeadingBlack(ActiveCell) Then
    leadWhiteSquare ActiveCell
    ElseIf isLeadingWhite(ActiveCell) Then
    leadBlackSquare ActiveCell
    End If
    End Sub</pre>


    I started with the assumption that you just wanted a box (the CheckIt macro), but then thought that it would be better to check/change the leading character (the checkLeading macro)

    As John indicated, you put this in the Worksheet_SelectionChange event. Just right-click on the sheet-tab, select View Code, and enter the following:
    <pre>Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    checkLeading
    End Sub</pre>


    Final note, notice that I kept the sheet code very short: you cannot easily clean/reuse the sheet code, so eveything possible should be in a code module. Sample is attached. Yours was a good idea, too bad there isn't a checked box.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checkbox Madness (Excel 97 SR2)

    Since Excel does not have a click event, you can't do exactly what you are asking. However, there are a couple of options that get close.

    1- With a little added code, you can use something like John suggested. This would flip the cell contents when you clicked on it, but it would also flip the contents if you tabed into the cell.

    2- A second alternative would be to use the Worksheet Before Double Click event routine and flip the contents when you double click on the cell. If the cell in qiestion is A1, then that code would look something like this:

    <pre>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub
    If (Target.Value = "o") Then
    Target.Value = "
    Legare Coleman

Posting Permissions

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