VBA

[Graham]

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

 

[Stefi]

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:

 

[Graham]

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]

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]

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:

 

[Graham]

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]

It works well for me. Isn't your worksheet protected?
Stefi


„Graham†ezt írta:

 

[Graham]

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]

Try this, it's supposed to do what you specified, hides all rows having YES
in column L. I supposed that the sheet is unprotected. If you want to protect
it then revove apostrophs from
' ActiveSheet.Protect
and
' ActiveSheet.Unprotect
lines, and unlock cells you want to write in!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:L")) Is Nothing Then
' ActiveSheet.Unprotect Password:="psw"
On Error Resume Next
Application.EnableEvents = False
If Not Target.HasFormula Then Target.Value = UCase(Target.Value)
Application.EnableEvents = True
On Error GoTo 0
If Target.Column = 12 Then
Columns("L").Select
yesrow = 0
On Error Resume Next
Selection.Find(What:="YES", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0
yesrow = ActiveCell.Row
firsthit = yesrow
If yesrow > 0 Then
Do
Rows(yesrow).Hidden = True
On Error Resume Next
Selection.FindNext(After:=ActiveCell).Activate
On Error GoTo 0
yesrow = ActiveCell.Row
Loop While yesrow > firsthit
End If
End If
' ActiveSheet.Protect Password:="psw", DrawingObjects:=True,
Contents:=True, Scenarios:=True
End If
End Sub

Regards,
Stefi


„Graham†ezt írta:

 

[Graham]

Hi Stef,thanx a mill it is working fine, much appreciated.

Graham

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi
Clicking the YES button will be appreciated.


„Graham†ezt írta: