Code to make cells appear based on list selection

E

ExcelQs

New Member
Joined
Jul 12, 2012
Messages
22
Hi. I have a Data Validation list in a cell. I want some cells to appear once a choice is picked from the list. This is the code I have so far. I get the error "Invalid procedure call or argument" and the macro isn't triggered by picking a value from the list.

Sub SearchRange()
If Cells("6:6").Value = True Then
Columns("E:G").EntireColumn.Hidden = False

Else
Selection.EntireColumn.Hidden = True
End If

End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
ExcelQs said:
Hi. I have a Data Validation list in a cell. I want some cells to appear once a choice is picked from the list. This is the code I have so far. I get the error "Invalid procedure call or argument" and the macro isn't triggered by picking a value from the list.

Sub SearchRange()
If Cells("6:6").Value = True Then
Columns("E:G").EntireColumn.Hidden = False
Else
Selection.EntireColumn.Hidden = True
End If

End Sub
Click to expand...

Cells("6:6") is not a valid cell reference.

Try something like below. This code has to go into the worksheet's code mode for it to automatically run when you make a change to the data validation list in cell F6 (change to suit).


  • Right-click on the sheet tab
  • Select View Code from the pop-up menu
  • Paste the code below in the worksheet code module

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = [COLOR=red]"F6" [/COLOR][COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Target.Value = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
            Columns("E:G").EntireColumn.Hidden = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
            Selection.EntireColumn.Hidden = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

You may have to also change the If statement. It's not clear what you want to do once the data validation list is changed.
 
Upvote 0
AlphaFrog said:
Cells("6:6") is not a valid cell reference.

Try something like below. This code has to go into the worksheet's code mode for it to automatically run when you make a change to the data validation list in cell F6 (change to suit).

  • Right-click on the sheet tab
  • Select View Code from the pop-up menu
  • Paste the code below in the worksheet code module

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = [COLOR=red]"F6" [/COLOR][COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Target.Value = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
            Columns("E:G").EntireColumn.Hidden = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
            Selection.EntireColumn.Hidden = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

You may have to also change the If statement. It's not clear what you want to do once the data validation list is changed.
Click to expand...

Thanks. Ok so I made a slight change to the code in the Else statement. Right now it does the opposite of what I want to do. If I pick a value from the list, the columns are hidden, instead of being shown. Also, if I make the cell blank, the columns arent hidden.


Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = [COLOR=red]"C6" [/COLOR][COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Target.Value = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
            Columns("E:G").EntireColumn.Hidden = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
            Columns("E:G").EntireColumn.Hidden = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
You could just swap the =False and =True or try this...

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = "C6" [COLOR=darkblue]Then[/COLOR]
        Columns("E:G").EntireColumn.Hidden = Len(Target) = 0
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
VBA Question
Replies
3
Views
128
RoryA
RoryA
C
  • Solved
Make Message Box Only Appear Once
Replies
6
Views
131
Joe4
Joe4
Kristylee0228
  • Question
Populating Cell based on File Name and using a List
Replies
0
Views
185
Kristylee0228
Kristylee0228
A
  • Question
After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"
Replies
1
Views
179
Ark68
A
sparky2205
  • Solved
Set default selections on a List Box on Workbook_Open()
Replies
3
Views
118
Domenic
D

Forum statistics

Threads
1,215,064
Messages
6,122,942
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top