hide and unhide worksheet

[TiggerToo]

hello all,

I need a little more instructions on why this VBA code does not work the way it suppose too:
I put the correct cell in place $K$23, now I want to hide a worksheet called "DutyCode" when info!$k$23 is blank or has "xx" or "XX" in the cell, and then unhide "DutyCode" when the number 27 is inputed in info!$k$23

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$k$23" Then Exit Sub
If Target.Value = 1234 Then
Worksheets("Sheet2").Visible = True
Else
Worksheets("Sheet2").Visible = False
End If
End Sub

any and all help is gladly tried and tested
Thanks
Walt

[Davers]

The only thing I can see is the first part is missing...try this:

Private Sub Worksheet_Calculate()
   Worksheet_Change Range("K23")
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Value = 27 Then
        Worksheets("Sheet2").Visible = True
    Else
        Worksheets("Sheet2").Visible = False
    End If

End Sub

[Tom Urtis]

Why would you frame the question to affect a sheet named "Duty Code" when your procedure shows "Sheet2", and why would you specify the number 27 in your question, but in your code specify 1234? Are you sure you are testing the right criteria?

You can get this to work at least two different ways. One way is to refer to the VBA object name of the worksheet whose tab is named "Sheet2" (or "Duty Code" or whatever sheet you are interested in hiding and unhiding). The worksheet object names are in the VBAProject window of the Visual Basic Editor (Alt+Q > Ctrl+R). My guess is, if you named a worksheet "Sheet2", the code name is "Sheet2", but you'd have to look to make sure, and of course with "Duty Code" you'd really need to look.

Anyway, the code would look like this *instead* of what you have, so replace what you have in your worksheet module with this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$K$23" Then Exit Sub
Run "SheetCommand"
If Target.Value = 1234 Then
Sheet2.Visible = False '.VWorksheets("Sheet2").Visible = False
Else
Sheet2.Visible = True 'Worksheets("Sheet2").Visible = True
End If
End Sub


Your other option is to call a macro that does the dirty deed when cell K23 is changed, instead of relying on the code to do that in the worksheet module when another worksheet is involved.

In the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$K$23" Then Exit Sub
Run "SheetCommand"
End Sub

In a standard module (assuming the target worksheet is Sheet1):

Sub SheetCommand()
If Worksheets("Sheet1").Range("K23").Value = 1234 Then
Worksheets("Sheet2").Visible = False
Else
Worksheets("Sheet2").Visible = True
End If
End Sub


Remember, to get to the worksheet module, right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.


Both the above approaches worked for me just now when tested.

[Davers]

Hmmm...why didn't anyone tell me my code was wrong?! I forgot the most important part to it, and included lines that didn't need to be there...sorry about that...that's what happens when I'm too quick to post!!!

Have a good day,

Dave..

P.S. Have revised my code to the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = Range("$K$23").Address Then
    If Target.Value = 27 Then
        Worksheets("Sheet2").Visible = True
    Else
        Worksheets("Sheet2").Visible = False
    End If
End If

End Sub

And this seems to work fine...