Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Check Mark Macro (Excel XP)

    I used tools|macro|record to place a checkmark into a cell and then attached the code to a button on my spreadsheet. It's not exactly what I wanted and I'm hoping someone will help me clean it up a bit.

    I need the checkmark placed in the current cell with a font size of 12. Easy enough, I guess I just change the "10" to a "12".

    Can the xxxxx=False statements be safely removed from the code?

    Once the checkmark is placed, I need the cell selection to just move down one, NOT go to "T3". This way I can place a series of checkmarks down a column by simply clicking the button.

    Lastly, once the checkmark is placed and the current cell moves down one row: I would want the previous font and font size restored to what it was prior to clicking the button. That's why I haven't already changed the "10" to a "12". Wasn't sure where to change it back...


    <pre>Sub Check()
    '
    ' Check Macro
    ' Macro recorded 11/15/2002 by Ricky
    '

    '
    ActiveCell.FormulaR1C1 = "
    - Ricky

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

    Re: Check Mark Macro (Excel XP)

    This probably does what you want:<pre>Sub Check()
    ActiveCell.FormulaR1C1 = "

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Check Mark Macro (Excel XP)

    Perfect, Thanks Hans, I appreciate your help.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Check Mark Macro (Excel XP)

    I've tried to add a line to the code under the
    <pre>.Size = 12</pre>

    to align the checkmark horizontally. Guess I'm going about it wrong. As soon as I enter the ".", a drop down box presents itself with the available options for the next statement, but "align or alignment" is not on the list.

    Since any entry in this column would be centered, would it be better to just format the appropriate cells on the sheet itself?
    - Ricky

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

    Re: Check Mark Macro (Excel XP)

    Alignment is a property of the cell, not the font. Try the following:

    <pre>Sub Check()
    ActiveCell.FormulaR1C1 = "
    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Check Mark Macro (Excel XP)

    Awesome! Thanks much. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    - Ricky

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Check Mark Macro (Excel XP)

    This subject has been covered before but the vba code is missing from the posts above this one.

    I am trying to make a macro that I can attach to a toolbar button that will do the following:

    1. <LI>Place a center-aligned checkmark in the active cell [Alt+0252], then
      <LI>change the active cell; moving down one.
      <LI>restore the font, size, and alignment properties to the original state (pre-checkmark).
    The code below results in an error when it gets to ".xlDown".
    I also tried: Selection.xlDown. That didn't work either!

    Any assistance would be appreciated.

    <pre>Sub checkmark()
    '
    ' check Macro
    ' capture current font, size, & alignments.
    ' change font to Wingdings
    ' place a center aligned checkmark (Alt + 0252)
    ' and make next cell down active
    ' and restore font, size, & alignments.
    '
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    With Selection.Font
    .Name = "Wingdings"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    ActiveCell.FormulaR1C1 = ""
    xlDown

    End Sub</pre>

    - Ricky

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

    Re: Check Mark Macro (Excel XP)

    xlDown is not a command, it's a constant. To move one cell down, use

    ActiveCell.Offset(1, 0).Select

    I'm not sure what you mean by "restore" the font etc. properties. You've moved to a different cell so what's to restore?

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Check Mark Macro (Excel XP)

    Thanks for fixing the "down one cell" issue.

    <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22> I guess I was thinking that the font, size, etc. would stick and carry over to the next cell... I don't know, it's been a long day, and I'm having a senior moment.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

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

    Re: Check Mark Macro (Excel XP)

    You could copy the formatting of the active cell to the cell below (before creating the check mark), if desired:

    ActiveCell.Copy
    ActiveCell.Offset(1, 0).PasteSpecial xlPasteFormats
    ' Code to create check mark goes below
    ...

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Check Mark Macro (Excel XP)

    I tried to use the checkmark button after selecting a range of cells (E5:E20); it placed a checkmark in E5 and made E6 the active cell.

    What if I would have wanted checkmarks placed in each cell in the range? The code begins with "With Selection". I tried changing that to "With Selection.Range". No luck there.
    - Ricky

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

    Re: Check Mark Macro (Excel XP)

    If you want to enter a check mark in each cell of the current selection you can use

    Sub CheckMark()
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    With .Font
    .Name = "Wingdings"
    .Size = 10
    End With
    .Value = ""
    End With
    End Sub

  13. The Following User Says Thank You to HansV For This Useful Post:

    The Hedgehog (2014-11-20)

Posting Permissions

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