Solved

Run a Sub on Check and Uncheck

Posted on 2010-08-19
5
207 Views
Last Modified: 2013-12-25
Excel 2010

When checkbox is checked I want the first part to run. when unchecked I want the second part to run....is this possible using one checkbox control? Please help.

Private Sub CommandButton1_Click()
 If Value = True Then
    Range("C3").Select
    ActiveCell.ClearFormats
    ActiveCell.NumberFormat = "M/D/YYYY"

  Else
    Range("C3").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=TODAY()", Formula2:="=TODAY()+15"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16777024
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = False

 End if
End Sub

Open in new window

0
Comment
Question by:BigBadWolf_000
  • 2
  • 2
5 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 500 total points
ID: 33480684
Change your test to the code beneath and it should work (you'll have to change CheckBox1 to whatever name the control has.

If CheckBox1.Value = True Then



0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33480696
Is the Checkbox the CommandButton1 itself? Or another control?
0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 33480732
Yes the CheckBox1_Click() itsself. (CommandButton1 was a typo)
CheckBox1.Value did the trick thank you  calacuccia:
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33480743
If Sheets("Sheet1").Shapes("Check Box 1").DrawingObject.Value = 1 Then
  ' it is checked
Else
  ' it is not checked
End if
0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 33480761
Thanks for that code snippet too cyberkiwi:
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question