CODEWITHSUNDEEP
vbaexcel
Twi
Twi

Reputation: 825

Best way to automatic buttons hiding in VBA

I am curious, that what is the best way to hide/inactivate a button/shape in Excel, depending a value of a cell.

For example: If "A1" cell value is 1, I would like to see the first active button/shape on Sheet2. If "A1" value is 2, I would like to see first two active button/shape on Sheet2 and so on.

What is the best way to handle it? I would rather not hardcode it like that:

            Select Case A1Value
                Case Is = 1
                    With Sheet1.Shapes("Rounded Rectangle 1")
                        .Visible = msoTrue
                    With Sheet1.Shapes("Rounded Rectangle 2")
                        .Visible = msoFalse
                    With Sheet1.Shapes("Rounded Rectangle 3")
                        .Visible = msoFalse
                    With Sheet1.Shapes("Rounded Rectangle 4")
                        .Visible = msoFalse
                Case Is = 2
                    With Sheet1.Shapes("Rounded Rectangle 1")
                        .Visible = msoTrue
                    With Sheet1.Shapes("Rounded Rectangle 2")
                        .Visible = msoTrue
                    With Sheet1.Shapes("Rounded Rectangle 3")
                        .Visible = msoFalse
                    With Sheet1.Shapes("Rounded Rectangle 4")
                        .Visible = msoFalse
                Case Is = 3
                    With Sheet1.Shapes("Rounded Rectangle 1")
                        .Visible = msoTrue
                    With Sheet1.Shapes("Rounded Rectangle 2")
                        .Visible = msoTrue
                    With Sheet1.Shapes("Rounded Rectangle 3")
                        .Visible = msoTrue
                    With Sheet1.Shapes("Rounded Rectangle 4")
                        .Visible = msoFalse
                Case Is = 4
                    With Sheet1.Shapes("Rounded Rectangle 1")
                        .Visible = msoTrue
                    With Sheet1.Shapes("Rounded Rectangle 2")
                        .Visible = msoTrue
                    With Sheet1.Shapes("Rounded Rectangle 3")
                        .Visible = msoTrue
                    With Sheet1.Shapes("Rounded Rectangle 4")
                        .Visible = msoTrue
            End Select

Upvotes: 1

Views: 274

Answers (3)

Ralph
Ralph

Reputation: 9444

While I do agree with you that it should be avoided to "hard code" something like this you have forgotten to give us an alternative. What could be an alternative to hard code the name? Are the shapes in a particular place on the sheet? Do they have a particular size?

If not, then you are stuck with their name and @TorbenKlein has given you a nice example of how you could work it out with a little less code. Yet, I'd like to throw in these few extra lines for consideration:

Public Sub ShowEm()

Dim shp As Shape

For Each shp In Worksheets("Sheet1").Shapes
    If shp.AutoShapeType = msoShapeRoundedRectangle Then
        ' ... and yet again we are stuck with the name of the shape ...
        Select Case Val(Trim(Replace(Replace(shp.Name, "Rounded", ""), "Rectangle", ""))) = Worksheets("Sheet1").Range("A1").Value2
        Case True
            MsgBox "gotacha!"
        Case False
            Debug.Print "you are not the one"
        End Select
        ' ... alternatively you can select them by placement (where they are on the sheet
        Debug.Print shp.TopLeftCell.Address
    End If

Next shp

End Sub

Upvotes: 1

AndyW
AndyW

Reputation: 440

The following steps through each shape on the sheet, ignores anything not starting with Rounded Rectangle and sets any Rounded Rectangle with a rightmost number not greater than that of the supplied value to visible. Note this will not work for values of Rounded Rectangle with more than 1 digit.

For each myShape in Sheet1.Shapes
   if myShape.Name Like "Rounded Rectangle *" then
       if Cint(Right(myShape.Name,1)) !> a1Value then
           myShape.visible= msoTrue
       end if
    end if
next

Upvotes: 1

Torben Klein
Torben Klein

Reputation: 3136

For each shp in Sheet1.Shapes
    If shp.Name Like "Rounded Rectangle *" Then
        shp.Visible = (shp.Name = "Rounded Rectangle " & A1Value)
    End If
Next

Explanation: Loops over all Shapes in the Worksheet; Looks only at the shapes with a name like "Rounded Rectangle" and sets visibility based on the expression in parentheses (which yields either TRUE or FALSE).

Also note that you can give each shape a custom name, by using the Name box (left of the formula box above the cells).

Upvotes: 1

Related Questions