Macro not working 100%

[phillipcook]

Hi guys

I have the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rng As Range


If Target.Cells.CountLarge > 1 Then Exit Sub
Set rng = Intersect(Target, Union(Range("E4"), Range("A10:A19")))
If rng Is Nothing Then Exit Sub


Select Case rng.Address(0, 0)
    Case "E4"
        Columns("J").EntireColumn.Hidden = Not CBool(Target.Value = "BMA - Hay Point Coal Terminal")
    Case Else
        Columns("K").EntireColumn.Hidden = Not CBool(Target.Value = "Surface Water - Monthly")
        Columns("L").EntireColumn.Hidden = Not CBool(Target.Value = "Surface Water - Investigation")
        Columns("M").EntireColumn.Hidden = Not CBool(Target.Value = "Potable Water - Investigation")
        Columns("N").EntireColumn.Hidden = Not CBool(Target.Value = "Ground Water - Unscheduled No Purge")
        Columns("O").EntireColumn.Hidden = Not CBool(Target.Value = "Ground Water - Unscheduled Purge")
        Columns("P").EntireColumn.Hidden = Not CBool(Target.Value = "Discharge Water - Daily" Or Target.Value = "Discharge Water - Weekly" Or Target.Value = "Discharge Water - Investigation")
        Columns("Q").EntireColumn.Hidden = Not CBool(Target.Value = "Quarterly Contractor Meeting")
        Columns("R").EntireColumn.Hidden = Not CBool(Target.Value = "Ground Water - Weekly" Or Target.Value = "Ground Water - Quarterly" Or Target.Value = "Ground Water - 6 Monthly")
        Columns("S").EntireColumn.Hidden = Not CBool(Target.Value = "Ground Water - Weekly" Or Target.Value = "Ground Water - Quarterly" Or Target.Value = "Ground Water - 6 Monthly")
End Select


End Sub

The Macro is close to working. The cells are remaining hidden if the data does not match, however only one column at any time will un-hide (the un-hidden column relates to the last data entered)

on occasions, if the criteria is met, multiple columns should be displayed, but this isn't occurring.

Can anyone please help me figure out why this macro is only allowing one column to ever un-hide?

Cheers in advance.

 

[RoryA]

Can you give an example of when multiple columns should unhide?

 

[phillipcook]

As an example:
If the following was true
A10 has "Surface Water - Investigation"
A11 has "Ground Water - Weekly"
A12 has "Quarterly contractor Meeting"

This should mean the following columns should un-hide:
L
R and S (as the criteria is met for both)
Q

currently if I had this data entered (with A12 "Quarterly contractor Meeting" being the last information entered) the only cell that would be un-hidden would be Q

 

[RoryA]

Your code won't work then because each subsequent entry will hide the previously unhidden ones. I suspect what you actually need to do is either only unhide columns based on the target value, not hide them, or loop through a range of cells to determine all the columns that should be visible. Hard to say which is appropriate without knowing the workflow/circumstances.

 

[phillipcook]

So the document that I am working on is a time record sheet.

On set tasks, we need to collect background information in relation to the number of samples taken.

the record sheet is as follows:

Scheduled Work	Position	Location	Purchase Order	Start Time	Finish Time	Vehicle Allowance	Hours
Potable Water - Investigation	Scientific Officer	Potable Water Sampling		11:20	13:00		1.67
Surface Water - Investigation	Scientific Officer	Surface Water Sampling		13:00	14:00		1.00
Ground Water - Unscheduled No Purge	Scientific Officer	Ground Water Sampling		14:00	15:30		1.50
Quarterly Contractor Meeting	Report Writer	NATA Laboratory		15:30	18:00	1.0	2.50

<colgroup><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

The background information sheet looks like this:

HPCT Purchase Order Number	DBCT Surface Water Sites <0.5m Below Spillway Sampled	DBCT Unscheduled Surface Water Sites Sampled	DBCT Unscheduled Potable Water Sites Sampled	DBCT Unscheduled Unpurged Ground Water Sites Attended	DBCT Unscheduled Purged Ground Water Sites Attended	DBCT Discharge Water Sites Sampled	DBCT Contractor meeting, Total Gauge Representatives	DBCT Scheduled Unpurged Ground Water Sites Attended	DBCT Scheduled Purged Ground Water Sites Attended

<colgroup><col span="7"><col span="3"></colgroup><tbody>
</tbody>

I have conditional formatting in all of the cells in the background table (box turns yellow when extra data needs to be provided.

As this takes up a lot of room, what I wanted to do was hide columns J:S unless the criteria outlined in the Macro above is entered in one of the cells between A10:A19

 

[RoryA]

Then I'd probably suggest hiding J:S to start with and have the code only unhide columns based on the values entered, not hide anything.

 

[phillipcook]

Hi Rory

How would I write the code?

Below I have written a table of the defined names that can possibly appear anywhere between A10:A19 (Apart from the first one in the list "HPCT" which is located only in E4) and the columns that need to unhide based on the selection:

​

Defined Names	Column To Unhide
HPCT Location: E4	J
Surface Water - Monthly Surface Water - Bi-Monthly Surface Water - Quarterly Surface Water - Annual Location: A10:A19	K
Surface Water - Investigation Location: A10:A19	L
Potable Water - Investigation Location: A10:A19	M
Ground Water - Unscheduled No Purge Location: A10:A19	N
Ground Water - Unscheduled Purge Location: A10:A19	O
Discharge Water - Daily Discharge Water - Weekly Discharge Water - Investigation Location: A10:A19	P
Quarterly Contractor Meeting Location: A10:A19	Q
Ground Water - Weekly Ground Water - Quarterly Ground Water - 6 Monthly Location: A10:A19	R
Ground Water - Weekly Ground Water - Quarterly Ground Water - 6 Monthly Location: A10:A19	S

<tbody>
</tbody>

Thank you heaps for your help as I'm only just starting to learn about macros

 

[RoryA]

What I mean is start with those columns hidden and then make your code unhide them as appropriate like this:

Select Case rng.Address(0, 0)
    Case "E4"
        If rng.Value = "BMA - Hay Point Coal Terminal" Then Columns("J").EntireColumn.Hidden = False
    Case Else
        If rng.Value = "Surface Water - Monthly" Then Columns("K").EntireColumn.Hidden = False
' etc