Solved

Run a Sub on Check and Uncheck

Posted on 2010-08-19
5
203 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now