Drop down list activates a macro

[marin820]

Hi everyone,

This is a good, fairly straightforward one: I have a drop down list with 3 choices on a spreadsheet (form that people fill out). I'm trying to write a macro where, choice 1 keeps the form as is; choice 2 hides rows 20-30 and unhides rows 30-40; choice 3 hides rows 20-30 and unhides rows 40-50.

Thanks for all your help.

 

[davidhall80]

Stick that in the module for your chosen sheet


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1") = "choice 2" Then
Rows("20:30").EntireRow.Hidden = True
Rows("30:40").EntireRow.Hidden = False
End If

If Range("A1") = "choice 3" Then
Rows("20:30").EntireRow.Hidden = False
Rows("30:40").EntireRow.Hidden = True
End If


End Sub

 

[marin820]

The problem with the above code is that I'm using a data validation list in cell A1, and you cant use the Worksheet_Change() because data validation lists don't trigger events.

Does anyone know a way around this - should I try Worksheet_Calculate() or something else instead?

 

[davidhall80]

I would say to use a Combo Box, but you probably want to keep the formatting of the data validation cells....right??

 

[Norie]

marin280

What version of Excel are you on?

If it's 2000 or later then data validation will trigger the change event.

 

[marin820]

I'm using Excel 2003...I tried it again and entered the code in Object, not Module and this seems to work, but only on Choice 2; when I pick Choice 3 the form doesn't change or hide the respective rows.

Also, is it possible to toggle back and forth between options on the drop down menu and have the form change accordingly, or is this only possible with a combo box?

 

[davidhall80]

I went to sheet1---> View code---> and pasted that code in there and the validation box did trigger the change. I also noticed that the spelling in "Choice" was Case Sensitive...Just make sure that all of your characters & spaces are identical between the validation list & the VB code.