|
hiding sheets
I have a box in sheet named "General INFO"
If an "X" or "x" is entered into this box, then a hidden sheet named "Detail INFO" becomes visible. The code looks like this: If Sheets("General INFO").Range("H45").Value = "X" Then Sheets("Detail INFO").Visible = True Else: Sheets("Detail INFO").Visible = False End If First, I don't knwo how to include "x" as well as "X" Second, what sub do i put this under? there is no button to activate, i just want this to always apply to the sheet. like as soon as x is entered, the sheet is visible. I was using Private Sub Worksheet_Change(ByVal Target As Range) but this created an error when i typed in other cells besides H45. This probably easy for you guys, but i'm pretty new at this. Thanks for any help Steve |
hiding sheets
Hi Steve,
Try: '============== Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("H45") If Not Intersect(rng, Target) Is Nothing Then Sheets("Detail INFO").Visible = LCase(rng.Value) = "x" End If End Sub '<<============== --- Regards, Norman "steve" wrote in message ... I have a box in sheet named "General INFO" If an "X" or "x" is entered into this box, then a hidden sheet named "Detail INFO" becomes visible. The code looks like this: If Sheets("General INFO").Range("H45").Value = "X" Then Sheets("Detail INFO").Visible = True Else: Sheets("Detail INFO").Visible = False End If First, I don't knwo how to include "x" as well as "X" Second, what sub do i put this under? there is no button to activate, i just want this to always apply to the sheet. like as soon as x is entered, the sheet is visible. I was using Private Sub Worksheet_Change(ByVal Target As Range) but this created an error when i typed in other cells besides H45. This probably easy for you guys, but i'm pretty new at this. Thanks for any help Steve |
hiding sheets
Norm,
That works perfect. If you don't mind, can you just explain the logic in what you did. if you dont' have time, that's fine. i'm just trying to get better at this stuff. i'm not sure exactly what you did. either way, thanks a lot steve "Norman Jones" wrote: Hi Steve, Try: '============== Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("H45") If Not Intersect(rng, Target) Is Nothing Then Sheets("Detail INFO").Visible = LCase(rng.Value) = "x" End If End Sub '<<============== --- Regards, Norman "steve" wrote in message ... I have a box in sheet named "General INFO" If an "X" or "x" is entered into this box, then a hidden sheet named "Detail INFO" becomes visible. The code looks like this: If Sheets("General INFO").Range("H45").Value = "X" Then Sheets("Detail INFO").Visible = True Else: Sheets("Detail INFO").Visible = False End If First, I don't knwo how to include "x" as well as "X" Second, what sub do i put this under? there is no button to activate, i just want this to always apply to the sheet. like as soon as x is entered, the sheet is visible. I was using Private Sub Worksheet_Change(ByVal Target As Range) but this created an error when i typed in other cells besides H45. This probably easy for you guys, but i'm pretty new at this. Thanks for any help Steve |
hiding sheets
Hi Steve
Set rng = Me.Range("H45") If Not Intersect(rng, Target) Is Nothing Then The above lines are intended to limit macro action to changes in the H45 cell. I would have liked to say: If Intersect(rng, Target) Is Something Then but the keyword something does not exist. Since, however, the keyword Nothing does exist, I use this with a double negative to obtain a valid equivalent expression. Sheets("Detail INFO").Visible = LCase(rng.Value) = "x" By comparing the lower case value of H45 with the lower case x, the code allows for either upper or lower case values. The right-hand side of the above equality expression returns either true or false and, therefore, toggles the visible property of the Detail Info sheet. --- Regards, Norman "steve" wrote in message ... Norm, That works perfect. If you don't mind, can you just explain the logic in what you did. if you dont' have time, that's fine. i'm just trying to get better at this stuff. i'm not sure exactly what you did. either way, thanks a lot steve "Norman Jones" wrote: Hi Steve, Try: '============== Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("H45") If Not Intersect(rng, Target) Is Nothing Then Sheets("Detail INFO").Visible = LCase(rng.Value) = "x" End If End Sub '<<============== --- Regards, Norman "steve" wrote in message ... I have a box in sheet named "General INFO" If an "X" or "x" is entered into this box, then a hidden sheet named "Detail INFO" becomes visible. The code looks like this: If Sheets("General INFO").Range("H45").Value = "X" Then Sheets("Detail INFO").Visible = True Else: Sheets("Detail INFO").Visible = False End If First, I don't knwo how to include "x" as well as "X" Second, what sub do i put this under? there is no button to activate, i just want this to always apply to the sheet. like as soon as x is entered, the sheet is visible. I was using Private Sub Worksheet_Change(ByVal Target As Range) but this created an error when i typed in other cells besides H45. This probably easy for you guys, but i'm pretty new at this. Thanks for any help Steve |
hiding sheets
Norman,
Thanks for the thorough explanation. The only syntax i still don't understand is the Me.Range what is the Me? "Norman Jones" wrote: Hi Steve Set rng = Me.Range("H45") If Not Intersect(rng, Target) Is Nothing Then The above lines are intended to limit macro action to changes in the H45 cell. I would have liked to say: If Intersect(rng, Target) Is Something Then but the keyword something does not exist. Since, however, the keyword Nothing does exist, I use this with a double negative to obtain a valid equivalent expression. Sheets("Detail INFO").Visible = LCase(rng.Value) = "x" By comparing the lower case value of H45 with the lower case x, the code allows for either upper or lower case values. The right-hand side of the above equality expression returns either true or false and, therefore, toggles the visible property of the Detail Info sheet. --- Regards, Norman "steve" wrote in message ... Norm, That works perfect. If you don't mind, can you just explain the logic in what you did. if you dont' have time, that's fine. i'm just trying to get better at this stuff. i'm not sure exactly what you did. either way, thanks a lot steve "Norman Jones" wrote: Hi Steve, Try: '============== Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("H45") If Not Intersect(rng, Target) Is Nothing Then Sheets("Detail INFO").Visible = LCase(rng.Value) = "x" End If End Sub '<<============== --- Regards, Norman "steve" wrote in message ... I have a box in sheet named "General INFO" If an "X" or "x" is entered into this box, then a hidden sheet named "Detail INFO" becomes visible. The code looks like this: If Sheets("General INFO").Range("H45").Value = "X" Then Sheets("Detail INFO").Visible = True Else: Sheets("Detail INFO").Visible = False End If First, I don't knwo how to include "x" as well as "X" Second, what sub do i put this under? there is no button to activate, i just want this to always apply to the sheet. like as soon as x is entered, the sheet is visible. I was using Private Sub Worksheet_Change(ByVal Target As Range) but this created an error when i typed in other cells besides H45. This probably easy for you guys, but i'm pretty new at this. Thanks for any help Steve |
hiding sheets
'Me' refers to the object which contains the code. Since the code
is in a sheet's code module, Me refers to that worksheet. If 'Me' appears in a userform's code module, it would refer to the userform. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "steve" wrote in message ... Norman, Thanks for the thorough explanation. The only syntax i still don't understand is the Me.Range what is the Me? "Norman Jones" wrote: Hi Steve Set rng = Me.Range("H45") If Not Intersect(rng, Target) Is Nothing Then The above lines are intended to limit macro action to changes in the H45 cell. I would have liked to say: If Intersect(rng, Target) Is Something Then but the keyword something does not exist. Since, however, the keyword Nothing does exist, I use this with a double negative to obtain a valid equivalent expression. Sheets("Detail INFO").Visible = LCase(rng.Value) = "x" By comparing the lower case value of H45 with the lower case x, the code allows for either upper or lower case values. The right-hand side of the above equality expression returns either true or false and, therefore, toggles the visible property of the Detail Info sheet. --- Regards, Norman "steve" wrote in message ... Norm, That works perfect. If you don't mind, can you just explain the logic in what you did. if you dont' have time, that's fine. i'm just trying to get better at this stuff. i'm not sure exactly what you did. either way, thanks a lot steve "Norman Jones" wrote: Hi Steve, Try: '============== Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("H45") If Not Intersect(rng, Target) Is Nothing Then Sheets("Detail INFO").Visible = LCase(rng.Value) = "x" End If End Sub '<<============== --- Regards, Norman "steve" wrote in message ... I have a box in sheet named "General INFO" If an "X" or "x" is entered into this box, then a hidden sheet named "Detail INFO" becomes visible. The code looks like this: If Sheets("General INFO").Range("H45").Value = "X" Then Sheets("Detail INFO").Visible = True Else: Sheets("Detail INFO").Visible = False End If First, I don't knwo how to include "x" as well as "X" Second, what sub do i put this under? there is no button to activate, i just want this to always apply to the sheet. like as soon as x is entered, the sheet is visible. I was using Private Sub Worksheet_Change(ByVal Target As Range) but this created an error when i typed in other cells besides H45. This probably easy for you guys, but i'm pretty new at this. Thanks for any help Steve |
| All times are GMT +1. The time now is 01:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com