Multiple private subs in worksheet

[Lord_B]

Hi,

This is my first post, hope get this right.

I am newish to VBA and have come up against problem (first of many no doubt). I have searched for an answer on the forums and Google but not finding one that works for me.
I need to put multiple private subs in one work sheet but am unable to run the code, the first part works but not the other bits.

I hope the below makes sense?

Private Sub Worksheet_Calculate()
     If Range("a5").Value = "a" Then
          Me.Shapes("Rectangle: Rounded Corners 1").Visible = True
     Else
            Me.Shapes("Rectangle: Rounded Corners 1").Visible = False
     End If
End Sub
 
Private Sub Worksheet_Calculate()
     If Range("a6").Value = "a" Then
          Me.Shapes("Rectangle: Rounded Corners 4").Visible = True
     Else
            Me.Shapes("Rectangle: Rounded Corners 4").Visible = False
     End If
End Sub
 
 
Private Sub Worksheet_Calculate()
     If Range("a7").Value = "a" Then
          Me.Shapes("Rectangle: Rounded Corners 5").Visible = True
     Else
            Me.Shapes("Rectangle: Rounded Corners 5").Visible = False
     End If
End Sub

Do I need to combine these somehow?

Hopefully this a rookie mistake I have made.

Thanks for looking/helping on this,

Ben

 

[Joe4]

Welcome to the Board!

You are not allowed to have multiple Procedures in the same module with the same name. You need to combine them all into one single procedure.
In this case, that is pretty easy to do, as it doesn't appear that you have any interference between them, i.e.

Private Sub Worksheet_Calculate()

     If Range("a5").Value = "a" Then
          Me.Shapes("Rectangle: Rounded Corners 1").Visible = True
     Else
            Me.Shapes("Rectangle: Rounded Corners 1").Visible = False
     End If

     If Range("a6").Value = "a" Then
          Me.Shapes("Rectangle: Rounded Corners 4").Visible = True
     Else
            Me.Shapes("Rectangle: Rounded Corners 4").Visible = False
     End If

     If Range("a7").Value = "a" Then
          Me.Shapes("Rectangle: Rounded Corners 5").Visible = True
     Else
            Me.Shapes("Rectangle: Rounded Corners 5").Visible = False
     End If

End Sub

 

[Fluff]

Hi & welcome to MrExcel.

You can only have one Calculate event per sheet, so you need to combine them like

Private Sub Worksheet_Calculate()
   If Range("a5").Value = "a" Then
      Me.Shapes("Rectangle: Rounded Corners 1").Visible = True
   Else
      Me.Shapes("Rectangle: Rounded Corners 1").Visible = False
   End If
   If Range("a6").Value = "a" Then
      Me.Shapes("Rectangle: Rounded Corners 4").Visible = True
   Else
      Me.Shapes("Rectangle: Rounded Corners 4").Visible = False
   End If
   If Range("a7").Value = "a" Then
      Me.Shapes("Rectangle: Rounded Corners 5").Visible = True
   Else
      Me.Shapes("Rectangle: Rounded Corners 5").Visible = False
   End If
End Sub

 

[Lord_B]

Thanks Joe4 & Fluff for the warm welcome,

Wow! when you put the code like that it makes sense!

Thanks to both of you for your help. you make it look soooo simple

 

[Fluff]

Glad we could help & thanks for the feedback