Hide rows based on dropdown box - Excel - Corsa Sport - for Vauxhall and Opel Corsa B, Corsa C and Corsa D

Not logged in [Login - Register]

You Are Not Registered Or Not Logged In

Corsa Sport » Message Board » Off Day » Geek Day » Hide rows based on dropdown box - Excel

New Topic

New Poll

Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.

A2H GO

Member

Registered: 14th Sep 04
Location: Stoke
User status: Offline

5th Jul 11 at 08:55

I have a drop down box with the option of YES or NO. (Cell G8)

If NO is selected I want rows 10 - 15 to hide and if YES is selected I want them to unhide.

I'm trying to use this macro, I open VBA, double click sheet 1, paste the code into the box and click save but when I select YES or NO from the dropdown box, nothing happens...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$8" Then
If Target.Value = 1 Then
Rows(10:15).EntireRow.Hidden = True
Else
Rows(10:15).EntireRow.Hidden = False
End If
End If
End Sub

pow

Premium Member

Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline

5th Jul 11 at 09:38

Surely you just want to If Target.Value = "YES" Then...

Also, do you need to use quotes round $G$8?

Also you need to start the macro on workbook open.

[Edited on 05-07-2011 by pow]

A2H GO

Member

Registered: 14th Sep 04
Location: Stoke
User status: Offline

5th Jul 11 at 09:53

If I take the quotes from around the cell I get an error. So like this:

Private Sub Workbook_Open ()

If Target.Address = "$G$8" Then
If Target.Value = "YES" Then
Rows(10:15).EntireRow.Hidden = True
Else
Rows(10:15).EntireRow.Hidden = False
End If
End If
End Sub

What do you mean, start the macro in workbook open, like above?

It also doesnt like the Rows(10:15), I dont get an error when I change it to a single row, i.e Rows(10)

[Edited on 05-07-2011 by A2H GO]

Dom

Member

Registered: 13th Sep 03
User status: Offline

5th Jul 11 at 10:18

code:
If Target.Address = "$G$8" And Target.Value = "YES" Then

Range("10:15").EntireRow.Hidden = True

ElseIf Target.Address = "$G$8" And Target.Value = "NO" Then

Range("10:15").EntireRow.Hidden = False

End If

That should work; just make sure the target values are correct.

Edit - Personally i'd use form controls over excels in-built drop downs, but that's only because i find them easier to use.

[Edited on 05-07-2011 by Dom]

A2H GO

Member

Registered: 14th Sep 04
Location: Stoke
User status: Offline

5th Jul 11 at 10:27

Cheers Dom, I've pasted that in and the target address' are right, clicked save but still nothing happens when I select YES or NO on the dropdown.

I've tried pasting it into 'Sheet 1' and 'This Workbook'. Do I need to click the play button and save it as a Macro or should it just work?

pow

Premium Member

Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline

5th Jul 11 at 10:35

It's been a while since I've looked at VB with Excel so I may well be talking absolute bullcrap

Dom's looks much tidier - but Target.Value needs to be set to "YES" or "NO".

You need to 'play' a Macro

A2H GO

Member

Registered: 14th Sep 04
Location: Stoke
User status: Offline

5th Jul 11 at 10:44

Not sure if this pic will work as its blocked at work but this is where I'm at:

Dom

Member

Registered: 13th Sep 03
User status: Offline

5th Jul 11 at 12:11

quote:
Originally posted by A2H GO
Cheers Dom, I've pasted that in and the target address' are right, clicked save but still nothing happens when I select YES or NO on the dropdown.

I've tried pasting it into 'Sheet 1' and 'This Workbook'. Do I need to click the play button and save it as a Macro or should it just work?

IIRC you need to save it as a macro and then you can execute the code (press 'Play' or F5 / or run the macro). I can't see any problems though from the pic.

A2H GO

Member

Registered: 14th Sep 04
Location: Stoke
User status: Offline

5th Jul 11 at 12:31

quote:
Originally posted by Dom

quote:
Originally posted by A2H GO
Cheers Dom, I've pasted that in and the target address' are right, clicked save but still nothing happens when I select YES or NO on the dropdown.

I've tried pasting it into 'Sheet 1' and 'This Workbook'. Do I need to click the play button and save it as a Macro or should it just work?

IIRC you need to save it as a macro and then you can execute the code (press 'Play' or F5 / or run the macro). I can't see any problems though from the pic.

Ah I need it to do it automatically, ie. User enters the spreadsheet then selects 'YES' to Travel and the row relating to this section automatically unhide if that makes sense. If they select 'NO', the rows hide again, as in the Macro is always active...

EDIT: Its definately working because if i put something wrong in the code and change the cell from YES to NO it jumps into the debugger in VBA.

[Edited on 05-07-2011 by A2H GO]

Dom

Member

Registered: 13th Sep 03
User status: Offline

5th Jul 11 at 12:49

I'm talking crud (from quickly testing it in Office 2007), you don't need to 'Run' it nor do you need to store a Macro for it to execute. What you do need to do is save it as a Macro Enabled Wordbook (extension - xlsm) and adjust the Macro Security setting otherwise the user will be presented with the Macro Disabled warning.
As you can tell it's been a good 5/8 years since i've really use VBA in Excel

A2H GO

Member

Registered: 14th Sep 04
Location: Stoke
User status: Offline

5th Jul 11 at 13:03

Saving the workbook as .xlsm hasn't made any difference.

Might give up in a min and get IT to create me an Intranet form.

Dom

Member

Registered: 13th Sep 03
User status: Offline

5th Jul 11 at 13:43

Make sure the 'if statement' code is in the correct sheet object (ie: Sheet1) only and NOT in ThisWorkbook object as ThisWorkbook doesn't have a Worksheet_Change event. I can't see why it shouldn't work otherwise though.
If you want you can always email it to me and i can have a look...

[Edited on 13-11-2011 by Dom]

A2H GO

Member

Registered: 14th Sep 04
Location: Stoke
User status: Offline

5th Jul 11 at 13:48

I've got it working

But it only works once, i.e. You select NO and the rows hide, but then no matter what you select after that nothing happens.

I think it wasnt working because Cell G8 was a merged cell with F8.

EDIT: Sorted! Thanks so much for your help, i'm well impressed with this!!

[Edited on 05-07-2011 by A2H GO]

A2H GO

Member

Registered: 14th Sep 04
Location: Stoke
User status: Offline

5th Jul 11 at 15:36

I have just come across a problem though....if I try to delete anything thats been entered into one of the unhidden cells, I get a debug error and it highlights the second line of the code in VBA...any ideas?

For example I unhide 'Travel' then enter a date into 'Date Required' then when I try to remove the date to do another the error pops up....

EDIT: Just realised its only if you use the Delete key, its fine if you use the backspace key. Which would be OK but doubt i'll be able to tell 500 to use the backspace key instead of delete.