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

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
This should work OK:-

Code:
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.
 
Upvote 0
Hmm, my original post doesn't seem to have come out properly. I'll repost here: -

Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,219,101
Messages
6,146,287
Members
450,685
Latest member
frederik00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top