column hide based on different worksheet value

[jonmckennawait]

Hi all

This was previously solved but I have hacked the workbook around a bit so reopening for help

In the attached workbook I need to be able to do the following:

If the value in Coloumn C on the support area worksheet is 'NO' then the corresponding field on the OAC worksheet auto hides.

The reference in coloumn A on support area worksheet is replicated in coloumns G to CW on the OAC worksheet

e.g. Support Area worksheet C3=NO then on the OAC worksheet coloumn G hides.

This code needs to reverse as well so if the answers on coloumn see are anything other than NO then unhide the coloumn on OAC.

If I have made this confusing please feel free to check my other post entitled the same where a forum member helped previously

Thanks in advance Jon

[ragulduy]

I would try something like:

Please Login or Register  to view this content.

It needs to go in the sheet object module for sheet4(Support Areas), I note that you have your selectionchange events in standard modules.

[jonmckennawait]

Hi there
I have copied in but it does not seem to work ...absolutly my issue as I am a bit of a Excel Novice!!

[ragulduy]

Hi - take a look at the attached example

[jonmckennawait]

In your example it is correct for other than CE1 who are No but still showing

[ragulduy]

Hi - I think it just needs updating. Change it to 0 and then back to NO and it should be ok.

[jonmckennawait]

ok great what do I need to do tp get the code from the example sheet into my version?

my version has macros and formulae in it I cant lose

Thanks

[ragulduy]

Copy the macro code from the sheet object module.

[jonmckennawait]

sorry ragulduy the code works if you manually enter no on the support area sheet however this is driven by the answers to the questions sheet

Thanks

[jonmckennawait]

sorry ragulduy the code works if you manually enter no on the support area sheet however this is driven by the answers to the questions sheet

Thanks

[ragulduy]

Sorry, I missed that they were formulas. Looking at your example, the formulas are linked to column D on sheet "Questions". Maybe you can just use:

Please Login or Register  to view this content.

Instead and put it in the sheet object module for the "Questions" worksheet?

[jonmckennawait]

seems to work every now and again i get a run ime error 13 type mismatch when debug this shows

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D:D")) Is Nothing Then
Sheets("OAC").Columns(WorksheetFunction.Match(Target.Offset(, -3), Sheets("OAC").Rows(1), 0)).Hidden = Target = "NO"
End If
End Sub

[ragulduy]

Are you trying to change more than one cell at a time? Does the value in column A correspond to a column header on the sheet OAC? Are you changing the cells by copy and paste?

The code works ok on your example for me...

[jonmckennawait]

It may well be all the hacking around I have done with the workbook stepping through the sheets they should behave as follows
Cover sheet - macros behind the buttons run hiding various rows and coloumns on the questions/support areas and OAC sheets
Questions sheet - the answers to the questions put a value in the support areas sheet
Support areas sheet - conditional formaating in place if answer is no and should hide corressponding coloumns on OAC
OAC sheet - conditional formatting and pick from drop down in place.

in answer to your questions column a reference number does corresspond to OAC coloumn and no not copy and pasting anything.

Thanks

[jonmckennawait]

NEw OAC Proposal V0.9.xls.xlsmAll

I am still struggling with finalising this project any help please would be great I have attached another version if someone could put the code in and send back that would be amazing