Userinterfaceonly

[Tami]

i have the following code to allow users to Group and Ungroup and delete
columns in a protected worksheet. can someone tell me what i'm doing wrong.
i got it working with grouping/ungrouping, but when i tried to add the
ability to delete columns it stopped working.
p.s. this is my first time using userinterfaceonly...is there something
risky about it or just a work-around because excel's protected worksheets are
so restrictive in what they allow a user to do?
thanks in advance for any and all advice,
tami

Private Sub Workbook_Open()

With Worksheets("Ladders")

.EnableOutlining = True
.Protect AllowDeletingColumns:=True, Password:="paspas", _
userinterfaceonly:=True
End With

End Sub

 

[Jacob Skaria]

Hi Tami

--By default all excel cells are protected. However locking cells has no
effect unless the worksheet is protected.

--Here you are trying to delete a column that contain locked cells. Locked
cells cannot be deleted while the worksheet is protected. (and this is the
message you get while trying to delete a column)

--If you are looking to allow the user to delete columns; then you will need
to unlock the cells before protecting the sheet.
(Format>cells>Protection>Locked)

Hope the above explanasion helps. Have a great week ahead..

If this post helps click Yes

 

[Tami]

Yes, that was bascially my understanding. so i wrote macros to allow them to
delete. i also had to write macros to allow them to group and ungroup. But
then i read about this userinterface thing and i put this code in the open
workbook vba...

With Worksheets("Ladders")
.Protect Password:="paspas", userinterfaceonly:=True
.EnableOutlining = True
End With

and suddently they are allowed to group and ungroup. so i was hoping that
by using the userinterface function again i could allow the user to delete
columns but i can't get it to work. i just don't fully understand this
userinterface thing...i've tried to read all the internet articles but i
don't quite understand how it works.
tami

 

[Jacob Skaria]

UserInterfaceOnly has nothing to do with the deletions/or any worksheet
functinalities.. The documentation says (UserInterfaceOnly Optional
Variant. True to protect the user interface, but not macros. If this argument
is omitted, protection applies both to macros and to the user interface.) So
If userinterface is True; then the protection applies only for the worksheet
tab....and not the macros...

If this post helps click Yes

 

[TSW632]

UserInterfaceOnly has nothing to do with the deletions/or any worksheet
functinalities.. The documentation says (UserInterfaceOnly   Optional
Variant. True to protect the user interface, but not macros. If this argument
is omitted,protectionapplies both to macros and to the user interface.) So
If userinterface is True; then theprotectionapplies only for the worksheet
tab....and not the macros...

If this post helps click Yes
---------------
Jacob Skaria








- Show quoted text -

I have the same issue (nearly - I just need them to have grouping
available), and have tried the following two sets of code in the "This
Workbook" module of the file, and neither has worked for me.

'To use group and ungroup w protection on

Option Explicit
Sub auto_open()
With Worksheets("Input")
.Protect Password:="ooohahhh", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

'To allow group/ungroup on protected sheet

Sub workbook_open()

Dim SH As Worksheet

For Each SH In ThisWorkbook.Worksheets

With SH
..EnableOutlining = True
..Protect Password:="ooohahhh", _
Contents:=True, _
userinterfaceonly:=True
End With

Any ideas would be greatly appreciated.

Troy

 

[TSW632]

I have the same issue (nearly - I just need them to have grouping
available), and have tried the following two sets of code in the "This
Workbook" module of the file, and neither has worked for me.

'To use group and ungroup w protection on

Option Explicit
Sub auto_open()
    With Worksheets("Input")
        .Protect Password:="ooohahhh", userinterfaceonly:=True
        .EnableOutlining = True
    End With
End Sub

'To allow group/ungroup on protected sheet

Sub workbook_open()

Dim SH As Worksheet

For Each SH In ThisWorkbook.Worksheets

With SH
.EnableOutlining = True
.Protect Password:="ooohahhh", _
Contents:=True, _
userinterfaceonly:=True
End With

Any ideas would be greatly appreciated.

Troy- Hide quoted text -

- Show quoted text -

Disregard my request. Turns out I just needed to restart my pc for the
code to start working.