Macro to determine Text Replacement

You seem to be saying that you want, for example, C5 to toggle between "Text B" and "Text X" when the yellow button is click. If so how does the button know that you want to do C5 and not G5 or the other two?

Try this which doesn't use the button. The code is in the SelectionChange sub and so if, say, you clicked C5, you'd have to manually select a different cell first if you immediately wanted to have C5 change back. The Range("C1").Activate code selects the other cell for you. You can remove them or change what they select.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("C5")) Is Nothing Then
    If Range("C5") = Range("N33") Then
        Range("C5") = Range("N27")
    Else
        Range("C5") = Range("N33")
    End If
    Range("C1").Activate
    Exit Sub
End If
If Not Intersect(Target, Range("G5")) Is Nothing Then
    If Range("G5") = Range("O33") Then
        Range("G5") = Range("O27")
    Else
        Range("G5") = Range("O33")
    End If
    Range("C1").Activate
    Exit Sub
End If
If Not Intersect(Target, Range("C15")) Is Nothing Then
    If Range("C15") = Range("N28") Then
        Range("C15") = Range("N34")
    Else
        Range("C15") = Range("N28")
    End If
    Range("C1").Activate
    Exit Sub
End If
If Not Intersect(Target, Range("G15")) Is Nothing Then
    If Range("G15") = Range("O34") Then
        Range("G15") = Range("O28")
    Else
        Range("G15") = Range("O34")
    End If
    Range("C1").Activate
    Exit Sub

Select allOpen in new window

Martin,

I loaded the code up just so I could see what you were doing but it didn't run.  

To answer your first question;

The yellow button determines which way the text replacement should work.  Click it once and it's in a mode where if you click on C5, it changes the text, however, you must click on one of the other three boxes for C5 to revert to the original Text.  If you click the button again, it switches modes to where if you click on any of the boxes, it will change and stay changed UNLESS you click on that particular box again.  Then it will revert back to the original.

I hope that is a cleaner explanation.

B.

A little more clarity:

The yellow button determines which way the text replacement should work.  Click it once and it's in a mode where if you click on C5, it changes the text, however, you must click on one of the other three boxes for C5 to revert to the original Text (and concurrently display the new text in the box now clicked).  If you click the button again, it switches modes to where if you click on any of the boxes, it will change and stay changed UNLESS you click on that particular box again.  Then it will revert back to the original.

When you say

...however, you must click on one of the other three boxes for C5 to revert to the original Text...

is that the same thing as saying the following?

...however, you must click on one of the other three boxes for C5 to revert to the Current Text value...

Yes.  The original Text is also known as the "Current" Text.  The other table has "Future" Text.  

So much for my clarity ;-)

B.

Got to do some exercise. Be back in a while.

Exactly!  You know, sitting is the new smoking!  Your profile picture indicates you like to hike.  Come visit my family sometime, and I'll take you on some great hikes in the mountains of VA, NC and SC.

Try the attached. When the button says "Mode 1" I think it does what you ask for in the first part of  Post ID 41923375 and when it's "Mode 2" it does the other.

If you don't like the "Mode 1" and "Mode 2" captions then change them in the shpMode_Click macro and in the line of code in the Worksheet_SelectionChange event.
28988967a.xlsm

Oh, and thanks for the offer but I don't get to the east coast very often and since I'm 75 the mountains may be too tough for me.

Well, you're still invited if you ever want to come over.

So Martin.  When I toggle between the two modes, they play out the same way.  Mode 1 works precisely as originally requested.  In Mode 2, when I select the upper left box and click on it, it should change "B" to "X".  Another click to the same box doesn't change the outcome in the box....it's still X.  However, when I click any of the other 3 boxes, they change but most importantly it resets the other 3 boxes so that the upper left box resets to B while the lower left box now shows W instead of A.  If I click on the upper right box, then "D" becomes "Z" and the lower left box switches back to A.  Make sense?   In other words, the user is seeing one box behind the other, one at a time and the macro is automatically resetting all other 3 back to the original condition.

B.

I find when we are in Mode 2 and I click on C5 several times that it toggles between B and X and back to B, etc., so please try it again. But I'm quite confused as to what else should happen so let me give you a scenario and you tell me what the result should be.

If the grid looks like

Text B Text D
Text A Text Y

what should it look like after I click C5 in Mode 2?

Text X     Text D
Text A     Text C

B.

OK, a picture is worth a thousand words so when were in Mode 1, what should this look like after C5 is clicked?

Text B Text D
Text A Text Y

Text X    Text D
Text A    Text Y

In Mode 1, only that cell changes AND stays changed until clicked again.  Then it reverts back.  So if you clicked C5 and X was in it, it would go back to B.  The other cells are not changed.  AND, if Cell G15 is clicked, G15 goes from Y to C.  Nothing changes in any other cell.

In Mode 2, any cell that is clicked changes, BUT all the other cells revert back to their original state.

Nailed it!  Great job.  Thank you very much.  I'm going to ask a simple related question concerning wrapping and centering the text in the Boxes.

Thank you again..... appreciate the support.

B.