Toggle button to run two Macros

[benjy79]

I have a spreadsheet that I put two buttons on. One button runs a macro that conditionally formats dates that will expire in next year and other runs a macro to undo that formating. What I want to do is have just one button - a toggle button that when clicked once shows expirations in red and clicked again will go back the way it was.

I want button to say "Check Experations" to start. Then when clicked makes dates coming in next year red.

The button will then read "Remove Check" So user will click to get rid of the red dates.


I have this as code for first button:

Sub CheckExpireations()
'
' CheckExpireations Macro
'
' Keyboard Shortcut: Ctrl+y
'
Range("D9:AC24").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=$AF$1+365"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
End Sub




And this for code for second button(macro)

Sub Uncheck()
'
' Uncheck Macro
'
' Keyboard Shortcut: Ctrl+r
'
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
Range("D9:AC24").Select
Selection.FormatConditions.Delete
End Sub


So in short toggle button shows "Check Experations" if Clicked runs macro CheckExperations(). Then button shows "Remove Check" and when clicked runs macro Uncheck()

Thanks for your help

 

[Smoakstack]

make two buttons. and move over each other. When you click on one, button1.visible = false, button2.visible = true. When you click the other then, button1.visible = true, and vice versa.

 

[Warship]

Welcome to the board.
Using an ActiveX CommandButton, try this:

Private Sub CommandButton1_Click()
  Select Case CommandButton1.Caption
    Case "Check Expirations"
      Range("D9:AC24").Select
      Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
      Formula1:="=$AF$1+365"
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
      .Color = -16776961
      .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = True
      CommandButton1.Caption = "Remove Check"
    Case "Remove Check"
      ActiveWindow.ScrollRow = 11
      ActiveWindow.ScrollRow = 9
      Range("D9:AC24").Select
      Selection.FormatConditions.Delete
      CommandButton1.Caption = "Check Expirations"
  End Select
End Sub

When you first setup the button, be sure to manually set the caption to one or the other.
This code goes in your Sheet Module. You could also toggle the color for more of a visual cue.

 

[benjy79]

Thanks Warship that worked. How would I change the color? I changed in properties but the button was same color on Check Experations and remove Check.

 

[Warship]

Private Sub CommandButton1_Click()
  Select Case CommandButton1.Caption
    Case "Check Expirations"
      Range("D9:AC24").Select
      Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
      Formula1:="=$AF$1+365"
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
      .Color = -16776961
      .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = True
      CommandButton1.Caption = "Remove Check"
      CommandButton1.BackColor = &H8080FF
    Case "Remove Check"
      ActiveWindow.ScrollRow = 11
      ActiveWindow.ScrollRow = 9
      Range("D9:AC24").Select
      Selection.FormatConditions.Delete
      CommandButton1.Caption = "Check Expirations"
      CommandButton1.BackColor = &HC0FFC0
  End Select
End Sub

To pick the colors you want:
Go into button properties and manually change the color.
Copy the number that shows after selection, i.e. &H00C0FFC0&
Paste this into your VBA code after: CommandButton1.BackColor =
After pasting, the number will change to the way Excel wants it.
Repeat for your second color choice.