How can I Hide Sheet 2 from a Value in sheet 1

[swieduwi]

I have three sheets sheet 1 is where user inputs info
sheets 2 and 3 are results
I need to only display one or the other sheet depending on the
value of a cell from sheet 1

[tkstock]

How familiar are you with VBA? You would need an event handler to capture the value entered, then show/hide the appropriate sheets. Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim a As String
    a = ActiveSheet.Cells(1, 1).Value
    If a = "the value" Then
        Sheets("Sheet1").Visible = False
        Sheets("Sheet2").Visible = True
    Else
        Sheets("Sheet2").Visible = False
        Sheets("Sheet1").Visible = True
    End If
End Sub

This uses the Worksheet_Change event handler - this should be in the worksheet module.

Hope this helps!

[swieduwi]

Some what new at VB,

[tkstock]

Some what new at VB,

I will annotate my code a little better:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim a As String
    a = ActiveSheet.Cells(x, y).Value ' x=row, y=column of cell you're checking
    If a = "the value" Then ' "the value" is the value you are checking against
        Sheets("Sheet1").Visible = False ' "Sheet1" = title of worksheet you want to hide
        Sheets("Sheet2").Visible = True ' "Sheet2" = title of worksheet you want to show
    Else
        Sheets("Sheet2").Visible = False
        Sheets("Sheet1").Visible = True
    End If
End Sub

To put the code in the worksheet module, hit Alt-F11 to get into the VB Editor. On the left-hand side, right-click on the worksheet you are checking, and click "View Code". Cut and paste the above code sample into the module. Change x and y in the a=ActiveSheet.Cells(x,y).Value statement to reflect the actual cell you are checking against. Also change the Sheet names to reflect the sheets that you want to show (Visible = true) and hide (Visible = false).

I hope this clarifies everything.

[swieduwi]

I tried it and could not get it to work.....

I then tried this and it works but it's ugly and not very smooth

Private Sub Worksheet_Calculate()

Select Case Range("G6").Value

Case Is = "s"
        Sheets("Diagram (HA)").Visible = False
        Sheets("Diagram (Single)").Visible = True
        
Case Is = "x"
        Sheets("Diagram (HA)").Visible = False
        Sheets("Diagram (Single)").Visible = True

Case Else
        Sheets("Diagram (HA)").Visible = True
        Sheets("Diagram (Single)").Visible = False
        
End Select
        Sheets("Vars").Visible = False
End Sub

[tkstock]

Why is it not smooth? Looks OK to me. You can add Appliction.Screenupdating = False to the beginning to make the screen changes invisible until their done.

If it works, use it.. BTW, I left something out of my original code.

My (revised) code equivalent to yours:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$6" then
      Dim a As String
      a = ActiveSheet.Cells(6, 7).Value 
      If a = "s" Or a = "x" Then
          Sheets("Diagram (HA)").Visible = False
          Sheets("Diagram (Single)").Visible = True 
      Else
          Sheets("Diagram (HA)").Visible = True
          Sheets("Diagram (Single)").Visible = False 
      End If
    End If
End Sub

If you noticed, your case "x" and case "s" were exactly the same...

HTH