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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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.
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339

ADVERTISEMENT

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

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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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
Top