Reducing redundant code

T

TweetingCynical

New Member
Joined
Apr 29, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. MacOS
Hi All,

I am a relative novice to vba. I am wondering if someone might help me reduce my code as I know an awful lot of it is just making the engine repeat things it's already done. Your help is greatly appreciated:

VBA Code: 
Sub Show1000()

ActiveSheet.Shapes("1000x9").Visible = True
ActiveSheet.Shapes("1000x8").Visible = True
ActiveSheet.Shapes("1000x7").Visible = True
ActiveSheet.Shapes("1000x6").Visible = True
ActiveSheet.Shapes("1000x5").Visible = True
ActiveSheet.Shapes("1000x4").Visible = True
ActiveSheet.Shapes("1000x3").Visible = True
ActiveSheet.Shapes("1000x2").Visible = True
ActiveSheet.Shapes("1000x1").Visible = True
ActiveSheet.Shapes("P1000x9").Visible = True
ActiveSheet.Shapes("P1000x8").Visible = True
ActiveSheet.Shapes("P1000x7").Visible = True
ActiveSheet.Shapes("P1000x6").Visible = True
ActiveSheet.Shapes("P1000x5").Visible = True
ActiveSheet.Shapes("P1000x4").Visible = True
ActiveSheet.Shapes("P1000x3").Visible = True
ActiveSheet.Shapes("P1000x2").Visible = True
ActiveSheet.Shapes("P1000x1").Visible = True

End Sub

Sub Show100()

ActiveSheet.Shapes("100x9").Visible = True
ActiveSheet.Shapes("100x8").Visible = True
ActiveSheet.Shapes("100x7").Visible = True
ActiveSheet.Shapes("100x6").Visible = True
ActiveSheet.Shapes("100x5").Visible = True
ActiveSheet.Shapes("100x4").Visible = True
ActiveSheet.Shapes("100x3").Visible = True
ActiveSheet.Shapes("100x2").Visible = True
ActiveSheet.Shapes("100x1").Visible = True
ActiveSheet.Shapes("P100x9").Visible = True
ActiveSheet.Shapes("P100x8").Visible = True
ActiveSheet.Shapes("P100x7").Visible = True
ActiveSheet.Shapes("P100x6").Visible = True
ActiveSheet.Shapes("P100x5").Visible = True
ActiveSheet.Shapes("P100x4").Visible = True
ActiveSheet.Shapes("P100x3").Visible = True
ActiveSheet.Shapes("P100x2").Visible = True
ActiveSheet.Shapes("P100x1").Visible = True

End Sub

Sub Show10()

ActiveSheet.Shapes("10x9").Visible = True
ActiveSheet.Shapes("10x8").Visible = True
ActiveSheet.Shapes("10x7").Visible = True
ActiveSheet.Shapes("10x6").Visible = True
ActiveSheet.Shapes("10x5").Visible = True
ActiveSheet.Shapes("10x4").Visible = True
ActiveSheet.Shapes("10x3").Visible = True
ActiveSheet.Shapes("10x2").Visible = True
ActiveSheet.Shapes("10x1").Visible = True
ActiveSheet.Shapes("P10x9").Visible = True
ActiveSheet.Shapes("P10x8").Visible = True
ActiveSheet.Shapes("P10x7").Visible = True
ActiveSheet.Shapes("P10x6").Visible = True
ActiveSheet.Shapes("P10x5").Visible = True
ActiveSheet.Shapes("P10x4").Visible = True
ActiveSheet.Shapes("P10x3").Visible = True
ActiveSheet.Shapes("P10x2").Visible = True
ActiveSheet.Shapes("P10x1").Visible = True

End Sub

Sub Show1()

ActiveSheet.Shapes("1x9").Visible = True
ActiveSheet.Shapes("1x8").Visible = True
ActiveSheet.Shapes("1x7").Visible = True
ActiveSheet.Shapes("1x6").Visible = True
ActiveSheet.Shapes("1x5").Visible = True
ActiveSheet.Shapes("1x4").Visible = True
ActiveSheet.Shapes("1x3").Visible = True
ActiveSheet.Shapes("1x2").Visible = True
ActiveSheet.Shapes("1x1").Visible = True
ActiveSheet.Shapes("P1x9").Visible = True
ActiveSheet.Shapes("P1x8").Visible = True
ActiveSheet.Shapes("P1x7").Visible = True
ActiveSheet.Shapes("P1x6").Visible = True
ActiveSheet.Shapes("P1x5").Visible = True
ActiveSheet.Shapes("P1x4").Visible = True
ActiveSheet.Shapes("P1x3").Visible = True
ActiveSheet.Shapes("P1x2").Visible = True
ActiveSheet.Shapes("P1x1").Visible = True

End Sub


Sub Hide_1000()

Worksheets("Representations").Select
Show1000
Range("Q1").Select

