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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
gazza_606,

Is the sole purpose of this code to toggle the values in B20:B36 from negative to positive?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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
Back
Top