Using VBA to rename tables when a form tab is selected/de-selected

[Faction21]

I need to have a table renamed when a tab is selected, and when a different tab is selected, i need it to rename the first table back to its orignal name, then rename the next table.

For example:

These are the tables

ConcatenateM&M-002
ConcatenateM&M-003
ConcatenateM&M-004
ConcatenateM&M-005

when tab "002" is selected it wil rename "ConcatenateM&M" to "ConcatenateM&M"

When it is de-selected by selecting another tab like "003", it renames "ConcatenateM&M" back to "ConcatenateM&M-002" and then renames "ConcatenateM&M-003" to "ConcatenateM&M"

So that when swiching between tabs in no particular order(could be anytab at any time), the apporpirate table is renamed to a new name, and when another tab is selected in random order, it will rename the appropriate ables again.

I tried the rename feature in a macro, and had it run when a tab had focus, but it does not know what to do if another tab is selcted since its capabilites are very limited.

Does anyone know a posible solution in VBA?
-thanks

 

[Rich]

What's the logic behind the request?