If ActiveCell.Value = 9 Then
    ActiveSheet.Shapes("1000x9").Visible = True
    ActiveSheet.Shapes("P1000x9").Visible = True
    ElseIf ActiveCell.Value = 8 Then
        ActiveSheet.Shapes("1000x9").Visible = False
        ActiveSheet.Shapes("P1000x9").Visible = True
        ElseIf ActiveCell.Value = 7 Then
            ActiveSheet.Shapes("1000x9").Visible = False
            ActiveSheet.Shapes("1000x8").Visible = False
            ActiveSheet.Shapes("P1000x9").Visible = False
            ActiveSheet.Shapes("P1000x8").Visible = False
            ElseIf ActiveCell.Value = 6 Then
                ActiveSheet.Shapes("1000x9").Visible = False
                ActiveSheet.Shapes("1000x8").Visible = False
                ActiveSheet.Shapes("1000x7").Visible = False
                ActiveSheet.Shapes("P1000x9").Visible = False
                ActiveSheet.Shapes("P1000x8").Visible = False
                ActiveSheet.Shapes("P1000x7").Visible = False
                    ElseIf ActiveCell.Value = 5 Then
                        ActiveSheet.Shapes("1000x9").Visible = False
                        ActiveSheet.Shapes("1000x8").Visible = False
                        ActiveSheet.Shapes("1000x7").Visible = False
                        ActiveSheet.Shapes("1000x6").Visible = False
                        ActiveSheet.Shapes("P1000x9").Visible = False
                        ActiveSheet.Shapes("P1000x8").Visible = False
                        ActiveSheet.Shapes("P1000x7").Visible = False
                        ActiveSheet.Shapes("P1000x6").Visible = False
                            ElseIf ActiveCell.Value = 4 Then
                                ActiveSheet.Shapes("1000x9").Visible = False
                                ActiveSheet.Shapes("1000x8").Visible = False
                                ActiveSheet.Shapes("1000x7").Visible = False
                                ActiveSheet.Shapes("1000x6").Visible = False
                                ActiveSheet.Shapes("1000x5").Visible = False
                                ActiveSheet.Shapes("P1000x9").Visible = False
                                ActiveSheet.Shapes("P1000x8").Visible = False
                                ActiveSheet.Shapes("P1000x7").Visible = False
                                ActiveSheet.Shapes("P1000x6").Visible = False
                                ActiveSheet.Shapes("P1000x5").Visible = False
                                ElseIf ActiveCell.Value = 3 Then
                                    ActiveSheet.Shapes("1000x9").Visible = False
                                    ActiveSheet.Shapes("1000x8").Visible = False
                                    ActiveSheet.Shapes("1000x7").Visible = False
                                    ActiveSheet.Shapes("1000x6").Visible = False
                                    ActiveSheet.Shapes("1000x5").Visible = False
                                    ActiveSheet.Shapes("1000x4").Visible = False
                                    ActiveSheet.Shapes("P1000x9").Visible = False
                                    ActiveSheet.Shapes("P1000x8").Visible = False
                                    ActiveSheet.Shapes("P1000x7").Visible = False
                                    ActiveSheet.Shapes("P1000x6").Visible = False
                                    ActiveSheet.Shapes("P1000x5").Visible = False
                                    ActiveSheet.Shapes("P1000x4").Visible = False
                                    ElseIf ActiveCell.Value = 2 Then
                                        ActiveSheet.Shapes("1000x9").Visible = False
                                        ActiveSheet.Shapes("1000x8").Visible = False
                                        ActiveSheet.Shapes("1000x7").Visible = False
                                        ActiveSheet.Shapes("1000x6").Visible = False
                                        ActiveSheet.Shapes("1000x5").Visible = False
                                        ActiveSheet.Shapes("1000x4").Visible = False
                                        ActiveSheet.Shapes("1000x3").Visible = False
                                        ActiveSheet.Shapes("P1000x9").Visible = False
                                        ActiveSheet.Shapes("P1000x8").Visible = False
                                        ActiveSheet.Shapes("P1000x7").Visible = False
                                        ActiveSheet.Shapes("P1000x6").Visible = False
                                        ActiveSheet.Shapes("P1000x5").Visible = False
                                        ActiveSheet.Shapes("P1000x4").Visible = False
                                        ActiveSheet.Shapes("P1000x3").Visible = False
                                        ElseIf ActiveCell.Value = 1 Then
                                            ActiveSheet.Shapes("1000x9").Visible = False
                                            ActiveSheet.Shapes("1000x8").Visible = False
                                            ActiveSheet.Shapes("1000x7").Visible = False
                                            ActiveSheet.Shapes("1000x6").Visible = False
                                            ActiveSheet.Shapes("1000x5").Visible = False
                                            ActiveSheet.Shapes("1000x4").Visible = False
                                            ActiveSheet.Shapes("1000x3").Visible = False
                                            ActiveSheet.Shapes("1000x2").Visible = False
                                            ActiveSheet.Shapes("P1000x9").Visible = False
                                            ActiveSheet.Shapes("P1000x8").Visible = False
                                            ActiveSheet.Shapes("P1000x7").Visible = False
                                            ActiveSheet.Shapes("P1000x6").Visible = False
                                            ActiveSheet.Shapes("P1000x5").Visible = False
                                            ActiveSheet.Shapes("P1000x4").Visible = False
                                            ActiveSheet.Shapes("P1000x3").Visible = False
                                            ActiveSheet.Shapes("P1000x2").Visible = False
                                            ElseIf ActiveCell.Value = 0 Then
                                                ActiveSheet.Shapes("1000x9").Visible = False
                                                ActiveSheet.Shapes("1000x8").Visible = False
                                                ActiveSheet.Shapes("1000x7").Visible = False
                                                ActiveSheet.Shapes("1000x6").Visible = False
                                                ActiveSheet.Shapes("1000x5").Visible = False
                                                ActiveSheet.Shapes("1000x4").Visible = False
                                                ActiveSheet.Shapes("1000x3").Visible = False
                                                ActiveSheet.Shapes("1000x2").Visible = False
                                                ActiveSheet.Shapes("1000x1").Visible = False
                                                ActiveSheet.Shapes("P1000x9").Visible = False
                                                ActiveSheet.Shapes("P1000x8").Visible = False
                                                ActiveSheet.Shapes("P1000x7").Visible = False
                                                ActiveSheet.Shapes("P1000x6").Visible = False
                                                ActiveSheet.Shapes("P1000x5").Visible = False
                                                ActiveSheet.Shapes("P1000x4").Visible = False
                                                ActiveSheet.Shapes("P1000x3").Visible = False
                                                ActiveSheet.Shapes("P1000x2").Visible = False
                                                ActiveSheet.Shapes("P1000x1").Visible = False
                                            End If

                                                                            
End Sub

Sub Hide_100()

Worksheets("Representations").Select
Show100
Range("R1").Select

