| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA
Can somebody please assist me, I have this code which looks like:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If If Target.Column = 12 Then If LCase(Target.Value) = "yes" Then Target.EntireRow.Hidden = True End If End If End Sub The first part works great were all lower case is changed to upper case,however If I have the text "YES" in a cell in row 12 then the entire row must be hidden. Help please, I do not now anything about VBA, but am willing to learn. Graham |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA
If Target.Column = 12 Then
This line checks COLUMN 12 (column L), not ROW 12. Use If Target.Row = 12 Then instead! Regards, Stefi €˛Graham€¯ ezt Ć*rta: Can somebody please assist me, I have this code which looks like: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If If Target.Column = 12 Then If LCase(Target.Value) = "yes" Then Target.EntireRow.Hidden = True End If End If End Sub The first part works great were all lower case is changed to upper case,however If I have the text "YES" in a cell in row 12 then the entire row must be hidden. Help please, I do not now anything about VBA, but am willing to learn. Graham |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA
Hi Stef
Thanx for that I actually want to check column 12 and if any cell in that column has the text "YES" in it then the entire row must be hidden. Thanking you in advance Graham "Stefi" wrote: If Target.Column = 12 Then This line checks COLUMN 12 (column L), not ROW 12. Use If Target.Row = 12 Then instead! Regards, Stefi €˛Graham€¯ ezt Ć*rta: Can somebody please assist me, I have this code which looks like: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If If Target.Column = 12 Then If LCase(Target.Value) = "yes" Then Target.EntireRow.Hidden = True End If End If End Sub The first part works great were all lower case is changed to upper case,however If I have the text "YES" in a cell in row 12 then the entire row must be hidden. Help please, I do not now anything about VBA, but am willing to learn. Graham |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA
Hi Stef
Thanx for that I actually want to check column 12 and if any cell in that column has the text "YES" in it then the entire row must be hidden. Thanking you in advance Graham "Graham" wrote: Can somebody please assist me, I have this code which looks like: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If If Target.Column = 12 Then If LCase(Target.Value) = "yes" Then Target.EntireRow.Hidden = True End If End If End Sub The first part works great were all lower case is changed to upper case,however If I have the text "YES" in a cell in row 12 then the entire row must be hidden. Help please, I do not now anything about VBA, but am willing to learn. Graham |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA
This is the piece of code that finds the FIRST "yes" in column 12 and hides
that row: yesrow = 0 On Error Resume Next yesrow = Target.EntireColumn.Find(What:="yes", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row On Error GoTo 0 If yesrow 0 Then Rows(yesrow).Hidden = True End If Stefi €˛Graham€¯ ezt Ć*rta: Hi Stef Thanx for that I actually want to check column 12 and if any cell in that column has the text "YES" in it then the entire row must be hidden. Thanking you in advance Graham "Graham" wrote: Can somebody please assist me, I have this code which looks like: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If If Target.Column = 12 Then If LCase(Target.Value) = "yes" Then Target.EntireRow.Hidden = True End If End If End Sub The first part works great were all lower case is changed to upper case,however If I have the text "YES" in a cell in row 12 then the entire row must be hidden. Help please, I do not now anything about VBA, but am willing to learn. Graham |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA
Hi Stef I dont no what I am doing wrong, look what i have done, dont no if it
is correct, get an error "unable to set the hidden property of the range class" Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If yesrow = 0 On Error Resume Next yesrow = Target.EntireColumn.Find(What:="yes", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row On Error GoTo 0 If yesrow 0 Then Rows(yesrow).Hidden = True End If End Sub "Stefi" wrote: This is the piece of code that finds the FIRST "yes" in column 12 and hides that row: yesrow = 0 On Error Resume Next yesrow = Target.EntireColumn.Find(What:="yes", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row On Error GoTo 0 If yesrow 0 Then Rows(yesrow).Hidden = True End If Stefi €˛Graham€¯ ezt Ć*rta: Hi Stef Thanx for that I actually want to check column 12 and if any cell in that column has the text "YES" in it then the entire row must be hidden. Thanking you in advance Graham "Graham" wrote: Can somebody please assist me, I have this code which looks like: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If If Target.Column = 12 Then If LCase(Target.Value) = "yes" Then Target.EntireRow.Hidden = True End If End If End Sub The first part works great were all lower case is changed to upper case,however If I have the text "YES" in a cell in row 12 then the entire row must be hidden. Help please, I do not now anything about VBA, but am willing to learn. Graham |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA
It works well for me. Isn't your worksheet protected?
Stefi €˛Graham€¯ ezt Ć*rta: Hi Stef I dont no what I am doing wrong, look what i have done, dont no if it is correct, get an error "unable to set the hidden property of the range class" Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If yesrow = 0 On Error Resume Next yesrow = Target.EntireColumn.Find(What:="yes", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row On Error GoTo 0 If yesrow 0 Then Rows(yesrow).Hidden = True End If End Sub "Stefi" wrote: This is the piece of code that finds the FIRST "yes" in column 12 and hides that row: yesrow = 0 On Error Resume Next yesrow = Target.EntireColumn.Find(What:="yes", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row On Error GoTo 0 If yesrow 0 Then Rows(yesrow).Hidden = True End If Stefi €˛Graham€¯ ezt Ć*rta: Hi Stef Thanx for that I actually want to check column 12 and if any cell in that column has the text "YES" in it then the entire row must be hidden. Thanking you in advance Graham "Graham" wrote: Can somebody please assist me, I have this code which looks like: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If If Target.Column = 12 Then If LCase(Target.Value) = "yes" Then Target.EntireRow.Hidden = True End If End If End Sub The first part works great were all lower case is changed to upper case,however If I have the text "YES" in a cell in row 12 then the entire row must be hidden. Help please, I do not now anything about VBA, but am willing to learn. Graham |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA
Hi Stef, I password protected my sheet with certain cells locked,have taken the protection off however the VBA code is not working properly. Is it too much to ask if you could writ a code for me, I want to achieve the following: From B:L all data must be in upper case, then I want in colmn L if I type "YES" in a cell then that entire row for that cell must be hidden. My data will be entered from row 3 onwards as I have headings in rows 1 to 2.I also have a filter on this spreadsheet in row 2 , thanx Stef-- Eager to learn ! "Stefi" wrote: It works well for me. Isn't your worksheet protected? Stefi €˛Graham€¯ ezt Ć*rta: Hi Stef I dont no what I am doing wrong, look what i have done, dont no if it is correct, get an error "unable to set the hidden property of the range class" Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If yesrow = 0 On Error Resume Next yesrow = Target.EntireColumn.Find(What:="yes", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row On Error GoTo 0 If yesrow 0 Then Rows(yesrow).Hidden = True End If End Sub "Stefi" wrote: This is the piece of code that finds the FIRST "yes" in column 12 and hides that row: yesrow = 0 On Error Resume Next yesrow = Target.EntireColumn.Find(What:="yes", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row On Error GoTo 0 If yesrow 0 Then Rows(yesrow).Hidden = True End If Stefi €˛Graham€¯ ezt Ć*rta: Hi Stef Thanx for that I actually want to check column 12 and if any cell in that column has the text "YES" in it then the entire row must be hidden. Thanking you in advance Graham "Graham" wrote: Can somebody please assist me, I have this code which looks like: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:L")) Is Nothing Then On Error Resume Next Application.EnableEvents = False If Not Target.HasFormula Then Target.Value = UCase(Target.Value) Application.EnableEvents = True On Error GoTo 0 End If If Target.Column = 12 Then If LCase(Target.Value) = "yes" Then Target.EntireRow.Hidden = True End If End If End Sub The first part works great were all lower case is changed to upper case,however If I have the text "YES" in a cell in row 12 then the entire row must be hidden. Help please, I do not now anything about VBA, but am willing to learn. Graham |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
