2 Vba codes on one sheet

[neilcsmith1984]

Hi,

I am very new to VBA and have managed to find and adapt the 2 below codes to work on separate sheets.

I am not sure if this is possible, however, I would like to have both codes working on sheet 1 together.

Code 1: (currently in sheet 1)
Sub SetVisible()

Dim s1 As Shape, s2 As Shape

Set s1 = Me.Shapes("Rectangle 1")
Set s2 = Me.Shapes("Rectangle 2")

Select Case UCase(Range("A1").Value)
Case "2015"
s1.Visible = msoTrue
s2.Visible = msoFalse
Case "2016"
s1.Visible = msoFalse
s2.Visible = msoTrue
Case "ALL"
s1.Visible = msoTrue
s2.Visible = msoTrue
Case Else
s1.Visible = msoFalse
s2.Visible = msoFalse
End Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A1").Address Then SetVisible
End Sub


Code 2: (currently in Sheet 2
Private Sub Worksheet_Calculate()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "E").End(xlUp).Row
On Error Resume Next
For Each c In Range("E1:E" & LastRow)
If c.Value = 1 Then
c.EntireRow.Hidden = True
ElseIf c.Value = 2 Then
c.EntireRow.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub

If anyone can help me get these 2 codes working on one sheet, I would be so grateful....

Thanks

 

[GWteB]

Can't you just put your entire code (as posted) in the module of the desired worksheet?

 

[neilcsmith1984]

I didn't think I could, but that has worked, thanks...

I have one final thing that I need help with;

In the code below A1 derives from a formula and when the value automatically changes the code doesn't work, it only works if i type a new value in A1. Is there anyway of making the code work when A1 changes due to the formula?

Sub SetVisible()

Dim s1 As Shape, s2 As Shape

Set s1 = Me.Shapes("Rectangle 1")
Set s2 = Me.Shapes("Rectangle 2")

Select Case UCase(Range("A1").Value)
Case "2015"
s1.Visible = msoTrue
s2.Visible = msoFalse
Case "2016"
s1.Visible = msoFalse
s2.Visible = msoTrue
Case "ALL"
s1.Visible = msoTrue
s2.Visible = msoTrue
Case Else
s1.Visible = msoFalse
s2.Visible = msoFalse
End Select
End Sub

 

[GWteB]

Forget about the Worksheet_Change event procedure and make a call to your SetVisible() sub from out the Worksheet_Calculate event procedure. Whenever cell A1 evaluates to another value, your SetVisible() sub will be called.