VB - With protected sheet
 

Dear Guys,

i have a simple Marco that basically hides some rows and columns when i pick
the hide option froma drop down list.

The problem is i protected the sheet, (but not the drop down list of course)
and when i choose the hide option, nothing happens!

The code is written below

Any suggestion is appreciated..

Thanks!


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Reset
With Application
.EnableEvents = False
If Not Application.Intersect(Me.Range("T75"), Target) Is Nothing Then
Me.Rows("76:92").Hidden = (UCase(Target.Value) = "HIDE")
End If
If Not Application.Intersect(Me.Range("t6"), Target) Is Nothing Then
Me.Rows("7").Hidden = (UCase(Target.Value) = "HIDE")
End If
.EnableEvents = True
End With
Exit Sub


Reset:
Application.EnableEvents = True
End Sub

VB - With protected sheet
 

You are right, if it is protected, the code does not work. But the code
would not work anyhow as currently structured. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Reset
With Application
.EnableEvents = False
If Not Application.Intersect(Me.Range("T75"), Target) Is Nothing Then
Me.Rows("76:92").Hidden = (UCase(Target.Value) = "HIDE")
End If
If Not Application.Intersect(Me.Range("t6"), Target) Is Nothing Then
Me.Rows("7").Hidden = (UCase(Target.Value) = "HIDE")
End If
.EnableEvents = True
End With
Exit Sub


Reset:
Application.EnableEvents = True
End Sub

"Batshon" wrote:

Dear Guys,

i have a simple Marco that basically hides some rows and columns when i pick
the hide option froma drop down list.

The problem is i protected the sheet, (but not the drop down list of course)
and when i choose the hide option, nothing happens!

The code is written below

Any suggestion is appreciated..

Thanks!


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Reset
With Application
.EnableEvents = False
If Not Application.Intersect(Me.Range("T75"), Target) Is Nothing Then
Me.Rows("76:92").Hidden = True
If Not Application.Intersect(Me.Range("t6"), Target) Is Nothing Then
Me.Rows("7").Hidden = True
End If
.EnableEvents = True
End With
Exit Sub
Reset:
Application.EnableEvents = True
End Sub

VB - With protected sheet
 

thanks for your reply man, but it is working perfectly fine in an unprotected
mode.
is there any trick that i can make the cade work under a protected mode?

Thanks!

"JLGWhiz" wrote:

You are right, if it is protected, the code does not work. But the code
would not work anyhow as currently structured. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Reset
With Application
.EnableEvents = False
If Not Application.Intersect(Me.Range("T75"), Target) Is Nothing Then
Me.Rows("76:92").Hidden = (UCase(Target.Value) = "HIDE")
End If
If Not Application.Intersect(Me.Range("t6"), Target) Is Nothing Then
Me.Rows("7").Hidden = (UCase(Target.Value) = "HIDE")
End If
.EnableEvents = True
End With
Exit Sub


Reset:
Application.EnableEvents = True
End Sub

"Batshon" wrote:

Dear Guys,

i have a simple Marco that basically hides some rows and columns when i pick
the hide option froma drop down list.

The problem is i protected the sheet, (but not the drop down list of course)
and when i choose the hide option, nothing happens!

The code is written below

Any suggestion is appreciated..

Thanks!


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Reset
With Application
.EnableEvents = False
If Not Application.Intersect(Me.Range("T75"), Target) Is Nothing Then
Me.Rows("76:92").Hidden = True
If Not Application.Intersect(Me.Range("t6"), Target) Is Nothing Then
Me.Rows("7").Hidden = True
End If
.EnableEvents = True
End With
Exit Sub
Reset:
Application.EnableEvents = True
End Sub

VB - With protected sheet
 

I might suggest that you have your code unprotect the sheet immediatley
before you hide the rows, then protect it again just before you return it to
the user.
"Batshon" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Reset
active sheet.unprotect password:="your password here"
....your code here
activesheet.protect password:="your password"
Exit Sub



thanks for your reply man, but it is working perfectly fine in an unprotected
mode.
is there any trick that i can make the cade work under a protected mode?

Thanks!

"JLGWhiz" wrote:

You are right, if it is protected, the code does not work. But the code
would not work anyhow as currently structured. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Reset
With Application
.EnableEvents = False
If Not Application.Intersect(Me.Range("T75"), Target) Is Nothing Then
Me.Rows("76:92").Hidden = (UCase(Target.Value) = "HIDE")
End If
If Not Application.Intersect(Me.Range("t6"), Target) Is Nothing Then
Me.Rows("7").Hidden = (UCase(Target.Value) = "HIDE")
End If
.EnableEvents = True
End With
Exit Sub


Reset:
Application.EnableEvents = True
End Sub

"Batshon" wrote:

Dear Guys,

i have a simple Marco that basically hides some rows and columns when i pick
the hide option froma drop down list.

The problem is i protected the sheet, (but not the drop down list of course)
and when i choose the hide option, nothing happens!

The code is written below

Any suggestion is appreciated..

Thanks!


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Reset
With Application
.EnableEvents = False
If Not Application.Intersect(Me.Range("T75"), Target) Is Nothing Then
Me.Rows("76:92").Hidden = True
If Not Application.Intersect(Me.Range("t6"), Target) Is Nothing Then
Me.Rows("7").Hidden = True
End If
.EnableEvents = True
End With
Exit Sub
Reset:
Application.EnableEvents = True
End Sub