Event or Worksheet Handler Issue

Welcome to the forum.

See VBE Help for the Visible property as it applies to the Worksheet object.

Hi krampus

It'll probably be something like this

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

John

You should look at the xlSheetVisibility enumerations, jaslake.

Hi shg

I'm very interested in being educated. Will you explain the differences in these lines of code

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

As always, I appreciate your insight, John

The Visible property of a worksheet is not a boolean, since it can have 3 possible values:
xlSheetVisible (-1)
xlSheetHidden (0)
xlSheetVeryHidden (2)

Whilst your code will work, since -1 and 0 are the equivalent numeric values of True/False, I wouldn't use it since it's misleading and will require an implicit conversion. It also assumes that MS won't decide at some future date to alter the values of the enumeration!

Hi romperstomper

I was aware of this

The Visible property of a worksheet is not a boolean, since it can have 3 possible values

but never thought of it in this light

I wouldn't use it since it's misleading and will require an implicit conversion

Thanks for the insight.

John

Hi krampus

Paste this code in your "Inputs" worksheet. Right click on the "Inputs" tab, select view code then paste the code in the screen that presents itself.

I assume you have "Data Validation" set for Inputs Cell A4 (Yes,No).

Please Login or Register  to view this content.

I learn a LOT from the insight of these people.

John

John, if the target cell is not A4, the Intersect will return Nothing, which will give you a runtime error.

Hi shg

I REALLY do appreciate your feedback. My 'learnin comes from doing and I didn't test the code enough. I'm amazed at how you can look at code and see issues.

krampus, here is the revised code that avoids the issue shg pointed out

Please Login or Register  to view this content.

Let me know of issues.

John

Maybe ...

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

I modestly prefer the former for versatility and a general aversion to string comparisons, but can't say which is lighter weight.

And I always leave a blank line after a possible early exit, which some people avoid altogether.

And I'd change the hard-coded A4 to a named range.

EDIT: I should add this: A lot of people get stuck when they need to do mutiple things in event code, and hack things together in horrible ways. The better practice in general is to have a single line in the event handler that calls an application-related procedure in a code module:

Please Login or Register  to view this content.

That makes it clear(er) to to how to extend the code to do different things that are related to the application, rather than the events.

Hi shg

Your individual instruction is greatly appreciated. If I understand you correctly the code would be something like this (I use Call exclusively as it tells me I'm calling another procedure)

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I try to do this whenever there's more than one

application-related procedure in a code module

and I try to do this whenever there's a change in direction in the code as it compartmentalizes things for me

leave a blank line

I did this in the code but, in my opinion, it makes the code more difficult for the OP to follow

I'd change the hard-coded A4 to a named range

I welcome your criticism and instruction whenever you're willing to give it. Thank you.

John

Sorry, my example was atrocious -- it doesn't even compile. In this,

Please Login or Register  to view this content.

... the testing is part of the application, not the event handling, right? The event handler should just dispatch what needs to be done in the event:

Please Login or Register  to view this content.

... then in a code module, do the things that are related to the application, regardless of what event or anything else led them to be invoked:

Please Login or Register  to view this content.

My point is to describe partitioning of functionality for modularity, not to recommend the particular code above.

Of course we all routinely ignore that in giving examples in the forum -- but then, the advice you get here is worth every cent you pay for it ...

Hi shg

I'll need to study this tomorrow. It's after midnight here.

Thanks for your interest in my education.

John

@ jaslake:

Thanks a TON!! I think I might be trying to do something abov emy skill level. It might take me a while to figure out where/how to put this in and what to change to make it work. Your post was excellent so I imagine it won't take me too long. :D

I have a sample file if it would be helpful (see attached). I am new to stuff this complex in Excel (which for you lot seems pretty easy, so my apologies for being such a noob).

You can see that I have A4 on the Input Sheet which I want to tirgger the Event. I have tabs after that for each Game which includes a Roster and a Game Form. I want to hide or unhide the Roster and Game tabs for Games 11, 12 and 13 (which I have yet to develop) if A4 on the Input Sheet is changed.

Like I said, I might be trying to do something WAY beyond my skill level. :D

Hi krampus

You indicate

I have A4 on the Input Sheet which I want to trigger the Event

yet A4's value is "Playoffs". Cell B4 may be more appropriate as it's a "Yes/No" cell but it's your workbook.

You can't hide or unhide these sheets

I want to hide or unhide the Roster and Game tabs for Games 11, 12 and 13 (which I have yet to develop)

if they don't exist. You'll get a "Subscript Out Of Range" error if you try to hide/unhide non-existent worksheets (I think...never tried it).

As far as I can tell, your workbook doesn't contain the worksheets necessary to do what you want to do. Develop the sheets for Games 11, 12 and 13, figure out which cell is to trigger the event (A4 or B4) and get back to me.

You may sense that I'm a little aggravated...well, just a little. It seems to me you provided sample data that doesn't contain information central to your question. On the other hand, you opened up a philosophical discussion with shg that I'm still trying to absorb. So, for that I'm grateful.

Get back to me on the A4/B4 issue, create sheets for your missing games and then perhaps I can help.

John

Sorry for the delay. My daughter is going in for surgery and we have been dealing with that for a while.

Attached is the FULL example of what I am trying to achieve:

- Input Sheet: I am trying to make B4 the event trigger. If B4=Yes, I want six hidden tabs to appear. If B4=No I want the hidden tabs to remain hidden.
- Hidden Tabs: There are six of them. They are: Playoff Game 1, Playoff Game 1 Roster, Playoff Game 2, Playoff Game 2 Roster, Playoff Game 3, Playoff Game 3 Roster. In the attached example the tabs are not hidden as I did not want to confuse anyone with the other tabs that are hidden.

Does this example give you an idea of what I am trying to do? If not I am happy to change it.

Thanks!

Krampus

Hi krampus

Hope your daughter is doing well. Have children and grandchildren and understand the demands and priority of Family.

I'm traveling this week due to Family priorities so will not get to this until next week. If you don't have a solution by then, I'll be happy to look at it with you.

Best wishes to your daughter for her speedy recovery.

John

Hi krampus

What should happen to Input Sheet lines 16, 17 and 18 if the user selects "NO". Should they still appear or disappear?

John

Hi John-

Yeah, good catch. The only way I knew how to make them "disappear" was to use conditional formatting. I recall in 2003 being able to use the "group/ungroup" feature and conditional formatting to make that happen but I cannot find the file I did that in. In short they should not appear if the user selects "NO".

Hi krampus

Well, the site's back up. This code is included in the attached workbook. It's not in the style that shg suggested as I wanted to keep it simple. However, I do understand his point

The event handler should just dispatch what needs to be done in the event:

and

partitioning of functionality for modularity

Please Login or Register  to view this content.

I'll be glad to rewrite the code in shg's suggested approach but I believe the direct approach will make the code more understandable to less experienced persons.
See if this does as you require. Let me know of issues.

John

Hi krampus
Glad the code works for you and that you can follow it. If you're satisfied, please mark your thread as "SOLVED".
John