Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protected Sheet | Excel Discussion (Misc queries) | |||
Protected cells in Protected sheet | Excel Programming | |||
Sheet Information - Is sheet protected? | Excel Discussion (Misc queries) | |||
Protected sheet to unprotected sheet | Excel Worksheet Functions | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming |