|
Automatically hide/unhide columns
I have a validation box with yes/no options in it in cell H7.
When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! |
Automatically hide/unhide columns
This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and
R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson |
Automatically hide/unhide columns
Thanks Dave - it works like a charm except for when "$/eq T" is selected with
H7 = "yes". In this case it also displays column M which needs to be hidden when $/eq T is selected (all K:N need to be hidden when $/eq T is selected). "Dave Peterson" wrote: This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson |
Automatically hide/unhide columns
I'm not sure I understand the requirements.
You have two cells to inspect (H6 and H7). There's only a few things that can happen: H7 H6 What should be hidden/shown ===== ================ ================================================= Yes Total $ Yes $/eq T Yes Total $ & $/eq t Yes blank no Total $ no $/eq T no Total $ & $/eq t no blank blank Total $ blank $/eq T blank Total $ & $/eq t blank blank Please explain what columns should be hidden/shown for each of these 12 conditions. (I didn't want to guess.) laststraw wrote: Thanks Dave - it works like a charm except for when "$/eq T" is selected with H7 = "yes". In this case it also displays column M which needs to be hidden when $/eq T is selected (all K:N need to be hidden when $/eq T is selected). "Dave Peterson" wrote: This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson -- Dave Peterson |
Automatically hide/unhide columns
Dave,
My apologies for not making this a bit clearer. What I have is something like this... Cost type: (cell H6) validation list = Total $ & $/eq T, Total $, $/eq T Variance: (cell H7) validation list = Yes, No K L M N O P Q R S T ---- Total Cost ---- ---- Unit Cost ---- 2007 2008 var 2007 2008 var 34 30 4 26 28 -2 etc If H6 = "Total $ & $/eq T" and H7 = "Yes", then K:M & P:R need to be displayed. If H7 = "No" then col M & R need to be hidden. This works fine using the previous code. However, if H6 = "$/eq T" and H7 = "Yes" then only P:R should be displayed. This combination currently displays col M as well which needs to remain hidden if this combination is selected. Is there an easy fix to this? Thanks so much! "Dave Peterson" wrote: I'm not sure I understand the requirements. You have two cells to inspect (H6 and H7). There's only a few things that can happen: H7 H6 What should be hidden/shown ===== ================ ================================================= Yes Total $ Yes $/eq T Yes Total $ & $/eq t Yes blank no Total $ no $/eq T no Total $ & $/eq t no blank blank Total $ blank $/eq T blank Total $ & $/eq t blank blank Please explain what columns should be hidden/shown for each of these 12 conditions. (I didn't want to guess.) laststraw wrote: Thanks Dave - it works like a charm except for when "$/eq T" is selected with H7 = "yes". In this case it also displays column M which needs to be hidden when $/eq T is selected (all K:N need to be hidden when $/eq T is selected). "Dave Peterson" wrote: This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson -- Dave Peterson |
Automatically hide/unhide columns
I don't think you gave all the possibilities.
How about filling all the possibilities in that chart? laststraw wrote: Dave, My apologies for not making this a bit clearer. What I have is something like this... Cost type: (cell H6) validation list = Total $ & $/eq T, Total $, $/eq T Variance: (cell H7) validation list = Yes, No K L M N O P Q R S T ---- Total Cost ---- ---- Unit Cost ---- 2007 2008 var 2007 2008 var 34 30 4 26 28 -2 etc If H6 = "Total $ & $/eq T" and H7 = "Yes", then K:M & P:R need to be displayed. If H7 = "No" then col M & R need to be hidden. This works fine using the previous code. However, if H6 = "$/eq T" and H7 = "Yes" then only P:R should be displayed. This combination currently displays col M as well which needs to remain hidden if this combination is selected. Is there an easy fix to this? Thanks so much! "Dave Peterson" wrote: I'm not sure I understand the requirements. You have two cells to inspect (H6 and H7). There's only a few things that can happen: H7 H6 What should be hidden/shown ===== ================ ================================================= Yes Total $ Yes $/eq T Yes Total $ & $/eq t Yes blank no Total $ no $/eq T no Total $ & $/eq t no blank blank Total $ blank $/eq T blank Total $ & $/eq t blank blank Please explain what columns should be hidden/shown for each of these 12 conditions. (I didn't want to guess.) laststraw wrote: Thanks Dave - it works like a charm except for when "$/eq T" is selected with H7 = "yes". In this case it also displays column M which needs to be hidden when $/eq T is selected (all K:N need to be hidden when $/eq T is selected). "Dave Peterson" wrote: This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Automatically hide/unhide columns
The possibilities a
H7 H6 Columns to be hidden / displayed yes total $ & $/eq T none / all yes total $ P:R / K:M yes $/eq T K:M / P:R no total $ & $/eq T M & R / K:L & P:Q no total $ M & P:R / K:L no $/eq T K:M & R / P:Q The only scenario that the code doesn't do as I need is "yes" + "$/eq T" because col M remains visible. The rest works great. "Dave Peterson" wrote: I don't think you gave all the possibilities. How about filling all the possibilities in that chart? laststraw wrote: Dave, My apologies for not making this a bit clearer. What I have is something like this... Cost type: (cell H6) validation list = Total $ & $/eq T, Total $, $/eq T Variance: (cell H7) validation list = Yes, No K L M N O P Q R S T ---- Total Cost ---- ---- Unit Cost ---- 2007 2008 var 2007 2008 var 34 30 4 26 28 -2 etc If H6 = "Total $ & $/eq T" and H7 = "Yes", then K:M & P:R need to be displayed. If H7 = "No" then col M & R need to be hidden. This works fine using the previous code. However, if H6 = "$/eq T" and H7 = "Yes" then only P:R should be displayed. This combination currently displays col M as well which needs to remain hidden if this combination is selected. Is there an easy fix to this? Thanks so much! "Dave Peterson" wrote: I'm not sure I understand the requirements. You have two cells to inspect (H6 and H7). There's only a few things that can happen: H7 H6 What should be hidden/shown ===== ================ ================================================= Yes Total $ Yes $/eq T Yes Total $ & $/eq t Yes blank no Total $ no $/eq T no Total $ & $/eq t no blank blank Total $ blank $/eq T blank Total $ & $/eq t blank blank Please explain what columns should be hidden/shown for each of these 12 conditions. (I didn't want to guess.) laststraw wrote: Thanks Dave - it works like a charm except for when "$/eq T" is selected with H7 = "yes". In this case it also displays column M which needs to be hidden when $/eq T is selected (all K:N need to be hidden when $/eq T is selected). "Dave Peterson" wrote: This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Automatically hide/unhide columns
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("h6:h7")) Is Nothing Then Exit Sub 'I'd show everything to start Me.Columns.Hidden = False Select Case LCase(Me.Range("h7").Value) Case Is = "yes" Select Case LCase(Me.Range("H6").Value) Case Is = "total $ & $/eq t" Me.Columns.Hidden = False Case Is = "total $" Me.Range("P:R").EntireColumn.Hidden = True Me.Range("K:M").EntireColumn.Hidden = False Case Is = "$/eq t" Me.Range("P:R").EntireColumn.Hidden = False Me.Range("K:M").EntireColumn.Hidden = True End Select Case Is = "no" Select Case LCase(Me.Range("H6").Value) Case Is = "total $ & $/eq t" Me.Range("m:M,R:r").EntireColumn.Hidden = True Me.Range("K:L,P:Q").EntireColumn.Hidden = False Case Is = "total $" Me.Range("M:M,P:R").EntireColumn.Hidden = True Me.Range("K:L").EntireColumn.Hidden = False Case Is = "$/eq t" Me.Range("K:M,R:R").EntireColumn.Hidden = False Me.Range("P:q").EntireColumn.Hidden = True End Select Case Else 'do nothing End Select End Sub laststraw wrote: The possibilities a H7 H6 Columns to be hidden / displayed yes total $ & $/eq T none / all yes total $ P:R / K:M yes $/eq T K:M / P:R no total $ & $/eq T M & R / K:L & P:Q no total $ M & P:R / K:L no $/eq T K:M & R / P:Q The only scenario that the code doesn't do as I need is "yes" + "$/eq T" because col M remains visible. The rest works great. "Dave Peterson" wrote: I don't think you gave all the possibilities. How about filling all the possibilities in that chart? laststraw wrote: Dave, My apologies for not making this a bit clearer. What I have is something like this... Cost type: (cell H6) validation list = Total $ & $/eq T, Total $, $/eq T Variance: (cell H7) validation list = Yes, No K L M N O P Q R S T ---- Total Cost ---- ---- Unit Cost ---- 2007 2008 var 2007 2008 var 34 30 4 26 28 -2 etc If H6 = "Total $ & $/eq T" and H7 = "Yes", then K:M & P:R need to be displayed. If H7 = "No" then col M & R need to be hidden. This works fine using the previous code. However, if H6 = "$/eq T" and H7 = "Yes" then only P:R should be displayed. This combination currently displays col M as well which needs to remain hidden if this combination is selected. Is there an easy fix to this? Thanks so much! "Dave Peterson" wrote: I'm not sure I understand the requirements. You have two cells to inspect (H6 and H7). There's only a few things that can happen: H7 H6 What should be hidden/shown ===== ================ ================================================= Yes Total $ Yes $/eq T Yes Total $ & $/eq t Yes blank no Total $ no $/eq T no Total $ & $/eq t no blank blank Total $ blank $/eq T blank Total $ & $/eq t blank blank Please explain what columns should be hidden/shown for each of these 12 conditions. (I didn't want to guess.) laststraw wrote: Thanks Dave - it works like a charm except for when "$/eq T" is selected with H7 = "yes". In this case it also displays column M which needs to be hidden when $/eq T is selected (all K:N need to be hidden when $/eq T is selected). "Dave Peterson" wrote: This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Automatically hide/unhide columns
This works except the last Case is = $/eq T needs to have the true and false
around the other way. Thanks for your help - it's very much appreciated! "Dave Peterson" wrote: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("h6:h7")) Is Nothing Then Exit Sub 'I'd show everything to start Me.Columns.Hidden = False Select Case LCase(Me.Range("h7").Value) Case Is = "yes" Select Case LCase(Me.Range("H6").Value) Case Is = "total $ & $/eq t" Me.Columns.Hidden = False Case Is = "total $" Me.Range("P:R").EntireColumn.Hidden = True Me.Range("K:M").EntireColumn.Hidden = False Case Is = "$/eq t" Me.Range("P:R").EntireColumn.Hidden = False Me.Range("K:M").EntireColumn.Hidden = True End Select Case Is = "no" Select Case LCase(Me.Range("H6").Value) Case Is = "total $ & $/eq t" Me.Range("m:M,R:r").EntireColumn.Hidden = True Me.Range("K:L,P:Q").EntireColumn.Hidden = False Case Is = "total $" Me.Range("M:M,P:R").EntireColumn.Hidden = True Me.Range("K:L").EntireColumn.Hidden = False Case Is = "$/eq t" Me.Range("K:M,R:R").EntireColumn.Hidden = False Me.Range("P:q").EntireColumn.Hidden = True End Select Case Else 'do nothing End Select End Sub laststraw wrote: The possibilities a H7 H6 Columns to be hidden / displayed yes total $ & $/eq T none / all yes total $ P:R / K:M yes $/eq T K:M / P:R no total $ & $/eq T M & R / K:L & P:Q no total $ M & P:R / K:L no $/eq T K:M & R / P:Q The only scenario that the code doesn't do as I need is "yes" + "$/eq T" because col M remains visible. The rest works great. "Dave Peterson" wrote: I don't think you gave all the possibilities. How about filling all the possibilities in that chart? laststraw wrote: Dave, My apologies for not making this a bit clearer. What I have is something like this... Cost type: (cell H6) validation list = Total $ & $/eq T, Total $, $/eq T Variance: (cell H7) validation list = Yes, No K L M N O P Q R S T ---- Total Cost ---- ---- Unit Cost ---- 2007 2008 var 2007 2008 var 34 30 4 26 28 -2 etc If H6 = "Total $ & $/eq T" and H7 = "Yes", then K:M & P:R need to be displayed. If H7 = "No" then col M & R need to be hidden. This works fine using the previous code. However, if H6 = "$/eq T" and H7 = "Yes" then only P:R should be displayed. This combination currently displays col M as well which needs to remain hidden if this combination is selected. Is there an easy fix to this? Thanks so much! "Dave Peterson" wrote: I'm not sure I understand the requirements. You have two cells to inspect (H6 and H7). There's only a few things that can happen: H7 H6 What should be hidden/shown ===== ================ ================================================= Yes Total $ Yes $/eq T Yes Total $ & $/eq t Yes blank no Total $ no $/eq T no Total $ & $/eq t no blank blank Total $ blank $/eq T blank Total $ & $/eq t blank blank Please explain what columns should be hidden/shown for each of these 12 conditions. (I didn't want to guess.) laststraw wrote: Thanks Dave - it works like a charm except for when "$/eq T" is selected with H7 = "yes". In this case it also displays column M which needs to be hidden when $/eq T is selected (all K:N need to be hidden when $/eq T is selected). "Dave Peterson" wrote: This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Automatically hide/unhide columns
So you changed the true and false around and it worked ok?
laststraw wrote: This works except the last Case is = $/eq T needs to have the true and false around the other way. Thanks for your help - it's very much appreciated! "Dave Peterson" wrote: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("h6:h7")) Is Nothing Then Exit Sub 'I'd show everything to start Me.Columns.Hidden = False Select Case LCase(Me.Range("h7").Value) Case Is = "yes" Select Case LCase(Me.Range("H6").Value) Case Is = "total $ & $/eq t" Me.Columns.Hidden = False Case Is = "total $" Me.Range("P:R").EntireColumn.Hidden = True Me.Range("K:M").EntireColumn.Hidden = False Case Is = "$/eq t" Me.Range("P:R").EntireColumn.Hidden = False Me.Range("K:M").EntireColumn.Hidden = True End Select Case Is = "no" Select Case LCase(Me.Range("H6").Value) Case Is = "total $ & $/eq t" Me.Range("m:M,R:r").EntireColumn.Hidden = True Me.Range("K:L,P:Q").EntireColumn.Hidden = False Case Is = "total $" Me.Range("M:M,P:R").EntireColumn.Hidden = True Me.Range("K:L").EntireColumn.Hidden = False Case Is = "$/eq t" Me.Range("K:M,R:R").EntireColumn.Hidden = False Me.Range("P:q").EntireColumn.Hidden = True End Select Case Else 'do nothing End Select End Sub laststraw wrote: The possibilities a H7 H6 Columns to be hidden / displayed yes total $ & $/eq T none / all yes total $ P:R / K:M yes $/eq T K:M / P:R no total $ & $/eq T M & R / K:L & P:Q no total $ M & P:R / K:L no $/eq T K:M & R / P:Q The only scenario that the code doesn't do as I need is "yes" + "$/eq T" because col M remains visible. The rest works great. "Dave Peterson" wrote: I don't think you gave all the possibilities. How about filling all the possibilities in that chart? laststraw wrote: Dave, My apologies for not making this a bit clearer. What I have is something like this... Cost type: (cell H6) validation list = Total $ & $/eq T, Total $, $/eq T Variance: (cell H7) validation list = Yes, No K L M N O P Q R S T ---- Total Cost ---- ---- Unit Cost ---- 2007 2008 var 2007 2008 var 34 30 4 26 28 -2 etc If H6 = "Total $ & $/eq T" and H7 = "Yes", then K:M & P:R need to be displayed. If H7 = "No" then col M & R need to be hidden. This works fine using the previous code. However, if H6 = "$/eq T" and H7 = "Yes" then only P:R should be displayed. This combination currently displays col M as well which needs to remain hidden if this combination is selected. Is there an easy fix to this? Thanks so much! "Dave Peterson" wrote: I'm not sure I understand the requirements. You have two cells to inspect (H6 and H7). There's only a few things that can happen: H7 H6 What should be hidden/shown ===== ================ ================================================= Yes Total $ Yes $/eq T Yes Total $ & $/eq t Yes blank no Total $ no $/eq T no Total $ & $/eq t no blank blank Total $ blank $/eq T blank Total $ & $/eq t blank blank Please explain what columns should be hidden/shown for each of these 12 conditions. (I didn't want to guess.) laststraw wrote: Thanks Dave - it works like a charm except for when "$/eq T" is selected with H7 = "yes". In this case it also displays column M which needs to be hidden when $/eq T is selected (all K:N need to be hidden when $/eq T is selected). "Dave Peterson" wrote: This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Automatically hide/unhide columns
yes - only on the one as stated above. Thanks!
"Dave Peterson" wrote: So you changed the true and false around and it worked ok? laststraw wrote: This works except the last Case is = $/eq T needs to have the true and false around the other way. Thanks for your help - it's very much appreciated! "Dave Peterson" wrote: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("h6:h7")) Is Nothing Then Exit Sub 'I'd show everything to start Me.Columns.Hidden = False Select Case LCase(Me.Range("h7").Value) Case Is = "yes" Select Case LCase(Me.Range("H6").Value) Case Is = "total $ & $/eq t" Me.Columns.Hidden = False Case Is = "total $" Me.Range("P:R").EntireColumn.Hidden = True Me.Range("K:M").EntireColumn.Hidden = False Case Is = "$/eq t" Me.Range("P:R").EntireColumn.Hidden = False Me.Range("K:M").EntireColumn.Hidden = True End Select Case Is = "no" Select Case LCase(Me.Range("H6").Value) Case Is = "total $ & $/eq t" Me.Range("m:M,R:r").EntireColumn.Hidden = True Me.Range("K:L,P:Q").EntireColumn.Hidden = False Case Is = "total $" Me.Range("M:M,P:R").EntireColumn.Hidden = True Me.Range("K:L").EntireColumn.Hidden = False Case Is = "$/eq t" Me.Range("K:M,R:R").EntireColumn.Hidden = False Me.Range("P:q").EntireColumn.Hidden = True End Select Case Else 'do nothing End Select End Sub laststraw wrote: The possibilities a H7 H6 Columns to be hidden / displayed yes total $ & $/eq T none / all yes total $ P:R / K:M yes $/eq T K:M / P:R no total $ & $/eq T M & R / K:L & P:Q no total $ M & P:R / K:L no $/eq T K:M & R / P:Q The only scenario that the code doesn't do as I need is "yes" + "$/eq T" because col M remains visible. The rest works great. "Dave Peterson" wrote: I don't think you gave all the possibilities. How about filling all the possibilities in that chart? laststraw wrote: Dave, My apologies for not making this a bit clearer. What I have is something like this... Cost type: (cell H6) validation list = Total $ & $/eq T, Total $, $/eq T Variance: (cell H7) validation list = Yes, No K L M N O P Q R S T ---- Total Cost ---- ---- Unit Cost ---- 2007 2008 var 2007 2008 var 34 30 4 26 28 -2 etc If H6 = "Total $ & $/eq T" and H7 = "Yes", then K:M & P:R need to be displayed. If H7 = "No" then col M & R need to be hidden. This works fine using the previous code. However, if H6 = "$/eq T" and H7 = "Yes" then only P:R should be displayed. This combination currently displays col M as well which needs to remain hidden if this combination is selected. Is there an easy fix to this? Thanks so much! "Dave Peterson" wrote: I'm not sure I understand the requirements. You have two cells to inspect (H6 and H7). There's only a few things that can happen: H7 H6 What should be hidden/shown ===== ================ ================================================= Yes Total $ Yes $/eq T Yes Total $ & $/eq t Yes blank no Total $ no $/eq T no Total $ & $/eq t no blank blank Total $ blank $/eq T blank Total $ & $/eq t blank blank Please explain what columns should be hidden/shown for each of these 12 conditions. (I didn't want to guess.) laststraw wrote: Thanks Dave - it works like a charm except for when "$/eq T" is selected with H7 = "yes". In this case it also displays column M which needs to be hidden when $/eq T is selected (all K:N need to be hidden when $/eq T is selected). "Dave Peterson" wrote: This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Automatically hide/unhide columns
I wasn't sure from the previous post. Glad you have your solution.
laststraw wrote: yes - only on the one as stated above. Thanks! "Dave Peterson" wrote: So you changed the true and false around and it worked ok? laststraw wrote: This works except the last Case is = $/eq T needs to have the true and false around the other way. Thanks for your help - it's very much appreciated! "Dave Peterson" wrote: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("h6:h7")) Is Nothing Then Exit Sub 'I'd show everything to start Me.Columns.Hidden = False Select Case LCase(Me.Range("h7").Value) Case Is = "yes" Select Case LCase(Me.Range("H6").Value) Case Is = "total $ & $/eq t" Me.Columns.Hidden = False Case Is = "total $" Me.Range("P:R").EntireColumn.Hidden = True Me.Range("K:M").EntireColumn.Hidden = False Case Is = "$/eq t" Me.Range("P:R").EntireColumn.Hidden = False Me.Range("K:M").EntireColumn.Hidden = True End Select Case Is = "no" Select Case LCase(Me.Range("H6").Value) Case Is = "total $ & $/eq t" Me.Range("m:M,R:r").EntireColumn.Hidden = True Me.Range("K:L,P:Q").EntireColumn.Hidden = False Case Is = "total $" Me.Range("M:M,P:R").EntireColumn.Hidden = True Me.Range("K:L").EntireColumn.Hidden = False Case Is = "$/eq t" Me.Range("K:M,R:R").EntireColumn.Hidden = False Me.Range("P:q").EntireColumn.Hidden = True End Select Case Else 'do nothing End Select End Sub laststraw wrote: The possibilities a H7 H6 Columns to be hidden / displayed yes total $ & $/eq T none / all yes total $ P:R / K:M yes $/eq T K:M / P:R no total $ & $/eq T M & R / K:L & P:Q no total $ M & P:R / K:L no $/eq T K:M & R / P:Q The only scenario that the code doesn't do as I need is "yes" + "$/eq T" because col M remains visible. The rest works great. "Dave Peterson" wrote: I don't think you gave all the possibilities. How about filling all the possibilities in that chart? laststraw wrote: Dave, My apologies for not making this a bit clearer. What I have is something like this... Cost type: (cell H6) validation list = Total $ & $/eq T, Total $, $/eq T Variance: (cell H7) validation list = Yes, No K L M N O P Q R S T ---- Total Cost ---- ---- Unit Cost ---- 2007 2008 var 2007 2008 var 34 30 4 26 28 -2 etc If H6 = "Total $ & $/eq T" and H7 = "Yes", then K:M & P:R need to be displayed. If H7 = "No" then col M & R need to be hidden. This works fine using the previous code. However, if H6 = "$/eq T" and H7 = "Yes" then only P:R should be displayed. This combination currently displays col M as well which needs to remain hidden if this combination is selected. Is there an easy fix to this? Thanks so much! "Dave Peterson" wrote: I'm not sure I understand the requirements. You have two cells to inspect (H6 and H7). There's only a few things that can happen: H7 H6 What should be hidden/shown ===== ================ ================================================= Yes Total $ Yes $/eq T Yes Total $ & $/eq t Yes blank no Total $ no $/eq T no Total $ & $/eq t no blank blank Total $ blank $/eq T blank Total $ & $/eq t blank blank Please explain what columns should be hidden/shown for each of these 12 conditions. (I didn't want to guess.) laststraw wrote: Thanks Dave - it works like a charm except for when "$/eq T" is selected with H7 = "yes". In this case it also displays column M which needs to be hidden when $/eq T is selected (all K:N need to be hidden when $/eq T is selected). "Dave Peterson" wrote: This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and R are hidden. Anything else and M and R are shown. If that's ok, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time! If Target.Cells.Count 1 Then Exit Sub If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then 'in H7 Me.Range("M1,R1").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("no")) ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then Me.Range("P:S").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("total $")) Me.Range("k:n").EntireColumn.Hidden _ = CBool(LCase(Target.Value) = LCase("$/eq t")) End If End Sub laststraw wrote: I have a validation box with yes/no options in it in cell H7. When "no" is selected I would like to hide columns M and R only. When yes is selected I would like these columns to be displayed. In the same worksheet I have a validation in cell H6 but would like columns P:S hidden if "Total $" is selected from the validation list, columns K:N hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T" is selected. I need the first validation to work in conjunction with the second so that for example, if total $ is selected and H7 has "yes" selected, only columns K:N are visible. I am working with Excel 2003 and my knowledge of VBA is extremely basic, extending only to pasting code into a module. I am very grateful for any help you can give me on this one. Thanks!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 08:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com