Auto Hide Columns

Hello Everyone

This is my first post and I need your help I’ve not written VB scripts before but can mostly understand them . I have created a form with a number of different drop down lists (which I’ve attached)

What I need to do is if C6 changes to Nursery hide columns G, H , M and N, Junior hide columns E, F, M and N and Senior hide columns M and N hide

But if School name changes to The Royal High School Bath then if C6 changes to Nursery hide columns G, H , M and N , Junior hide columns E and F, Senior hide column E

I hope this makes sense and I hope someone can help me

Thank you

Hi Arlette

Thank you for your reply. If you click on the school name the drop down arrow shows at the end row. In the school list there are 24 schools listed.

sorry Ive attached again

Hi

Sorry mine using Excel 2010. I've tried to save as XLS but it removes all the list

Sorry I understand now No it will only be Bath in the condition as all the other schools will be the same and will only change if junior, senior or Nursery is selected.

Thank you for your help

The Royal High School Bath
Blackheath High School
Brighton and Hove High School
Bromley High School
Central Newcastle High School
Croydon High School
Heathfield School
Howell's School
Ipswich High School for Girls
Kensington Prep School
Northampton High School
Norwich High School for Girls
Notting Hill & Ealing High School
Nottingham Girls' High School
Oxford High School
Portsmouth High School
Putney High School
Sheffield High School
Shrewsbury High School
South Hampstead High School
Streatham & Clapham High School
Sutton High School
Sydenham High School
Wimbledon High School

if C6 changes to Nursery hide columns G, H , M and N, Junior hide columns E, F, M and N and Senior hide columns M and N hide

Arlette,

I think something like this?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Range("B3").Select
    If ActiveCell.Value = "The Royal High School Bath" Then
        Call Bath
    Else:   Call Schools
    End If
End Sub
Sub Bath()
    Range("C6").Select
    If ActiveCell.Value = "Nursery" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("G:H").EntireColumn.Hidden = True
        Range("M:N").EntireColumn.Hidden = True
    End If
    If ActiveCell.Value = "Junior" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("E:F").EntireColumn.Hidden = True
    End If
    If ActiveCell.Value = "Senior" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("E:E").EntireColumn.Hidden = True
    End If
End Sub
Sub Schools()
    Range("C6").Select
    If ActiveCell.Value = "Nursery" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("G:H").EntireColumn.Hidden = True
        Range("M:N").EntireColumn.Hidden = True
    End If
    If ActiveCell.Value = "Junior" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("E:F").EntireColumn.Hidden = True
        Range("M:N").EntireColumn.Hidden = True
    End If
    If ActiveCell.Value = "Senior" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("M:N").EntireColumn.Hidden = True
    End If
End Sub

As all the conditions for the schools are the same except for if The Royal High School Bath is selected

Also, Msmac - there is a spelling mistake on the "Section" tab for Nursery

Regards,

Mowgli

Thank you for your help I will try this and thank you for spotting the mistake.

No problem,

Was just picked up when the sheet did not change because the value in C6 was "Nusery", normally my spelling is atrosius

Mowgli

Thank you for your help with the code but I'm going to be a pain sorry. But where do I input the code. I've tired to copy an past into sheet1 and save not nothing changes within the main form. I've tried insert a userform but again nothing happens

Sorry to be so stuipid

Msmac,

its not a problem, you are in the right place if you have questions

Open your workbook and press ALT + F11 to open the VBA editor
You will see 11 sheets with the names to correspond with each sheet (Tab) in your workbook and at the end an extra section called "ThisWorkbook"
Double Click on ThisWorkbook and you will see a blank page to the right of the screen
Copy the Code from the section below and paste it into the blank area in ThisWorkbook
You can now close the VBA editor and save your workbook however it is important to save it as a Macro Enabled Workbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Range("B3").Select
    If ActiveCell.Value = "The Royal High School Bath" Then
        Call Bath
    Else:   Call Schools
    End If
End Sub
Sub Bath()
    Range("C6").Select
    If ActiveCell.Value = "Nursery" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("G:H").EntireColumn.Hidden = True
        Range("M:N").EntireColumn.Hidden = True
    End If
    If ActiveCell.Value = "Junior" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("E:F").EntireColumn.Hidden = True
    End If
    If ActiveCell.Value = "Senior" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("E:E").EntireColumn.Hidden = True
    End If
End Sub
Sub Schools()
    Range("C6").Select
    If ActiveCell.Value = "Nursery" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("G:H").EntireColumn.Hidden = True
        Range("M:N").EntireColumn.Hidden = True
    End If
    If ActiveCell.Value = "Junior" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("E:F").EntireColumn.Hidden = True
        Range("M:N").EntireColumn.Hidden = True
    End If
    If ActiveCell.Value = "Senior" Then
        Range("A:N").EntireColumn.Hidden = False
        Range("M:N").EntireColumn.Hidden = True
    End If
End Sub

I have attached a picture to show what it looks like in the VBA editor

VBA.jpg

Hi Mowgli

Thank you, thank you its working

Glad to be of help.

If you are happy with the solution don't forget to mark the thread as SOLVED

Mowgli