Toggle the text on a button at each press

[IronM]

So, this is a followup question of this thread.

I have now this code:

Sub IronM()
   If Range("Plan").Column = 3 Then
      ThisWorkbook.Names("Plan").RefersTo = Range("Plan").Offset(, 1)
   Else
      ThisWorkbook.Names("Plan").RefersTo = Range("Plan").Offset(, -1)
   End If
End Sub

Is there a way to add to this code to a button so when I click once it will write Forecast on it and click second will show Planned and cycle between these 2 at each press (considering the default (column 3) is Planned?
Doesn't necessary needs to be a visual change of the design of the button but at least for the text to appear so I know on which column the name range moved to.

Thank you!

 

[Fluff]

Is this a Form Control Button, or an ActiveX one?

 

[IronM]

Is this a Form Control Button, or an ActiveX one?

I am using a Form Control Button but if it's better with ActiveX one, will use that too. The code you provided initially, I've added to a macro attached to the button. If there is a better way, I'm open to it.

 

[Fluff]

Forgot to ask, what is the name of the button?

 

[IronM]

FC_button

 

[Fluff]

Ok, how about

Sub IronM()
   If Range("Plan").Column = 3 Then
      ThisWorkbook.Names("Plan").RefersTo = Range("Plan").Offset(, 1)
   Else
      ThisWorkbook.Names("Plan").RefersTo = Range("Plan").Offset(, -1)
   End If
   With ActiveSheet.Shapes("FC_button").TextFrame.Characters
      .Text = IIf(.Text = "Planned", "Forecast", "Planned")
   End With
End Sub

 

[IronM]

It appears they are inversed. And no matter if I replace in your code Planned with Forecast, they still appear wrong. Meaning that on column 3 I need it to be Planned and on 4 Forecat.
Does it have to do with the column not being 3 anymore? I changed it to 10 as I moved my data there...

 

[IronM]

Will it be easier to use an ActiveX button? I also want to change the color of the button and apparently I cannon on a Form Control button.

 

[IronM]

It appears they are inversed. And no matter if I replace in your code Planned with Forecast, they still appear wrong. Meaning that on column 3 I need it to be Planned and on 4 Forecat.
Does it have to do with the column not being 3 anymore? I changed it to 10 as I moved my data there...

Nevermind, I don't know what happened but now it works...

Many thanks!!

 

[Fluff]

You're welcome & thanks for the feedback.