Data validation dropdown macro to hide/unhide column (only one) on another worksheet in the same workbook

river said:

I'm pretty new to vba macros but I need help with a basic issue.
I have a worksheet named "Setup" with a data validation list containing the options "Yes" and "No".
Based on the selection "No" I would like to have a single column (say, AM) on a different worksheet named "Viewer" made hidden. If "Yes" is selected, the same column would be kept unhidden.

Click to expand...

What cell on the sheet Setup is the data validation list in?

Thanks for responding, JoeMo!
The cell is a merged range from V9 to AF9.

river said:

Thanks for responding, JoeMo!
The cell is a merged range from V9 to AF9.

Click to expand...

This is code for the sheet Setup.

To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Joe:
Thanks for replying so promptly about my macro issue. Unfortunately, the macro didn't run. It passed the debugging test, tho. There is also another macro on the same page as the validation list to hide/unhide all sheets but "Viewer", "Setup, and another page that is executed with a checkbox.

river said:

Joe:
Thanks for replying so promptly about my macro issue. Unfortunately, the macro didn't run. It passed the debugging test, tho. There is also another macro on the same page as the validation list to hide/unhide all sheets but "Viewer", "Setup, and another page that is executed with a checkbox.

Click to expand...

That's the kind of information you should supply on your initial post. Can you post the other macro that's on the sheet Setup?

Joe:
I renamed the two sheets "Summary" with the column AM that I wish to hide, and "Config" that has the data validation list with only two values: "Yes" and "No".

Sub Workbook_SheetChange(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("V9")) Is Nothing Then
If Target.Value = "No" Then
Sheets("Summary").Columns("AM").Hidden = True
Else
Sheets("Summary").Columns("AM").Hidden = False
End If

End Sub




Sub CheckBox1_Click()

If CheckBox1.Value = True Then
Sheets("Summary").Visible = True
Sheets("HeatMap").Visible = True
Sheets("Config").Visible = True
Sheets("Instructions").Visible = False
Sheets("Service").Visible = False
Sheets("Callouts").Visible = False
Sheets("Aging").Visible = False
Sheets("Expenditures").Visible = False
Sheets("Capital Planning").Visible = False
Sheets("Service - Pivot").Visible = False
Sheets("Callouts - Pivot").Visible = False
Sheets("Aging - Pivot").Visible = False
Sheets("Expenditures - Pivot").Visible = False
Sheets("Capital - Pivot").Visible = False
Sheets("Delete Me").Visible = False
Sheets("Equipment List").Visible = False
Sheets("Lookups").Visible = False
ActiveWindow.ScrollWorkbookTabs Sheets:=-2
Sheets("Summary").Select

Else
Sheets("Summary").Visible = True
Sheets("HeatMap").Visible = True
Sheets("Config").Visible = True
Sheets("Instructions").Visible = True
Sheets("Service").Visible = True
Sheets("Callouts").Visible = True
Sheets("Aging").Visible = True
Sheets("Expenditures").Visible = True
Sheets("Capital Planning").Visible = True
Sheets("Service - Pivot").Visible = True
Sheets("Callouts - Pivot").Visible = True
Sheets("Aging - Pivot").Visible = True
Sheets("Expenditures - Pivot").Visible = True
Sheets("Capital - Pivot").Visible = True
Sheets("Delete Me").Visible = True
Sheets("Equipment List").Visible = True
Sheets("Lookups").Visible = True

End If

End Sub

river said:

Joe:
I renamed the two sheets "Summary" with the column AM that I wish to hide, and "Config" that has the data validation list with only two values: "Yes" and "No".

Sub Workbook_SheetChange(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("V9")) Is Nothing Then
If Target.Value = "No" Then
Sheets("Summary").Columns("AM").Hidden = True
Else
Sheets("Summary").Columns("AM").Hidden = False
End If

End Sub

Click to expand...

That's not the code I posted and the red bit doesn't look right to me. Why have you made this change?

Joe:
Sorry but I'm working on two computers, one of which is mine and has a wireless connection, and the company's computer which for the time being doesn't have internet access. System problems. So something went wrong on the copy-paste-save to flash drive, etc.
Here is the adjusted syntax. If it's correct it should only be minus the private designation. It still isn't working.

Sub Workbook_SheetChange(ByVal Target As Range)
If Not Intersect(Target, Range("V9")) Is Nothing Then
If Target.Value = "No" Then
Sheets("Summary").Columns("AM").Hidden = True
Else
Sheets("Summary").Columns("AM").Hidden = False
End If
End Sub

river said:

Joe:
Sorry but I'm working on two computers, one of which is mine and has a wireless connection, and the company's computer which for the time being doesn't have internet access. System problems. So something went wrong on the copy-paste-save to flash drive, etc.
Here is the adjusted syntax. If it's correct it should only be minus the private designation. It still isn't working.

Sub Workbook_SheetChange(ByVal Target As Range)
If Not Intersect(Target, Range("V9")) Is Nothing Then
If Target.Value = "No" Then
Sheets("Summary").Columns("AM").Hidden = True
Else
Sheets("Summary").Columns("AM").Hidden = False
End If
End Sub

Click to expand...

That's because you didn't copy/paste the code into the VBE. What you show here is not what I gave you. Go back to post #2 and follow the instructions there exactly as I posted them for whatever the sheet is that has your validation drop down in it.