Capture Old Cell Value - VBA

[redbaron06]

Hi Everyone!

I would really appreciate some help with this one.

I am trying to hide a worksheet using the old value in a cell and then unhide another worksheet using new value in the cell. I want this event to be triggered by a change in cell "O11" (generated from a formula). I have tried a few different methods and have run into a roadblock. I cannot figure out how to save the previous value of the cell before the change to use later in the Sub.

Thanks in advance! - Terry

Private Sub Worksheet_Calculate()
Static OldVal1 As Variant, OldVal2 As Variant
If Range("O11").Value <> OldVal1 Then
With Worksheets(OldVal1)Visible
.Visible = xlSheetVeryHidden
End With
OldVal1 = Range("O11").Value
Call PlayTennis
With Worksheets(Range("O11").Value)
.Visible = xlSheetVisible
End With
End If
End Sub

 

[Andrew Poulsom]

Is it?

Private Sub Worksheet_Calculate()
    Static OldVal As Variant
    If IsEmpty(OldVal) Then OldVal = Range("O11").Value
    If Range("O11").Value <> OldVal Then
        Worksheets(OldVal).Visible = xlSheetVeryHidden
        OldVal = Range("O11").Value
        Call PlayTennis
        Worksheets(Range("O11").Value).Visible = xlSheetVisible
    End If
End Sub

 

[redbaron06]

Thank you so much Andrew!

That solves the worksheet problem! However, it now prevents Call PlayTennis from running properly. I will tinker and see what I can do.

Sub PlayTennis()

RangeTango = Range("O11").Value
Sheets(Range("O11").Value).Activate
Range("A1:D50").Select
Selection.Copy Destination:=Sheets("Main").Range("A1")
Sheets("Main").Activate
End Sub

 

[redbaron06]

Got it, thanks again!

Private Sub Worksheet_Calculate()
Static OldVal As Variant
If IsEmpty(OldVal) Then OldVal = Range("O11").Value
If Range("O11").Value <> OldVal Then
Worksheets(OldVal).Visible = xlSheetVeryHidden
OldVal = Range("O11").Value
Worksheets(Range("O11").Value).Visible = xlSheetVisible
Call PlayTennis
End If
End Sub