Help needed to to auto rotate value in Cell

bemp87

Board Regular
Joined
Dec 10, 2016
Messages
100
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
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.
 
Last edited:

bemp87

Board Regular
Joined
Dec 10, 2016
Messages
100
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

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