Problems Using My Custom Function

[PosseJohn]

I have a numerous worksheets that contain data which is graphed several different ways.

After my users enter the data, when they Deactivate the worksheet, I have a the following code for each of the worksheets.

Please Login or Register  to view this content.

This calls my custom function

Please Login or Register  to view this content.

First Question: Instead of using the specific worksheet name, how do I obtain the name of the sheet that was just deactivated, using Activesheet gives the name of the worksheet that was newly selected.

Second Question: The "For Each" statement does not work...what do I have wrong?

Lastly: When in VBA, on the properties dialog, I see (name) as the first entry, and then Name further down in the dialog. What's the different between the two? Should/can I use the (name) value, so that in case the user changes the tab name, the code will still work?


Thank you.

[Bob Phillips]

1. Use Me.Name

2. Use

Please Login or Register  to view this content.

3. The first one is the Codename. Yes, you should change it to a meaningful name and use that.

[PosseJohn]

The Me.Name works great, gives the name of the worksheet that is being deactivated. Tested by putting "Msgbox Me.Name" in code.

I ended up having to put a few more lines into the code...I also changed it from a function to a sub routine.

Please Login or Register  to view this content.

Any way of just referring to the deactivated sheet rather than selecting it?

[Bob Phillips]

Just use the worksheets(name) object.