excel VBA unhide shape when cell containing name of shape is selected

mperrah

Board Regular
Joined
Mar 12, 2005
Messages
62
Hello,
I have a sheet that is basically a map. I have a drop down that now unhides a shape with its matching name. The shapes are lines showing path from one cell to another.
One of the cells contains the name of a shape.
When I click the Cell I'd like the corresponding shape to unhide. and others of same type to hide.
The shape names either start with a T or an R.
When i click a T shape hide all other T shapes and show the one that name its matches the value of the selected cell.
This is the code that reads the drop down value.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim tShow As Variant
Dim rShow As Variant
Dim tbl As Worksheet
Dim cMp As Worksheet

Set tbl = Worksheets("Tables")
Set cMp = Worksheets("Campus")

' unhide shape for tank path
    If Target.Address = "$AC$5" Then
        Call HideTanks
        tShow = tbl.Range(CStr("$M$2")).Value
        cMp.Shapes.Range(CStr(tShow)).Visible = True
    End If

' unhide reactor shape path
    If Target.Address = "$AJ$5" Then
        Call HideReactors
        rShow = tbl.Range(CStr("$K$2")).Value
        cMp.Shapes.Range(CStr(rShow)).Visible = True
    End If
    
End Sub

This is how I hide the shapes that start with "T"

VBA Code:
Sub HideTanks()
Dim tbl As Worksheet
Dim cMp As Worksheet
Dim sHp As Shape
Dim tNk As Variant
Dim i As Variant

    Set tbl = Worksheets("Tables")
    Set cMp = Worksheets("Campus")
    
    tNk = tbl.Range("J2:J" & tbl.Range("J" & Rows.Count).End(xlUp).Row)

    With cMp
         For Each i In tNk
            cMp.Shapes.Range(CStr(i)).Visible = False
        Next i
    End With
End Sub

This code updates values in a cell based on clicked items.
I have an attempt at code to capture the active cell value to pass to the shape unhide step: not working and commented out.

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim tbl As Worksheet
Dim cMp As Worksheet
Dim vShow As Variant

Set tbl = Worksheets("Tables")
Set cMp = Worksheets("Campus")

    ' press clear button cell
    If Selection.Count = 1 Then
        ' Clear All
        If Not Intersect(Target, Range("$AC$2")) Is Nothing Then
            Call HideEachShape
        ' Clear Tanks
        ElseIf Not Intersect(Target, Range("$AF$2")) Is Nothing Then
            Call HideTanks
        ' Clear Reactors
        ElseIf Not Intersect(Target, Range("$AK$2")) Is Nothing Then
            Call HideReactors
        End If
    
    On Error GoTo ws_exit
    Application.EnableEvents = False
    
    'If Target.Address = "$AC$5" Then
        ' Call HideTanks
        'vShow = cMp.Range(CStr(ActiveCell)).Value
        'cMp.Shapes.Range(CStr(vShow)).Visible = True
    ' End If
    
    ' check for click in column AQ Tank equipment items
    If Target.Column = 43 And _
        Target.Row > 7 Then
        If Target.Value <> "" Then
            Range("AC8").Value = ActiveCell.Value  ' rewrite in AC8 what ever is clicked - named cell aLoc
        End If
    End If
    
    ' Check for click in column AS Reactor equipment items
    If Target.Column = 45 And _
        Target.Row > 7 Then
        If Target.Value <> "" Then
            Range("AJ8").Value = ActiveCell.Value  ' rewrite in cell AK8 what ever is clicked - named cell bLoc
        End If
    End If
End If
ws_exit: Application.EnableEvents = True

End Sub

Any advice is greatly appreciated.
Thank you,
Mark
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mperrah

Board Regular
Joined
Mar 12, 2005
Messages
62
I have a table I reference for a formula to get the value from the drop down and return the Shape name. I added a cell address to the corresponding to the shape and cell the shape name is in to this named table. The table name is vTbl and the cell address reference is the 3rd column in the table, I have that column named vLoc as its own named range if this might help.
 

mperrah

Board Regular
Joined
Mar 12, 2005
Messages
62
Maybe something like this? This errors on Intersect says "cant be optional"
I can imagine the code might cause a lag,
but can "on selection" we loop through the vTgt array and if the" selected cell" finds a match then unhide its matching named Shape...
VBA Code:
    vTgt = tbl.Range("$S:$S33")
    
    If Not Intersect(Target.Range(vTgt)) Is Nothing Then
        vShow = cMp.Range(vTgt).Value
        cMp.Shapes.Range(CStr(vShow)).Visible = True
    End If
 

mperrah

Board Regular
Joined
Mar 12, 2005
Messages
62
I got, hope i didnt wast anyones time. Thank you
VBA Code:
    With tbl
        tRng = .Range("$J$2:$J$23")
        rRng = .Range("$G$2:$G$11")
        
    End With
    
    For Each x In tRng
        If ActiveCell.Value = x Then
        'MsgBox x
            Call HideTanks
            cMp.Shapes.Range(CStr(x)).Visible = True
        End If
    Next x
    
    For Each i In rRng
        If ActiveCell.Value = i Then
        'MsgBox i
            Call HideReactors
            cMp.Shapes.Range(CStr(i)).Visible = True
        End If
    Next i
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,493
Members
417,029
Latest member
lingx86

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
Top