What to add if i click a button again

gazza_606

New Member
Joined
Aug 19, 2014
Messages
22
I currently have a button in an excel sheet that reverses a selection of data from positive to negative or visa versa. With the macro the font in the button changes from red to green to indicate that the button has been pressed. What i need to add is to change the font back to red if it is pressed again and needs to keep changing everytime the button is pressed.

Here is the current macro
Sub Reverse()
'
' Reverse Macro
'
ActiveSheet.Unprotect
Range("B20:B36").Select
Selection.Copy
Range("W72").Select
ActiveSheet.Paste
Range("X72").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-6]"
Range("X72").Select
Selection.AutoFill Destination:=Range("X72:X88"), Type:=xlFillDefault
Range("X72:X88").Select
Range("X72").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*R72C18"
Range("X72").Select
Selection.AutoFill Destination:=Range("X72:X88")
Range("X72:X88").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-42
Range("B20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Shapes.Range(Array("Button 68")).Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 4
End With
ActiveSheet.Protect
End Sub

Is there bit of code that can be added to keep switching between the two colours

Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
gazza_606,

Is the sole purpose of this code to toggle the values in B20:B36 from negative to positive?
 

gazza_606

New Member
Joined
Aug 19, 2014
Messages
22
Hi Snakehips, yes that is all this needs to do but i just need to know if they have been reversed and i thought the easiest way is to change the font colour.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This should switch between positive and negative and black and green button font.
Up to you to set the initial state / colour combination, after which it will toggle.

Code:
Sub Pos_Neg()
Application.ScreenUpdating = False
For Each cell In Range("B20:B36")
cell.Value = cell * -1
Next cell
With ActiveSheet.Shapes("Button 68").TextFrame.Characters.Font
If .ColorIndex = 4 Then
.ColorIndex = 1
Else
.ColorIndex = 4
End If
End With
End Sub

Hope that helps.
 

gazza_606

New Member
Joined
Aug 19, 2014
Messages
22

ADVERTISEMENT

Thanks, i have used this and it works :) thanks again
 

gazza_606

New Member
Joined
Aug 19, 2014
Messages
22
Hi, I am just looking into this again and have a quick question. although the values that need reversing are in B20:B36 this is the maximum case, sometimes i just need B20:B31, how do i only reverse the cells that are populated?
Thanks
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try....

Code:
Sub Pos_Neg()
Application.ScreenUpdating = False
For Each cell In Range("B20:B36")
If Not cell = "" Then cell.Value = cell * -1
Next cell
With ActiveSheet.Shapes("Button 68").TextFrame.Characters.Font
If .ColorIndex = 4 Then
.ColorIndex = 1
Else
.ColorIndex = 4
End If
End With
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,300
Members
430,537
Latest member
Antonio11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top