Macro code to run "Deactivatesheet" macro except when the target = Specific sheet

[Will S]

Heya,

I'm certain there is a code for this but can't really seem to find it when looking, I'd like some sort of IF function at the top of my "Deactivate" macro that says

If target = [Sheet Name] then
'Do "sweet FA"
Else
'My macro Here
End If

Could anyone enlighten me on this, keep going around in circles while looking on google.

Thanks,

 

[Will S]

Is this harder than I thought or is the code actually what was said above, I haven't actually tested it but from what I've seen with what I've researched is that I'll need target.something. I know that most of the ones I've seen with the target.address are for selection or value change, but is there no way to make it look at the sheet it's going to as it deactivates rather than a specific cell?

 

[hiker95]

Will S,

By deactivate, do you mean to delete the worksheet?

What are the worksheet names, in your workbook, that you DO NOT want to deactivate/delete?

 

[hiker95]

Will S,

If you have a workbook with the following worksheets:
Instructions
DataBase
Summary
work1
work2
work3
work4

And, you want to delete all the worksheets except the following worksheets:
Instructions
DataBase
Summary


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub DeleteSheets()
' hiker95, 01/09/2014, ME749361
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Instructions" And ws.Name <> "Summary" And ws.Name <> "DataBase" Then
    ws.Delete
  End If
Next ws
Application.DisplayAlerts = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the DeleteSheets macro.

 

[Will S]

Heya, when I say "Deactivate" I mean that when I'm on that sheet and try to change to another. I said deactivate as that's what the private sub is called. "Private Sub Worksheet_Deactivate()"

so what I'm after is something like:

Private Sub Worksheet_Deactivate()
If target.worksheet("StandardInformation") then   'As in the sheet I'm trying to activate/go to.
'nothing
Else
'My macro here
End If

Sorry for the confusing terminology as I said, I just used that term because that's it's default project name in the "workbook" module.

 

[Will S]

Although, looking at that, rather than checking the sheet names, and deleting the sheet, do you think it would be possible to say:

if "ws.Name = "StandardInformation" and Activesheet.Name = "StandardInformation" then"...

I'm unsure if this coding would work but I'll test it out and get back to you, if you hear nothing then it means it hasn't worked.

 

[hiker95]

Will S,

I'm unsure if this coding would work but I'll test it out and get back to you, if you hear nothing then it means it hasn't worked.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[Will S]

Bumping this but also asking a related question.

I'm still trying to get it so that it will ignore showing a message when I change from one sheet to a certain set of others. Seems there isn't a way to do the if target = blah blah.

As this is more of an issue than I thought I'll explain the issue.

Scenario
I have one (Or more sheets) that are created by the user. These sheets have various fields that NEED to be filled before moving on, as part of this I've made a number of pop-ups to make it annoying as possible for anyone trying to ignore the sheets without filling them in. (They might actually need to see other pages so I can't just block them).

I've made plenty of things to say on each page that the important pages are/aren't filled in and have even got it showing the name of the first Incomplete sheet and I've got it so that on activation of a non-vital sheet a pop up will appear reminding them that the other sheets aren't filled. (To make it as annoying as possible I also added a pop-up when leaving the important sheet)

Problem
What I'd like to do is if they choose to head back with the first pop up (shown upon leaving the incomplete sheet), when it takes them to the right sheet, it stops the other pop-up from appearing and adding confusion/causing bugs.

Brainstorm ideas (I'm still far from an expert with VBA so still not fully aware of it's limitations, a few of these ideas might be kinda dumb)

. Having it see the name of the sheet it's moving to and deactivating itself/not running depending on results.
. Having the first pop-up name a variable and setting it to 0, if you choose the "Go back" option then the 0 turns to a one and the second pop up will see this 1/0 and run accordingly. (Might try doing that with a cell rather than just creating a variable in VBA and hoping it carries over) I could probably do that but not sure if it's the best idea.
. Creating a "Run on deactivation of sheet" macro on the sheets with the second macro, to say "If active sheet = Important, Stop all pop-ups" Honestly this is the idea that I am the most skeptical about as I have no idea on coding for any of it. Also I'm pretty sure that the deactivate macro would run after the one I'm trying to stop anyway.

Any help with this is appreciated, I've tried option one and the first part of option two to no use. If you do have any thoughts could you please just give a bit of feedback on the brainstorm ideas as I'm trying to learn as much of VBA as possible, thanks.

Best regards,