hiding range of columns depending on a set value

**toeragh** · 12-07-2007, 10:04 AM

Hi What is wrong with this ... it does not appear to work. What am I missing.
Trying to hide components depending on whats selected - colum P has the triggers.

Please Login or Register  to view this content.

**jasoncw** · 12-07-2007, 10:53 AM

Perhaps this is what you are looking for?

Please Login or Register  to view this content.

Jason

**toeragh** · 12-07-2007, 11:23 AM

Hi Jason,

I've popped the sheet up ... still can't seem to get it working.
Would you mind having a look.

Thanks.

Peter.

**jasoncw** · 12-07-2007, 11:45 AM

Sorry, Peter, I cannot open that file, since I am using Office 97.

Jason

**toeragh** · 12-07-2007, 11:53 AM

Sorry Jason - force of habit to save as 2007 file.
try this one.

**toeragh** · 12-11-2007, 09:28 AM

OK ...
I have got my code doing what I want - well almost. I have had to set it up to use a command button to activate the code. How can I set it to change upon selection within sheet. IE A = Add ... blank is Remove it

Please Login or Register  to view this content.

**jasoncw** · 12-11-2007, 10:45 AM

Sorry, Peter, I forgot about this thread.

I included the following procedure in the Worksheet Change event handler, which will run the procedure whenever one of the applicable cells change:

Please Login or Register  to view this content.

HTH

Jason

**toeragh** · 12-11-2007, 10:58 AM

Jason,

You are a star, many thanks.

**jasoncw** · 12-11-2007, 11:01 AM

Actually, after looking at the code, this is a little shorter if you want to copy and paste over the other procedure:

Please Login or Register  to view this content.

Glad it helped.

Jason

**toeragh** · 12-11-2007, 11:51 AM

I'll give that a try as well ... I made a mistook in the original - i got my hides and unhides the wrong way round. Fixed it by switching the True and False round.

Really and truelly thanks for the help.

MMM ... that new one, only shows 1 component at a time... I added the line below under the DIM's to hide all components, then depending on what's selected the component is unhidden.

Please Login or Register  to view this content.

Peter.

**jasoncw** · 12-12-2007, 10:20 AM

Hi, Peter.

That second procedure I posted will work properly, but only if the cells are changed individually. So if you were to copy one cell into several at the same time, you are correct in that it will not properly work.

Instead of listing each range twice, I determined whether to hide them or not at the beginning, and assigning that True/False value to the variable bHide. So if the columns need to be hidden, bHide is True. Otherwise, bHide is False.
It will apply the correct T/F value to the appropriate columns, but again, only if cells are changed individually.

Based on your response that you had the T/F backwards, all you would need to do to correct the second procedure is swap the one set of T/F values.

Jason

**toeragh** · 12-12-2007, 12:54 PM

Hi Jason,

yes mate that's what I done with the second batch of code, switched the T/F round.

I have used some of the logic to create the effect i need. As I do need to show all selected components, not individuals.

Peter.