hide tab if cell equals "Yes"

[alisachsf]

Hi,
I want to hide a tab based on a value in a different tab.
For example, if on the Dashboard cell D8 = "Yes" or "No" then hide the tab named "Audit".
If D8 has anything else but "Yes" or "No", then leave the tab visible.

Can anyone help with the code?

Thanks!

[Fluff13]

How about

Please Login or Register  to view this content.

This needs to go in the Dashboard sheet module

[Greg M]

Hi there,

See if the attached workbook does what you need. It uses the following code in the VBA CodeModule of the "Dashboard" worksheet:

Please Login or Register  to view this content.

The highlighted values may be changed to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[alisachsf]

Thank you!
Greg, in your example you have a drop down showing yes or no.
In my case it's a formula, and it could be anything.
So if it is yes or no, i need it hide the audit tab, but if for example is says "sales" then leave it visible.

How can I adjust your code to do that?

Thanks!

[Greg M]

Hi again,

Just remove the data validation from the cell and replace its contents with your formula. In fact, if you just clear the contents of the cell (instead of selecting "No" from the dropdown), the tab will be hidden.

Regards,

Greg M

[Fluff13]

Change events are not triggered by formulae, What is the formula?

[Fluff13]

If the only cells that D8 looks at are on the Dashboard sheet, you could use

Please Login or Register  to view this content.

[Greg M]

Change events are not triggered by formulae

Nice one, Fluff13 - my bad for not noticing the implication of the changed requirement!


Hi again alisachsf,

I think the following code should do what you need:

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[alisachsf]

Thank you both! Really appreciate the help.

Unfortunately I get an error on the Me.Range (invalid use of Me keyword).

[Greg M]

Hi again,

That error suggests that you've entered the code in a standard VBA CodeModule - it must be entered in the VBA CodeModule for the worksheet in question.

Hope this helps,

Regards,

Greg M

[alisachsf]

So I added it to the Dashboard tab module where I already have a call macro.
The new macro (to hide a tab) doesn't work automatically, but when I added a line to the call it created an error.
Right now I have the following under the Dashboard module:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Greg M]

Hi again,

The "Worksheet_Hide" routine declares "Target" as an argument:

Please Login or Register  to view this content.

but you have not passed any argument to it in the calling statement:

Please Login or Register  to view this content.

The calling statement should read:

Please Login or Register  to view this content.

Hope this helps.

Regards,

Greg M

[alisachsf]

That didn't work, not sure why. But I switched the code to the following and now it works!

Please Login or Register  to view this content.

Thanks for your help!