If ActiveCell.Value = 9 Then
    ActiveSheet.Shapes("100x9").Visible = True
    ActiveSheet.Shapes("P100x9").Visible = True
    ElseIf ActiveCell.Value = 8 Then
        ActiveSheet.Shapes("100x9").Visible = False
        ActiveSheet.Shapes("P100x9").Visible = True
        ElseIf ActiveCell.Value = 7 Then
            ActiveSheet.Shapes("100x9").Visible = False
            ActiveSheet.Shapes("100x8").Visible = False
            ActiveSheet.Shapes("P100x9").Visible = False
            ActiveSheet.Shapes("P100x8").Visible = False
            ElseIf ActiveCell.Value = 6 Then
                ActiveSheet.Shapes("100x9").Visible = False
                ActiveSheet.Shapes("100x8").Visible = False
                ActiveSheet.Shapes("100x7").Visible = False
                ActiveSheet.Shapes("P100x9").Visible = False
                ActiveSheet.Shapes("P100x8").Visible = False
                ActiveSheet.Shapes("P100x7").Visible = False
                    ElseIf ActiveCell.Value = 5 Then
                        ActiveSheet.Shapes("100x9").Visible = False
                        ActiveSheet.Shapes("100x8").Visible = False
                        ActiveSheet.Shapes("100x7").Visible = False
                        ActiveSheet.Shapes("100x6").Visible = False
                        ActiveSheet.Shapes("P100x9").Visible = False
                        ActiveSheet.Shapes("P100x8").Visible = False
                        ActiveSheet.Shapes("P100x7").Visible = False
                        ActiveSheet.Shapes("P100x6").Visible = False
                            ElseIf ActiveCell.Value = 4 Then
                                ActiveSheet.Shapes("100x9").Visible = False
                                ActiveSheet.Shapes("100x8").Visible = False
                                ActiveSheet.Shapes("100x7").Visible = False
                                ActiveSheet.Shapes("100x6").Visible = False
                                ActiveSheet.Shapes("100x5").Visible = False
                                ActiveSheet.Shapes("P100x9").Visible = False
                                ActiveSheet.Shapes("P100x8").Visible = False
                                ActiveSheet.Shapes("P100x7").Visible = False
                                ActiveSheet.Shapes("P100x6").Visible = False
                                ActiveSheet.Shapes("P100x5").Visible = False
                                ElseIf ActiveCell.Value = 3 Then
                                    ActiveSheet.Shapes("100x9").Visible = False
                                    ActiveSheet.Shapes("100x8").Visible = False
                                    ActiveSheet.Shapes("100x7").Visible = False
                                    ActiveSheet.Shapes("100x6").Visible = False
                                    ActiveSheet.Shapes("100x5").Visible = False
                                    ActiveSheet.Shapes("100x4").Visible = False
                                    ActiveSheet.Shapes("P100x9").Visible = False
                                    ActiveSheet.Shapes("P100x8").Visible = False
                                    ActiveSheet.Shapes("P100x7").Visible = False
                                    ActiveSheet.Shapes("P100x6").Visible = False
                                    ActiveSheet.Shapes("P100x5").Visible = False
                                    ActiveSheet.Shapes("P100x4").Visible = False
                                    ElseIf ActiveCell.Value = 2 Then
                                        ActiveSheet.Shapes("100x9").Visible = False
                                        ActiveSheet.Shapes("100x8").Visible = False
                                        ActiveSheet.Shapes("100x7").Visible = False
                                        ActiveSheet.Shapes("100x6").Visible = False
                                        ActiveSheet.Shapes("100x5").Visible = False
                                        ActiveSheet.Shapes("100x4").Visible = False
                                        ActiveSheet.Shapes("100x3").Visible = False
                                        ActiveSheet.Shapes("P100x9").Visible = False
                                        ActiveSheet.Shapes("P100x8").Visible = False
                                        ActiveSheet.Shapes("P100x7").Visible = False
                                        ActiveSheet.Shapes("P100x6").Visible = False
                                        ActiveSheet.Shapes("P100x5").Visible = False
                                        ActiveSheet.Shapes("P100x4").Visible = False
                                        ActiveSheet.Shapes("P100x3").Visible = False
                                        ElseIf ActiveCell.Value = 1 Then
                                            ActiveSheet.Shapes("100x9").Visible = False
                                            ActiveSheet.Shapes("100x8").Visible = False
                                            ActiveSheet.Shapes("100x7").Visible = False
                                            ActiveSheet.Shapes("100x6").Visible = False
                                            ActiveSheet.Shapes("100x5").Visible = False
                                            ActiveSheet.Shapes("100x4").Visible = False
                                            ActiveSheet.Shapes("100x3").Visible = False
                                            ActiveSheet.Shapes("100x2").Visible = False
                                            ActiveSheet.Shapes("P100x9").Visible = False
                                            ActiveSheet.Shapes("P100x8").Visible = False
                                            ActiveSheet.Shapes("P100x7").Visible = False
                                            ActiveSheet.Shapes("P100x6").Visible = False
                                            ActiveSheet.Shapes("P100x5").Visible = False
                                            ActiveSheet.Shapes("P100x4").Visible = False
                                            ActiveSheet.Shapes("P100x3").Visible = False
                                            ActiveSheet.Shapes("P100x2").Visible = False
                                            ElseIf ActiveCell.Value = 0 Then
                                                ActiveSheet.Shapes("100x9").Visible = False
                                                ActiveSheet.Shapes("100x8").Visible = False
                                                ActiveSheet.Shapes("100x7").Visible = False
                                                ActiveSheet.Shapes("100x6").Visible = False
                                                ActiveSheet.Shapes("100x5").Visible = False
                                                ActiveSheet.Shapes("100x4").Visible = False
                                                ActiveSheet.Shapes("100x3").Visible = False
                                                ActiveSheet.Shapes("100x2").Visible = False
                                                ActiveSheet.Shapes("100x1").Visible = False
                                                ActiveSheet.Shapes("P100x9").Visible = False
                                                ActiveSheet.Shapes("P100x8").Visible = False
                                                ActiveSheet.Shapes("P100x7").Visible = False
                                                ActiveSheet.Shapes("P100x6").Visible = False
                                                ActiveSheet.Shapes("P100x5").Visible = False
                                                ActiveSheet.Shapes("P100x4").Visible = False
                                                ActiveSheet.Shapes("P100x3").Visible = False
                                                ActiveSheet.Shapes("P100x2").Visible = False
                                                ActiveSheet.Shapes("P100x1").Visible = False
                                            End If

                                                                            
End Sub

Sub Hide_10()

Worksheets("Representations").Select
Show10
Range("S1").Select

If ActiveCell.Value = 9 Then
    ActiveSheet.Shapes("10x9").Visible = True
    ActiveSheet.Shapes("P10x9").Visible = True
    ElseIf ActiveCell.Value = 8 Then
        ActiveSheet.Shapes("10x9").Visible = False
        ActiveSheet.Shapes("P10x9").Visible = True
        ElseIf ActiveCell.Value = 7 Then
            ActiveSheet.Shapes("10x9").Visible = False
            ActiveSheet.Shapes("10x8").Visible = False
            ActiveSheet.Shapes("P10x9").Visible = False
            ActiveSheet.Shapes("P10x8").Visible = False
            ElseIf ActiveCell.Value = 6 Then
                ActiveSheet.Shapes("10x9").Visible = False
                ActiveSheet.Shapes("10x8").Visible = False
                ActiveSheet.Shapes("10x7").Visible = False
                ActiveSheet.Shapes("P10x9").Visible = False
                ActiveSheet.Shapes("P10x8").Visible = False
                ActiveSheet.Shapes("P10x7").Visible = False
                    ElseIf ActiveCell.Value = 5 Then
                        ActiveSheet.Shapes("10x9").Visible = False
                        ActiveSheet.Shapes("10x8").Visible = False
                        ActiveSheet.Shapes("10x7").Visible = False
                        ActiveSheet.Shapes("10x6").Visible = False
                        ActiveSheet.Shapes("P10x9").Visible = False
                        ActiveSheet.Shapes("P10x8").Visible = False
                        ActiveSheet.Shapes("P10x7").Visible = False
                        ActiveSheet.Shapes("P10x6").Visible = False
                            ElseIf ActiveCell.Value = 4 Then
                                ActiveSheet.Shapes("10x9").Visible = False
                                ActiveSheet.Shapes("10x8").Visible = False
                                ActiveSheet.Shapes("10x7").Visible = False
                                ActiveSheet.Shapes("10x6").Visible = False
                                ActiveSheet.Shapes("10x5").Visible = False
                                ActiveSheet.Shapes("P10x9").Visible = False
                                ActiveSheet.Shapes("P10x8").Visible = False
                                ActiveSheet.Shapes("P10x7").Visible = False
                                ActiveSheet.Shapes("P10x6").Visible = False
                                ActiveSheet.Shapes("P10x5").Visible = False
                                ElseIf ActiveCell.Value = 3 Then
                                    ActiveSheet.Shapes("10x9").Visible = False
                                    ActiveSheet.Shapes("10x8").Visible = False
                                    ActiveSheet.Shapes("10x7").Visible = False
                                    ActiveSheet.Shapes("10x6").Visible = False
                                    ActiveSheet.Shapes("10x5").Visible = False
                                    ActiveSheet.Shapes("10x4").Visible = False
                                    ActiveSheet.Shapes("P10x9").Visible = False
                                    ActiveSheet.Shapes("P10x8").Visible = False
                                    ActiveSheet.Shapes("P10x7").Visible = False
                                    ActiveSheet.Shapes("P10x6").Visible = False
                                    ActiveSheet.Shapes("P10x5").Visible = False
                                    ActiveSheet.Shapes("P10x4").Visible = False
                                    ElseIf ActiveCell.Value = 2 Then
                                        ActiveSheet.Shapes("10x9").Visible = False
                                        ActiveSheet.Shapes("10x8").Visible = False
                                        ActiveSheet.Shapes("10x7").Visible = False
                                        ActiveSheet.Shapes("10x6").Visible = False
                                        ActiveSheet.Shapes("10x5").Visible = False
                                        ActiveSheet.Shapes("10x4").Visible = False
                                        ActiveSheet.Shapes("10x3").Visible = False
                                        ActiveSheet.Shapes("P10x9").Visible = False
                                        ActiveSheet.Shapes("P10x8").Visible = False
                                        ActiveSheet.Shapes("P10x7").Visible = False
                                        ActiveSheet.Shapes("P10x6").Visible = False
                                        ActiveSheet.Shapes("P10x5").Visible = False
                                        ActiveSheet.Shapes("P10x4").Visible = False
                                        ActiveSheet.Shapes("P10x3").Visible = False
                                        ElseIf ActiveCell.Value = 1 Then
                                            ActiveSheet.Shapes("10x9").Visible = False
                                            ActiveSheet.Shapes("10x8").Visible = False
                                            ActiveSheet.Shapes("10x7").Visible = False
                                            ActiveSheet.Shapes("10x6").Visible = False
                                            ActiveSheet.Shapes("10x5").Visible = False
                                            ActiveSheet.Shapes("10x4").Visible = False
                                            ActiveSheet.Shapes("10x3").Visible = False
                                            ActiveSheet.Shapes("10x2").Visible = False
                                            ActiveSheet.Shapes("P10x9").Visible = False
                                            ActiveSheet.Shapes("P10x8").Visible = False
                                            ActiveSheet.Shapes("P10x7").Visible = False
                                            ActiveSheet.Shapes("P10x6").Visible = False
                                            ActiveSheet.Shapes("P10x5").Visible = False
                                            ActiveSheet.Shapes("P10x4").Visible = False
                                            ActiveSheet.Shapes("P10x3").Visible = False
                                            ActiveSheet.Shapes("P10x2").Visible = False
                                            ElseIf ActiveCell.Value = 0 Then
                                                ActiveSheet.Shapes("10x9").Visible = False
                                                ActiveSheet.Shapes("10x8").Visible = False
                                                ActiveSheet.Shapes("10x7").Visible = False
                                                ActiveSheet.Shapes("10x6").Visible = False
                                                ActiveSheet.Shapes("10x5").Visible = False
                                                ActiveSheet.Shapes("10x4").Visible = False
                                                ActiveSheet.Shapes("10x3").Visible = False
                                                ActiveSheet.Shapes("10x2").Visible = False
                                                ActiveSheet.Shapes("10x1").Visible = False
                                                ActiveSheet.Shapes("P10x9").Visible = False
                                                ActiveSheet.Shapes("P10x8").Visible = False
                                                ActiveSheet.Shapes("P10x7").Visible = False
                                                ActiveSheet.Shapes("P10x6").Visible = False
                                                ActiveSheet.Shapes("P10x5").Visible = False
                                                ActiveSheet.Shapes("P10x4").Visible = False
                                                ActiveSheet.Shapes("P10x3").Visible = False
                                                ActiveSheet.Shapes("P10x2").Visible = False
                                                ActiveSheet.Shapes("P10x1").Visible = False
                                            End If

                                                                            
