Hiding OLEObjects based on data validation on a different worksheet

AwesomeSteph

Board Regular
Joined
Aug 18, 2017
Messages
80
Hi I have run into a roadblock that I just can't figure out, I would really appreciate any help I can get.

On sheet 2 in B2 there is a data validation drop down list that has 6 different options. What I need to happen is that on sheets 6, 8, and 9 for an embedded word doc "Object 1" to be visible unless one specific option is chosen lets call it "XYZ" in that case I need "Object 2" to be visible and "Object 1" to not be visible. So far what I have works for the first part but the second part that should be anything else other then "XYZ" does not hide object 2 and both are visible.

Sheet2 B2 = XYZ = only object 2 visible object 1 hidden
Sheet2 B2 = not XYZ = only object 1 visible object 2 hidden

Please help.

<code><code>
Private Sub Worksheet_SelectionChange(ByVal target As Range)
Application.ScreenUpdating = False

If Sheet2.Range("B2") = "XYZ" Then
ActiveSheet.OLEObjects("Object 1").Visible = False
ActiveSheet.OLEObjects("Object 2").Visible = True
Else
ActiveSheet.OLEObjects("Object 1").Visible = True
ActiveSheet.OLEObjects("Object 2").Visible = False
End If


Application.ScreenUpdating = True
End Sub
</code></code>
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Watch MrExcel Video

Forum statistics

Threads
1,109,493
Messages
5,529,187
Members
409,856
Latest member
MAO
Top