Solved

Run a Sub on Check and Uncheck

Posted on 2010-08-19
5
208 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 describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

789 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