Hiding Columns on two sheets. Elvis would want you to help m

[Lowell In the south]

I am trying to hide a columns on sheet1 and a columns on sheet2 based on a cells on sheet1. If A7 is "" then I want to hide column K on sheet1 and column P on sheet2. If A9 is "" then I want to hide column L on sheet1 and columnQ on sheet2 and so on. Here is my modification of a bit of code that kknie posted 4/30/2002 that will hide a column on sheet1 based on a cell on sheet1 but I dont know how to get it to hide the column on sheet2 also. Thanks for your help!


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 And Target.Address = "$A$7" Then
If LCase(Target.Value) = "" Then
Columns("k").EntireColumn.Hidden = True
ElseIf LCase(Target.Value)<> "" Then
Columns("k").EntireColumn.Hidden = False
End If
End If

If Target.Cells.Count = 1 And Target.Address = "$A$9" Then
If LCase(Target.Value) = "" Then
Columns("l").EntireColumn.Hidden = True
ElseIf LCase(Target.Value)<> "" Then
Columns("l").EntireColumn.Hidden = False
End If
End If
This message was edited by Lowell In the south on 2002-09-27 12:01

 

[Mudface]

This should work OK:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flag As Boolean

With Target
    If .Count <> 1 Then Exit Sub
    Select Case .Address
    Case Is = "$A"
        If .Value = "" Then Flag = True
        Sheet1.Columns("K").Hidden = Flag
        Sheet2.Columns("P").Hidden = Flag
        
    Case Is = "$A"
        If .Value = "" Then Flag = True
        Sheet1.Columns("L").Hidden = Flag
        Sheet2.Columns("Q").Hidden = Flag
    End Select
End With

End Sub

How many cells are you testing as there may be a way to reduce the amount of code to write by incorporating a 'generic' test rather than going through each one individually as above.

 

[Lowell In the south]

Thanks guys i will try and I WILL report back on this. Thanks again!!!



The KING lives on at Graceland!

 

[Mudface]

Hmm, my original post doesn't seem to have come out properly. I'll repost here: -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flag As Boolean

With Target
    If .Count <> 1 Then Exit Sub
    Select Case .Address
    Case Is = "$A"
        If .Value = "" Then Flag = True
        Sheet1.Columns("K").Hidden = Flag
        Sheet2.Columns("P").Hidden = Flag
        
    Case Is = "$A"
        If .Value = "" Then Flag = True
        Sheet1.Columns("L").Hidden = Flag
        Sheet2.Columns("Q").Hidden = Flag
    End Select
End With

End Sub

 

[Mudface]

Nope still being truncated.

Lowell, replace the first instance of "$A" with "$A$7" and the second with "$A$9". How strange.

Wonder if this works OK without the code tags: -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flag As Boolean

With Target
If .Count<> 1 Then Exit Sub
Select Case .Address
Case Is = "$A$7"
If .Value = "" Then Flag = True
Sheet1.Columns("K").Hidden = Flag
Sheet2.Columns("P").Hidden = Flag

Case Is = "$A$9"
If .Value = "" Then Flag = True
Sheet1.Columns("L").Hidden = Flag
Sheet2.Columns("Q").Hidden = Flag
End Select
End With

End Sub

Edit:- yep, that's better. Seems to be a problem with the code tags.
This message was edited by Mudface on 2002-09-27 13:31

 

[Lowell In the south]

Here it is with modified worksheet names I get a compile Error:Sub Function Not defined on the first
Window Inputs.Columns("K").Hidden = Flag

IDEAS??? THANKS FOR YOUR HELP!!!!!

Private Sub Worksheet_Change(ByVal Target As Range)


Dim Flag As Boolean

With Target
If .Count <> 1 Then Exit Sub
Select Case .Address
Case Is = "$A$7"
If .Value = "" Then Flag = True
Window Inputs.Columns("K").Hidden = Flag
Quote Format.Columns("P").Hidden = Flag

Case Is = "$A$9"
If .Value = "" Then Flag = True
Window Inputs.Columns("L").Hidden = Flag
Quote Format.Columns("Q").Hidden = Flag
End Select
End With

End Sub

 

[Mudface]

Try this: -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flag As Boolean

With Target
If .Count <> 1 Then Exit Sub
Select Case .Address
Case Is = "$A$7"
If .Value = "" Then Flag = True
Worksheets("Window Inputs").Columns("K").Hidden = Flag
Worksheets("Quote Format").Columns("P").Hidden = Flag

Case Is = "$A$9"
If .Value = "" Then Flag = True
Worksheets("Window Inputs").Columns("L").Hidden = Flag
Worksheets("Quote Format").Columns("Q").Hidden = Flag
End Select
End With

End Sub

You can reference worksheets in several different ways, by name as above, by Index number (eg Worksheets(1)) depending on the order they are in in the workbook, or by their object names which you can see in the left-hand pane of the VBA editor.

 

[Lowell In the south]

WORKs GRRRRRRRRREATTTTTTT!!!!!!

Thank You So much!