End Sub

Sub Hide_1()

Worksheets("Representations").Select
Show1
Range("T1").Select

If ActiveCell.Value = 9 Then
    ActiveSheet.Shapes("1x9").Visible = True
    ActiveSheet.Shapes("P1x9").Visible = True
    ElseIf ActiveCell.Value = 8 Then
        ActiveSheet.Shapes("1x9").Visible = False
        ActiveSheet.Shapes("P1x9").Visible = True
        ElseIf ActiveCell.Value = 7 Then
            ActiveSheet.Shapes("1x9").Visible = False
            ActiveSheet.Shapes("1x8").Visible = False
            ActiveSheet.Shapes("P1x9").Visible = False
            ActiveSheet.Shapes("P1x8").Visible = False
            ElseIf ActiveCell.Value = 6 Then
                ActiveSheet.Shapes("1x9").Visible = False
                ActiveSheet.Shapes("1x8").Visible = False
                ActiveSheet.Shapes("1x7").Visible = False
                ActiveSheet.Shapes("P1x9").Visible = False
                ActiveSheet.Shapes("P1x8").Visible = False
                ActiveSheet.Shapes("P1x7").Visible = False
                    ElseIf ActiveCell.Value = 5 Then
                        ActiveSheet.Shapes("1x9").Visible = False
                        ActiveSheet.Shapes("1x8").Visible = False
                        ActiveSheet.Shapes("1x7").Visible = False
                        ActiveSheet.Shapes("1x6").Visible = False
                        ActiveSheet.Shapes("P1x9").Visible = False
                        ActiveSheet.Shapes("P1x8").Visible = False
                        ActiveSheet.Shapes("P1x7").Visible = False
                        ActiveSheet.Shapes("P1x6").Visible = False
                            ElseIf ActiveCell.Value = 4 Then
                                ActiveSheet.Shapes("1x9").Visible = False
                                ActiveSheet.Shapes("1x8").Visible = False
                                ActiveSheet.Shapes("1x7").Visible = False
                                ActiveSheet.Shapes("1x6").Visible = False
                                ActiveSheet.Shapes("1x5").Visible = False
                                ActiveSheet.Shapes("P1x9").Visible = False
                                ActiveSheet.Shapes("P1x8").Visible = False
                                ActiveSheet.Shapes("P1x7").Visible = False
                                ActiveSheet.Shapes("P1x6").Visible = False
                                ActiveSheet.Shapes("P1x5").Visible = False
                                ElseIf ActiveCell.Value = 3 Then
                                    ActiveSheet.Shapes("1x9").Visible = False
                                    ActiveSheet.Shapes("1x8").Visible = False
                                    ActiveSheet.Shapes("1x7").Visible = False
                                    ActiveSheet.Shapes("1x6").Visible = False
                                    ActiveSheet.Shapes("1x5").Visible = False
                                    ActiveSheet.Shapes("1x4").Visible = False
                                    ActiveSheet.Shapes("P1x9").Visible = False
                                    ActiveSheet.Shapes("P1x8").Visible = False
                                    ActiveSheet.Shapes("P1x7").Visible = False
                                    ActiveSheet.Shapes("P1x6").Visible = False
                                    ActiveSheet.Shapes("P1x5").Visible = False
                                    ActiveSheet.Shapes("P1x4").Visible = False
                                    ElseIf ActiveCell.Value = 2 Then
                                        ActiveSheet.Shapes("1x9").Visible = False
                                        ActiveSheet.Shapes("1x8").Visible = False
                                        ActiveSheet.Shapes("1x7").Visible = False
                                        ActiveSheet.Shapes("1x6").Visible = False
                                        ActiveSheet.Shapes("1x5").Visible = False
                                        ActiveSheet.Shapes("1x4").Visible = False
                                        ActiveSheet.Shapes("1x3").Visible = False
                                        ActiveSheet.Shapes("P1x9").Visible = False
                                        ActiveSheet.Shapes("P1x8").Visible = False
                                        ActiveSheet.Shapes("P1x7").Visible = False
                                        ActiveSheet.Shapes("P1x6").Visible = False
                                        ActiveSheet.Shapes("P1x5").Visible = False
                                        ActiveSheet.Shapes("P1x4").Visible = False
                                        ActiveSheet.Shapes("P1x3").Visible = False
                                        ElseIf ActiveCell.Value = 1 Then
                                            ActiveSheet.Shapes("1x9").Visible = False
                                            ActiveSheet.Shapes("1x8").Visible = False
                                            ActiveSheet.Shapes("1x7").Visible = False
                                            ActiveSheet.Shapes("1x6").Visible = False
                                            ActiveSheet.Shapes("1x5").Visible = False
                                            ActiveSheet.Shapes("1x4").Visible = False
                                            ActiveSheet.Shapes("1x3").Visible = False
                                            ActiveSheet.Shapes("1x2").Visible = False
                                            ActiveSheet.Shapes("P1x9").Visible = False
                                            ActiveSheet.Shapes("P1x8").Visible = False
                                            ActiveSheet.Shapes("P1x7").Visible = False
                                            ActiveSheet.Shapes("P1x6").Visible = False
                                            ActiveSheet.Shapes("P1x5").Visible = False
                                            ActiveSheet.Shapes("P1x4").Visible = False
                                            ActiveSheet.Shapes("P1x3").Visible = False
                                            ActiveSheet.Shapes("P1x2").Visible = False
                                            ElseIf ActiveCell.Value = 0 Then
                                                ActiveSheet.Shapes("1x9").Visible = False
                                                ActiveSheet.Shapes("1x8").Visible = False
                                                ActiveSheet.Shapes("1x7").Visible = False
                                                ActiveSheet.Shapes("1x6").Visible = False
                                                ActiveSheet.Shapes("1x5").Visible = False
                                                ActiveSheet.Shapes("1x4").Visible = False
                                                ActiveSheet.Shapes("1x3").Visible = False
                                                ActiveSheet.Shapes("1x2").Visible = False
                                                ActiveSheet.Shapes("1x1").Visible = False
                                                ActiveSheet.Shapes("P1x9").Visible = False
                                                ActiveSheet.Shapes("P1x8").Visible = False
                                                ActiveSheet.Shapes("P1x7").Visible = False
                                                ActiveSheet.Shapes("P1x6").Visible = False
                                                ActiveSheet.Shapes("P1x5").Visible = False
                                                ActiveSheet.Shapes("P1x4").Visible = False
                                                ActiveSheet.Shapes("P1x3").Visible = False
                                                ActiveSheet.Shapes("P1x2").Visible = False
                                                ActiveSheet.Shapes("P1x1").Visible = False
                                            End If

                                                                            
