How to hide shapes in VBA based on a particular cell with Data validation

On which sheet you want to hide the shape, the one which contains the code or the Calculation Sheet?

The below code will hide the shape from the sheet where I15 is being changed to Yes or No.
Place the below code on the Sheet which has a drop down in I15 to select Yes or No, I guess it should be Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Dim myshape As Shape
Set dws = Sheets("Calculation")
If Target.Address(0, 0) = "I15" Then
    For Each myshape In ActiveSheet.Shapes
        If myshape.Type = 1 Then
            myshape.Visible = True
            Exit For
        End If
    Next myshape
    If Target.Value = "No" Then myshape.Visible = False
End If
End Sub

Select allOpen in new window

The above code is for Sheet Change Event and you cannot have two similar codes on the same sheet module.
So if you are having the previous code from your previous question on the same sheet, let me know then, it will need to be tweaked.

Thanks again.  Yes, it's on the same sheet as the previous one, also I don't need to make reference to another sheet as the shapes are on the active worksheet. Thanks

Also, do you know how to name buttons so I can hide some but not all of them under the same code please? I'd like to hide rows but I can't 'shift objects off sheet' so I need to hide some of the buttons too.  Thanks in advance.

So do you mean, the sheet1 will have the code for hiding the columns on Calculation Sheet and hiding the shapes on Sheet1?

What types of buttons they are? Form controls or ActiveX command buttons?

Sorry, forget Calculation in this case.  The shapes are on the page I want to add the code to (e.g. Sheet1) so I don't need to reference another page.
I do already have the similar code on that page's code though.
I'm 90% sure they're Form controls.

If the button is a form control button, you can rename it by right clicking it and pressing Esc key then to exit from the right click context menu which appears because you right clicked on the button, then in the Name Box, the box left to the formula bar and just above the column letter A where you see the address of the selected cell on the sheet, click in there and type another name for your form control button. By default you will see Button 1, Button 2 etc in the Name Box.
Once you give a name to the form control button, you can access the button by it's name. By the way if this is your first button inserted on the sheet, it will be named by default as Button 1 and if this is the case, you can access it by it's name Button 1 also.

You are supposed to place the below code on Sheet1 module after removing any existing code which starts with a line like this...
Private Sub Worksheet_Change(ByVal Target As Range)
Remove any such existing code and then paste the code given below onto the same code window.

The code will hide the columns on Calculation Sheet as per the selection made in I17 on Sheet1 and hide the button which is named as "myButton" and place on Sheet1 itself as per the selection made in I15 on Sheet1.

I hope I have made all points clear enough. Though I am attaching here a sample workbook for your reference so that you know what should be the code setup in the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Set dws = Sheets("Calculation")
If Target.Address(0, 0) = "I17" Then
    dws.Columns("Q:V").Hidden = False
    Application.EnableEvents = False
    If Range("I17").Value = "2-5 years" Then
       dws.Columns("Q:V").Hidden = True
    ElseIf Range("I17").Value = "2-6 years" Then
       dws.Columns("T:V").Hidden = True
    End If
    Application.EnableEvents = True
ElseIf Target.Address(0, 0) = "I15" Then
    For Each myshape In ActiveSheet.Shapes
        If myshape.Name = "myButton" Then
            myshape.Visible = True
            Exit For
        End If
    Next myshape
    If Target.Value = "No" Then myshape.Visible = False
End If
End Sub

Select allOpen in new window

Button-Example.xlsm

Thank you @Subodh Tiwari (Neeraj).
How do I hide multiple buttons please? Is there any way of naming multiple buttons with the same name for convenience of coding or do they all have to be different?

You're welcome.

You cannot have two objects with the same name.
You will have to adopt a different approach and since this is completely different scenario, I would suggest to open a new question describing your requirement correctly.

Thanks.  Is it not as simple as adding another line for each button as follows?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Set dws = Sheets("Calculation")
If Target.Address(0, 0) = "I17" Then
    dws.Columns("Q:V").Hidden = False
    Application.EnableEvents = False
    If Range("I17").Value = "2-5 years" Then
       dws.Columns("Q:V").Hidden = True
    ElseIf Range("I17").Value = "2-6 years" Then
       dws.Columns("T:V").Hidden = True
    End If
    Application.EnableEvents = True
ElseIf Target.Address(0, 0) = "I15" Then
    For Each myshape In ActiveSheet.Shapes
        If myshape.Name = "myButton" Then
        If myshape.Name = "Button 18" Then
        If myshape.Name = "Button 19" Then
            myshape.Visible = True
            Exit For
        End If
    Next myshape
    If Target.Value = "No" Then myshape.Visible = False
End If
End Sub

Everything is easy James if we know your workbook setup that will give us idea about how many buttons are there, how are they named and which are to to be deleted. :)

It would be better if you can upload a sample workbook along with your requirement clearly.

Thanks, I have attached.  I'd like the selection 'No' in I15 to hide all the shapes (buttons / arrows) except for Button3.
Thanks in advance.

File
Book1-JC.xlsm

When do you want to hide those buttons and arrows, when you select "Yes" in I15 or select "No" in I15?

Sorry. You already answered that. :)

Thank you, that works great!
I've got one more (final) question about hiding worksheets within an object that already has a worksheet change code within it but I'll post another question.
Thanks again!!

You're welcome James! Glad your issue is resolved. :)

I can't seem to get this right - I want to hide rows 16:20 when I15 is "No" within this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Dim myShape As Shape
Set dws = Sheets("Calculation")


If Target.Address(0, 0) = "I17" Then
    dws.Columns("Q:V").Hidden = False
    Application.EnableEvents = False
    If Range("I17").Value = "2-5 years" Then
       dws.Columns("Q:V").Hidden = True
    ElseIf Range("I17").Value = "2-6 years" Then
       dws.Columns("T:V").Hidden = True
    End If
    
    Application.EnableEvents = True
    
    
ElseIf Target.Address(0, 0) = "I15" Then
    For Each myShape In ActiveSheet.Shapes
        myShape.Visible = True
    Next myShape
    If Target = "No" Then
        For Each myShape In ActiveSheet.Shapes
            Select Case myShape.Name
                Case "Buttons"
                Case "Buttons1"
                Case Else
                    myShape.Visible = False
            End Select
        Next myShape
    End If
    
    If Target.Address(0, 0) = "I15" Then
    If Range("I15").Value = "No" Then
    Rows("16:20").EntireRow.Hidden = True
    Application.EnableEvents = True
End If
  
    Application.EnableEvents = True
End If
End If
End Sub

Select allOpen in new window