Macro not working 100%

phillipcook

Board Regular
Joined
Jun 25, 2015
Messages
87
Hi guys

I have the following macro:
Code:
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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Can you give an example of when multiple columns should unhide?
 

phillipcook

Board Regular
Joined
Jun 25, 2015
Messages
87
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Jun 25, 2015
Messages
87

ADVERTISEMENT

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 WorkPositionLocationPurchase OrderStart
Time
Finish
Time
Vehicle AllowanceHours
Potable Water - InvestigationScientific OfficerPotable Water Sampling11:2013:00 1.67
Surface Water - InvestigationScientific OfficerSurface Water Sampling 13:0014:00 1.00
Ground Water - Unscheduled No PurgeScientific OfficerGround Water Sampling 14:0015:30 1.50
Quarterly Contractor MeetingReport WriterNATA Laboratory 15:3018:001.02.50

<colgroup><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>
The background information sheet looks like this:
HPCT Purchase Order NumberDBCT Surface Water Sites <0.5m Below Spillway SampledDBCT Unscheduled Surface Water Sites SampledDBCT Unscheduled Potable Water Sites SampledDBCT Unscheduled Unpurged Ground Water Sites AttendedDBCT Unscheduled Purged Ground Water Sites AttendedDBCT Discharge Water Sites SampledDBCT Contractor meeting, Total Gauge RepresentativesDBCT Scheduled Unpurged Ground Water Sites AttendedDBCT 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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Jun 25, 2015
Messages
87
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What I mean is start with those columns hidden and then make your code unhide them as appropriate like this:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,623
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top