End Sub

Sub ProtectWB()
ThisWorkbook.Protect Password:="UKExceED", Structure:=True, Windows:=True
End Sub

Sub UnProtectWB()
ThisWorkbook.UnProtect Password:="UKExceED"
End Sub

Sub ShowAllCards()

ActiveSheet.Shapes("1000C").Visible = False
ActiveSheet.Shapes("100C").Visible = False
ActiveSheet.Shapes("10C").Visible = False
ActiveSheet.Shapes("1C").Visible = False
ActiveSheet.Shapes("TotalC").Visible = False
ActiveSheet.Buttons("ShowAllC").Visible = False
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("ShowTotal").Visible = False
ActiveSheet.Buttons("HideTotal").Visible = True
ActiveSheet.Buttons("Show1000C").Visible = False
ActiveSheet.Buttons("Hide1000C").Visible = True
ActiveSheet.Buttons("Show100C").Visible = False
ActiveSheet.Buttons("Hide100C").Visible = True
ActiveSheet.Buttons("Show10C").Visible = False
ActiveSheet.Buttons("Hide10C").Visible = True
ActiveSheet.Buttons("Show1C").Visible = False
ActiveSheet.Buttons("Hide1C").Visible = True

End Sub

Sub ShowAllImages()

ActiveSheet.Shapes("1000R").Visible = False
ActiveSheet.Shapes("100R").Visible = False
ActiveSheet.Shapes("10R").Visible = False
ActiveSheet.Shapes("1R").Visible = False
ActiveSheet.Buttons("ShowAllR").Visible = False
ActiveSheet.Buttons("HideAllR").Visible = True
ActiveSheet.Buttons("Show1000R").Visible = False
ActiveSheet.Buttons("Hide1000R").Visible = True
ActiveSheet.Buttons("Show100R").Visible = False
ActiveSheet.Buttons("Hide100R").Visible = True
ActiveSheet.Buttons("Show10R").Visible = False
ActiveSheet.Buttons("Hide10R").Visible = True
ActiveSheet.Buttons("Show1R").Visible = False
ActiveSheet.Buttons("Hide1R").Visible = True

End Sub

Sub HideAllCards()

