Help needed to to auto rotate value in Cell

[bemp87]

Howdy- I'm needing some help with something i'm not sure how to do.

I have a 2 cells (text values) that has names (i.e. Bob and James), i am needing a way to have these values swap each week for example.

If this week E5 is Bob and C7 is James, i need for it to next week automatically switch to where James should be E5 and Bob should be C7.

I would assume there is a way to do this but i am unsure as to how. Any help would be much appreciated.

 

[JLGWhiz]

You would need a named range for a storage cell somewhere on your worksheet to prevent the names from changing when the sheet is deactivated and then reactivated on Monday. For demonstration purposes Cell XFD100000 will be used in the code, without naming the range. The purpose in nameing the range is so that it can still be used if there are additions or deletions of rows and columns.
It should be located far enough to the right and far enough downward that it would not be inadvertantly deleted by a user. If you use the named range be sure you substitute it everywhere the "XFD100000" appears in the code.

Private Sub Worksheet_Activate()
If Weekday(Date) <> 2 Then
    Range("XFD100000") = True
    Exit Sub
End If
If Range("XFD100000") <> "False" Then
    If Range("E5") = "Bob" Then
        Range("E5") = "James"
        Range("C7") = "Bob"
    Else
        Range("E5") = "Bob"
        Range("C7") = "James"
    End If
    Range("XFD100000") = "False"
End If
End Sub

copy and paste the code into the worksheet code module of the sheet you want the cells to populate on. To access the code module, right click the sheet name tab, then click 'View Code' in the pop up menu. The code will automatically run when the sheet is activated but will only execute the changes on Mondays.

 

[bemp87]

Hi Whiz- thanks for the code, a couple questions:

1. Is there a need to put any data in cell XFD1000000?
2. I know you said this will only execute on Monday's, for sake of giving it a test run today, is there way for me test it now to see how it runs?

 

[JLGWhiz]

Hi Whiz- thanks for the code, a couple questions:

1. Is there a need to put any data in cell XFD1000000?
2. I know you said this will only execute on Monday's, for sake of giving it a test run today, is there way for me test it now to see how it runs?

1. No need for a date in the storage cell, it is only used to check for true or false, which the code puts into it.
2. Weekdays are numbered 1 thru 7 for Sunday thru Saturday. To test the code you can change the day number in the 'If Weekday(Date) <>' statement to the current day of the week, then select a different sheet and come back to the target sheet to activate it. It will only switch cells once on the day used in the statement, while the storage cell = False. But don't forget to change the day number back to the day you want it to make the changes.