ActiveSheet.Shapes("1000C").Visible = True
ActiveSheet.Shapes("100C").Visible = True
ActiveSheet.Shapes("10C").Visible = True
ActiveSheet.Shapes("1C").Visible = True
ActiveSheet.Shapes("TotalC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = False
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("ShowTotal").Visible = True
ActiveSheet.Buttons("HideTotal").Visible = False
ActiveSheet.Buttons("Show1000C").Visible = True
ActiveSheet.Buttons("Hide1000C").Visible = False
ActiveSheet.Buttons("Show100C").Visible = True
ActiveSheet.Buttons("Hide100C").Visible = False
ActiveSheet.Buttons("Show10C").Visible = True
ActiveSheet.Buttons("Hide10C").Visible = False
ActiveSheet.Buttons("Show1C").Visible = True
ActiveSheet.Buttons("Hide1C").Visible = False

End Sub

Sub HideAllImages()

ActiveSheet.Shapes("1000R").Visible = True
ActiveSheet.Shapes("100R").Visible = True
ActiveSheet.Shapes("10R").Visible = True
ActiveSheet.Shapes("1R").Visible = True
ActiveSheet.Buttons("HideAllR").Visible = False
ActiveSheet.Buttons("ShowAllR").Visible = True
ActiveSheet.Buttons("Show1000R").Visible = True
ActiveSheet.Buttons("Hide1000R").Visible = False
ActiveSheet.Buttons("Show100R").Visible = True
ActiveSheet.Buttons("Hide100R").Visible = False
ActiveSheet.Buttons("Show10R").Visible = True
ActiveSheet.Buttons("Hide10R").Visible = False
ActiveSheet.Buttons("Show1R").Visible = True
ActiveSheet.Buttons("Hide1R").Visible = False

End Sub

Sub ShowTotalC()

ActiveSheet.Shapes("TotalC").Visible = False
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("ShowTotal").Visible = False
ActiveSheet.Buttons("HideTotal").Visible = True

End Sub

Sub HideTotalC()

ActiveSheet.Shapes("TotalC").Visible = True
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("ShowTotal").Visible = True
ActiveSheet.Buttons("HideTotal").Visible = False

End Sub

Sub Show1000C()

ActiveSheet.Shapes("1000C").Visible = False
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("Show1000C").Visible = False
ActiveSheet.Buttons("Hide1000C").Visible = True

End Sub

Sub Hide1000C()

ActiveSheet.Shapes("1000C").Visible = True
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("Show1000C").Visible = True
ActiveSheet.Buttons("Hide1000C").Visible = False

End Sub

Sub Show1000R()

ActiveSheet.Shapes("1000R").Visible = False
ActiveSheet.Buttons("ShowAllR").Visible = True
ActiveSheet.Buttons("HideAllR").Visible = True
ActiveSheet.Buttons("Show1000R").Visible = False
ActiveSheet.Buttons("Hide1000R").Visible = True

End Sub

Sub Hide1000R()

ActiveSheet.Shapes("1000R").Visible = True
ActiveSheet.Buttons("ShowAllR").Visible = True
ActiveSheet.Buttons("HideAllR").Visible = True
ActiveSheet.Buttons("Show1000R").Visible = True
ActiveSheet.Buttons("Hide1000R").Visible = False

End Sub

Sub Show100C()

ActiveSheet.Shapes("100C").Visible = False
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("Show100C").Visible = False
ActiveSheet.Buttons("Hide100C").Visible = True

End Sub

Sub Hide100C()

ActiveSheet.Shapes("100C").Visible = True
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("Show100C").Visible = True
ActiveSheet.Buttons("Hide100C").Visible = False

End Sub

Sub Show100R()

ActiveSheet.Shapes("100R").Visible = False
ActiveSheet.Buttons("ShowAllR").Visible = True
ActiveSheet.Buttons("HideAllR").Visible = True
ActiveSheet.Buttons("Show100R").Visible = False
ActiveSheet.Buttons("Hide100R").Visible = True

End Sub

Sub Hide100R()

ActiveSheet.Shapes("100R").Visible = True
ActiveSheet.Buttons("ShowAllR").Visible = True
ActiveSheet.Buttons("HideAllR").Visible = True
ActiveSheet.Buttons("Show100R").Visible = True
ActiveSheet.Buttons("Hide100R").Visible = False

End Sub

Sub Show10C()

ActiveSheet.Shapes("10C").Visible = False
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("Show10C").Visible = False
ActiveSheet.Buttons("Hide10C").Visible = True

End Sub

Sub Hide10C()

ActiveSheet.Shapes("10C").Visible = True
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("Show10C").Visible = True
ActiveSheet.Buttons("Hide10C").Visible = False

End Sub

Sub Show10R()

ActiveSheet.Shapes("10R").Visible = False
ActiveSheet.Buttons("ShowAllR").Visible = True
ActiveSheet.Buttons("HideAllR").Visible = True
ActiveSheet.Buttons("Show10R").Visible = False
ActiveSheet.Buttons("Hide10R").Visible = True

End Sub

Sub Hide10R()

ActiveSheet.Shapes("10R").Visible = True
ActiveSheet.Buttons("ShowAllR").Visible = True
ActiveSheet.Buttons("HideAllR").Visible = True
ActiveSheet.Buttons("Show10R").Visible = True
ActiveSheet.Buttons("Hide10R").Visible = False

End Sub

Sub Show1C()

ActiveSheet.Shapes("1C").Visible = False
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("Show1C").Visible = False
ActiveSheet.Buttons("Hide1C").Visible = True

End Sub

Sub Hide1C()

ActiveSheet.Shapes("1C").Visible = True
ActiveSheet.Buttons("ShowAllC").Visible = True
ActiveSheet.Buttons("HideAllC").Visible = True
ActiveSheet.Buttons("Show1C").Visible = True
ActiveSheet.Buttons("Hide1C").Visible = False

End Sub

Sub Show1R()

ActiveSheet.Shapes("1R").Visible = False
ActiveSheet.Buttons("ShowAllR").Visible = True
ActiveSheet.Buttons("HideAllR").Visible = True
ActiveSheet.Buttons("Show1R").Visible = False
ActiveSheet.Buttons("Hide1R").Visible = True

End Sub

Sub Hide1R()

ActiveSheet.Shapes("1R").Visible = True
ActiveSheet.Buttons("ShowAllR").Visible = True
ActiveSheet.Buttons("HideAllR").Visible = True
ActiveSheet.Buttons("Show1R").Visible = True
ActiveSheet.Buttons("Hide1R").Visible = False

End Sub
 
Sort by date Sort by votes
MoshiM said:
I haven't looked at your file but you're mostly there already. You've already got the separate subs all you need to do is supply the values like for your Show1000 sub. All you'd need to do is replace it with
Code: 
Sub Show1000()
    ShowShapes 1000
end sub

Same for hiding shapes:
Code: 
Sub Hide_1000
    Hide_Shapes 1000
end sub
Click to expand...

Hi, thanks again so much.

I have been replacing your code into mine step by step, but there is a bit of an error here:

1588772207590.png

So what should happen is all 9 of the red cubes should be hidden because there is now value to the thousands digit in the total number. Incidently if the total number is only worth 5, it still shows all 9 red cubes, all 9 blue squares and all 9 green sticks.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Can you show me how you have called the Sub or where you think it has gone wrong.
Also here is the rest of it along with a few minor tweaks from what I posted earlier.
Code: 
Sub ShowShapes(Target_Number As Long, Optional Upper_Limit As Long = 0)

Dim INDX As Long

If Upper_Limit = 0 Then Upper_Limit = 9

With ActiveSheet

    For INDX = 1 To Upper_Limit
   
        .Shapes(Target_Number & "x" & INDX).Visible = True
       
        .Shapes("P" & Target_Number & "x" & INDX).Visible = True
       
    Next INDX
   
End With

End Sub

Sub Hide_Shapes(Target_INDX As Long)

Dim WS As Worksheet, Val As Long, INDX As Long

Set WS = Worksheets("Representations")

With WS

    Select Case Target_INDX
   
        Case 1000: Val = .Range("Q1").Value2
        Case 100: Val = .Range("R1").Value2
        Case 10: Val = .Range("S1").Value2
        Case 1: Val = .Range("T1").Value2
       
    End Select
   
    If Val <> 0 Then ShowShapes Target_Number:=Target_INDX, Upper_Limit:=Val 'Make shapes less than or equal to Val visible

    Select Case Val
   
        Case 8 'dont need 9 since showshapes already made it visible if val is 9
       
            .Shapes(Target_INDX & "x9").Visible = False
           
            .Shapes("P" & Target_INDX & "x9").Visible = True
       
        Case Else 'Make shapes greater than val invisible
       
            For INDX = Val + 1 To 9
           
                .Shapes(Target_INDX & "x" & INDX).Visible = False
               
                .Shapes("P" & Target_INDX & "x" & INDX).Visible = False
               
            Next INDX
           
    End Select

End With
                                                                           
End Sub
Sub Card_Image_Visibility(Card As Boolean, Image As Boolean, Show As Boolean)

Dim NUM(1 To 4) As String, X As Long, C_R As String

If Card = True Then
    C_R = "C" 'Card
Else
    C_R = "R" 'Image
End If

For X = 1 To 4
    NUM(X) = 1000 / (10 ^ (X - 1)) & C_R '1000,100,10,1
Next X

With ActiveSheet

    .Shapes("TotalC").Visible = Not Show
   
    For X = 1 To 4
       
        .Shapes(NUM(X)).Visible = Not Show
       
        Select Case Show
            Case True
                .Buttons("Show" & NUM(X)).Visible = False
                .Buttons("Hide" & NUM(X)).Visible = True
            Case False
                .Buttons("Show" & NUM(X)).Visible = True
                .Buttons("Hide" & NUM(X)).Visible = False
        End Select
       
    Next X
   
    Select Case Show
   
        Case True
       
            .Buttons("HideAll" & C_R).Visible = True
            .Buttons("ShowAll" & C_R).Visible = False
           
            .Buttons("ShowTotal").Visible = False
            .Buttons("HideTotal").Visible = True
           
        Case False
       
            .Buttons("HideAll" & C_R).Visible = False
            .Buttons("ShowAll" & C_R).Visible = True

            .Buttons("ShowTotal").Visible = True
            .Buttons("HideTotal").Visible = False
           
    End Select
   
End With

End Sub
Sub C_R_INDX_Visibility(INDX As Long, Card As Boolean, Image As Boolean, Show As Boolean)
   
'USE When you wish to show or hide a card or shape...like for your Hide/Show1000C/R subs
Dim Partial_STR As String, C_R As String

If Card = True Then
    C_R = "C" 'Card
Else
    C_R = "R" 'Image
End If

Partial_STR = INDX & C_R

With ActiveSheet
   
    .Shapes(Partial_STR).Visible = Not Show
   
    .Buttons("ShowAll" & C_R).Visible = True
    .Buttons("HideAll" & C_R).Visible = True
   
    Select Case Show
   
        Case True
       
            .Buttons("Show" & C_R).Visible = False
            .Buttons("Hide" & C_R).Visible = True
        Case False
            .Buttons("Show" & C_R).Visible = True
            .Buttons("Hide" & C_R).Visible = False
           
    End Select
   
End With
   
End Sub
Sub TotalC_Visibility(Show As Boolean)

With ActiveSheet

    .Buttons("ShowAllC").Visible = True
    .Buttons("HideAllC").Visible = True
   
    .Shapes("TotalC").Visible = Not Show
   
    Select Case Show
        Case True
            .Buttons("ShowTotal").Visible = False
            .Buttons("HideTotal").Visible = True
        Case False
            .Buttons("ShowTotal").Visible = True
            .Buttons("HideTotal").Visible = False
    End Select
   
End With

End Sub

Sub ProtectWB()
    ThisWorkbook.Protect Password:="UKExceED", Structure:=True, Windows:=True
End Sub

Sub UnProtectWB()
    ThisWorkbook.Unprotect Password:="UKExceED"
End Sub
 
Upvote 0
I found the issue. In the ShowShapes sub , for both the arguments add byval before them.

VBA Code: 
Sub ShowShapes(ByVal Target_Number As Long, Optional ByVal Upper_Limit As Long = 0)

Dim INDX As Long

If Upper_Limit = 0 Then Upper_Limit = 9

With ActiveSheet

    For INDX = 1 To Upper_Limit
    
        .Shapes(Target_Number & "x" & INDX).Visible = True
        
        .Shapes("P" & Target_Number & "x" & INDX).Visible = True
        
    Next INDX
    
End With

End Sub

Sub Hide_Shapes(ByVal Target_INDX As Long)

Dim WS As Worksheet, Val As Long, INDX As Long

Set WS = Worksheets("Representations")

With WS

    Select Case Target_INDX
    
        Case 1000: Val = .Range("Q1").Value2
        Case 100: Val = .Range("R1").Value2
        Case 10: Val = .Range("S1").Value2
        Case 1: Val = .Range("T1").Value2
        
    End Select
    
    If Val <> 0 Then ShowShapes Target_Number:=Target_INDX, Upper_Limit:=Val 'Make shapes less than or equal to Val visible

    Select Case Val
    
        Case 8 'dont need 9 since showshapes already made it visible if val is 9
        
            .Shapes(Target_INDX & "x9").Visible = False
            
            .Shapes("P" & Target_INDX & "x9").Visible = True
        
        Case Else 'Make shapes greater than val invisible
        
            For INDX = Val + 1 To 9
            
                .Shapes(Target_INDX & "x" & INDX).Visible = False
                
                .Shapes("P" & Target_INDX & "x" & INDX).Visible = False
                
            Next INDX
            
    End Select

End With
                                                                            
End Sub
Sub Card_Image_Visibility(Card As Boolean, Image As Boolean, Show As Boolean)

Dim NUM(1 To 4) As String, X As Long, C_R As String

If Card = True Then
    C_R = "C" 'Card
Else
    C_R = "R" 'Image
End If

For X = 1 To 4
    NUM(X) = 1000 / (10 ^ (X - 1)) & C_R '1000,100,10,1
Next X

With ActiveSheet

    .Shapes("TotalC").Visible = Not Show
    
    For X = 1 To 4
        
        .Shapes(NUM(X)).Visible = Not Show
        
        Select Case Show
            Case True
                .Buttons("Show" & NUM(X)).Visible = False
                .Buttons("Hide" & NUM(X)).Visible = True
            Case False
                .Buttons("Show" & NUM(X)).Visible = True
                .Buttons("Hide" & NUM(X)).Visible = False
        End Select
        
    Next X
    
    Select Case Show
    
        Case True
        
            .Buttons("HideAll" & C_R).Visible = True
            .Buttons("ShowAll" & C_R).Visible = False
            
            .Buttons("ShowTotal").Visible = False
            .Buttons("HideTotal").Visible = True
            
        Case False
        
            .Buttons("HideAll" & C_R).Visible = False
            .Buttons("ShowAll" & C_R).Visible = True

            .Buttons("ShowTotal").Visible = True
            .Buttons("HideTotal").Visible = False
            
    End Select
    
End With

End Sub
Sub C_R_INDX_Visibility(ByVal INDX As Long, Card As Boolean, Image As Boolean, Show As Boolean)
    
'USE When you wish to show or hide a card or shape...like for your Hide/Show1000C/R subs
Dim Partial_STR As String, C_R As String

If Card = True Then
    C_R = "C" 'Card
Else
    C_R = "R" 'Image
End If

Partial_STR = INDX & C_R

With ActiveSheet
    
    .Shapes(Partial_STR).Visible = Not Show
    
    .Buttons("ShowAll" & C_R).Visible = True
    .Buttons("HideAll" & C_R).Visible = True
    
    Select Case Show
    
        Case True
        
            .Buttons("Show" & C_R).Visible = False
            .Buttons("Hide" & C_R).Visible = True
        Case False
            .Buttons("Show" & C_R).Visible = True
            .Buttons("Hide" & C_R).Visible = False
            
    End Select
    
End With
    
End Sub
Sub TotalC_Visibility(Show As Boolean)

With ActiveSheet

    .Buttons("ShowAllC").Visible = True
    .Buttons("HideAllC").Visible = True
    
    .Shapes("TotalC").Visible = Not Show
    
    Select Case Show
        Case True
            .Buttons("ShowTotal").Visible = False
            .Buttons("HideTotal").Visible = True
        Case False
            .Buttons("ShowTotal").Visible = True
            .Buttons("HideTotal").Visible = False
    End Select
    
End With

End Sub

Sub ProtectWB()
    ThisWorkbook.Protect Password:="UKExceED", Structure:=True, Windows:=True
End Sub

Sub UnProtectWB()
    ThisWorkbook.Unprotect Password:="UKExceED"
End Sub
 
Upvote 0
MoshiM said:
I found the issue. In the ShowShapes sub , for both the arguments add byval before them.

VBA Code: 
Sub ShowShapes(ByVal Target_Number As Long, Optional ByVal Upper_Limit As Long = 0)

Dim INDX As Long

If Upper_Limit = 0 Then Upper_Limit = 9

With ActiveSheet

    For INDX = 1 To Upper_Limit
 
        .Shapes(Target_Number & "x" & INDX).Visible = True
     
        .Shapes("P" & Target_Number & "x" & INDX).Visible = True
     
    Next INDX
 
End With

End Sub

Sub Hide_Shapes(ByVal Target_INDX As Long)

Dim WS As Worksheet, Val As Long, INDX As Long

Set WS = Worksheets("Representations")

With WS

    Select Case Target_INDX
 
        Case 1000: Val = .Range("Q1").Value2
        Case 100: Val = .Range("R1").Value2
        Case 10: Val = .Range("S1").Value2
        Case 1: Val = .Range("T1").Value2
     
    End Select
 
    If Val <> 0 Then ShowShapes Target_Number:=Target_INDX, Upper_Limit:=Val 'Make shapes less than or equal to Val visible

    Select Case Val
 
        Case 8 'dont need 9 since showshapes already made it visible if val is 9
     
            .Shapes(Target_INDX & "x9").Visible = False
         
            .Shapes("P" & Target_INDX & "x9").Visible = True
     
        Case Else 'Make shapes greater than val invisible
     
            For INDX = Val + 1 To 9
         
                .Shapes(Target_INDX & "x" & INDX).Visible = False
             
                .Shapes("P" & Target_INDX & "x" & INDX).Visible = False
             
            Next INDX
         
    End Select

End With
                                                                         
End Sub
Sub Card_Image_Visibility(Card As Boolean, Image As Boolean, Show As Boolean)

Dim NUM(1 To 4) As String, X As Long, C_R As String

If Card = True Then
    C_R = "C" 'Card
Else
    C_R = "R" 'Image
End If

For X = 1 To 4
    NUM(X) = 1000 / (10 ^ (X - 1)) & C_R '1000,100,10,1
Next X

With ActiveSheet

    .Shapes("TotalC").Visible = Not Show
 
    For X = 1 To 4
     
        .Shapes(NUM(X)).Visible = Not Show
     
        Select Case Show
            Case True
                .Buttons("Show" & NUM(X)).Visible = False
                .Buttons("Hide" & NUM(X)).Visible = True
            Case False
                .Buttons("Show" & NUM(X)).Visible = True
                .Buttons("Hide" & NUM(X)).Visible = False
        End Select
     
    Next X
 
    Select Case Show
 
        Case True
     
            .Buttons("HideAll" & C_R).Visible = True
            .Buttons("ShowAll" & C_R).Visible = False
         
            .Buttons("ShowTotal").Visible = False
            .Buttons("HideTotal").Visible = True
         
        Case False
     
            .Buttons("HideAll" & C_R).Visible = False
            .Buttons("ShowAll" & C_R).Visible = True

            .Buttons("ShowTotal").Visible = True
            .Buttons("HideTotal").Visible = False
         
    End Select
 
End With

End Sub
Sub C_R_INDX_Visibility(ByVal INDX As Long, Card As Boolean, Image As Boolean, Show As Boolean)
 
'USE When you wish to show or hide a card or shape...like for your Hide/Show1000C/R subs
Dim Partial_STR As String, C_R As String

If Card = True Then
    C_R = "C" 'Card
Else
    C_R = "R" 'Image
End If

Partial_STR = INDX & C_R

With ActiveSheet
 
    .Shapes(Partial_STR).Visible = Not Show
 
    .Buttons("ShowAll" & C_R).Visible = True
    .Buttons("HideAll" & C_R).Visible = True
 
    Select Case Show
 
        Case True
     
            .Buttons("Show" & C_R).Visible = False
            .Buttons("Hide" & C_R).Visible = True
        Case False
            .Buttons("Show" & C_R).Visible = True
            .Buttons("Hide" & C_R).Visible = False
         
    End Select
 
End With
 
End Sub
Sub TotalC_Visibility(Show As Boolean)

With ActiveSheet

    .Buttons("ShowAllC").Visible = True
    .Buttons("HideAllC").Visible = True
 
    .Shapes("TotalC").Visible = Not Show
 
    Select Case Show
        Case True
            .Buttons("ShowTotal").Visible = False
            .Buttons("HideTotal").Visible = True
        Case False
            .Buttons("ShowTotal").Visible = True
            .Buttons("HideTotal").Visible = False
    End Select
 
End With

End Sub

Sub ProtectWB()
    ThisWorkbook.Protect Password:="UKExceED", Structure:=True, Windows:=True
End Sub

Sub UnProtectWB()
    ThisWorkbook.Unprotect Password:="UKExceED"
End Sub
Click to expand...

So this:

VBA Code: 
Sub Hide1R()

ActiveSheet.Shapes("1R").Visible = True
ActiveSheet.Buttons("Hide1R").Visible = False

Dim ButtonName As Variant
    For Each ButtonName In Split("ShowAllR,HideAllR,Show1R", ",")
        ActiveSheet.Buttons(ButtonName).Visible = True
    Next

End Sub

Should now be this:

VBA Code: 
Sub Hide1R()

C_R_INDX_Visibility 1, False, True, False

End Sub

I get an error on this line:
VBA Code: 
Case False
.Buttons("Show" & C_R).Visible = True

in the sub for C_R_INDX_Visivibility
 
Last edited:
Upvote 0
TweetingCynical said:
in the sub for C_R_INDX_Visivibility
Click to expand...

My bad use this instead.
VBA Code: 
Sub C_R_INDX_Visibility(ByVal INDX As Long, Card As Boolean, Image As Boolean, Show As Boolean)
    
'USE When you wish to show or hide a card or shape...like for your Hide/Show1000C/R subs
Dim Partial_STR As String, C_R As String

If Card = True Then
    C_R = "C" 'Card
Else
    C_R = "R" 'Image
End If

Partial_STR = INDX & C_R

With ActiveSheet
    
    .Shapes(Partial_STR).Visible = Not Show
    
    .Buttons("ShowAll" & C_R).Visible = True
    .Buttons("HideAll" & C_R).Visible = True
    
    Select Case Show
    
        Case True
        
            .Buttons("Show" & Partial_STR).Visible = False
            .Buttons("Hide" & Partial_STR).Visible = True
        Case False
            .Buttons("Show" & Partial_STR).Visible = True
            .Buttons("Hide" & Partial_STR).Visible = False
            
    End Select
    
End With
    
End Sub
 
Upvote 0
MoshiM said:
My bad use this instead.
VBA Code: 
Sub C_R_INDX_Visibility(ByVal INDX As Long, Card As Boolean, Image As Boolean, Show As Boolean)
   
'USE When you wish to show or hide a card or shape...like for your Hide/Show1000C/R subs
Dim Partial_STR As String, C_R As String

If Card = True Then
    C_R = "C" 'Card
Else
    C_R = "R" 'Image
End If

Partial_STR = INDX & C_R

With ActiveSheet
   
    .Shapes(Partial_STR).Visible = Not Show
   
    .Buttons("ShowAll" & C_R).Visible = True
    .Buttons("HideAll" & C_R).Visible = True
   
    Select Case Show
   
        Case True
       
            .Buttons("Show" & Partial_STR).Visible = False
            .Buttons("Hide" & Partial_STR).Visible = True
        Case False
            .Buttons("Show" & Partial_STR).Visible = True
            .Buttons("Hide" & Partial_STR).Visible = False
           
    End Select
   
End With
   
End Sub
Click to expand...

Amazing! This is absolutely perfect. Thank you so much!

I am now going through it one line at a time to try and work out why the code does what it does. I made the previous code using a mixture of recorded macros to see what was happening, and some basic vba I knew. Yours is so much more concise! I'd love to be at your level one day!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,586
Messages
6,125,689
Members
449,250
Latest member
azur3

Share this page

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
Back
Top