Protect Sheet Disables Custom Command Bar

[Guest]

Hi All

I have crated a custom command bar using the following
code:

Sub LoadSortBar()

Dim customBar As CommandBar
Dim newButton As CommandBarButton

On Error Resume Next

Set customBar = CommandBars.Add("SortBar", msoBarTop)
Set newButton = customBar.Controls.Add
(msoControlButton, ID:=210)
Set newButton = customBar.Controls.Add
(msoControlButton, ID:=211)
Set newButton = customBar.Controls.Add
(msoControlButton, ID:=928)

customBar.Visible = True
customBar.Enabled = True

End Sub

However when I protect the sheet by selecting Tools |
Protection | Protect sheet, this command bar is disabled.

Is there away to protect the sheet and have this command
bar enabled.

Thanks

 

[Bernie Deitrick]

No. All those specific commandbuttons are disabled by protection. If you
want to have an unprotected commandbar, then you will need to write your own
macros that unprotect the sheet, do the sort, then reprotect the sheet, and
assign those macros to custom commandbar buttons.

HTH,
Bernie
MS Excel MVP

 

[Doug Glancy]

This is a really simple example of kind of what Bernie is talking about. It
assigns a macro to a button on your toolbar that calls up the Sort Dialog
box. If you have specific sorts that you want done, then I'd do what Bernie
said and code the actual sorts instead of just calling up the dialog box.
If you want the flexibility of the dialog box then this works, but leaves a
lot unanswered, i.e., what was protected before you unprotected it (sheets,
contents, etc.) and what if some other runtime error, besides selecting an
area with no data, occurs.

Sub LoadSortBar()

Dim customBar As CommandBar
Dim newButton As CommandBarButton

On Error Resume Next
Application.CommandBars("SortBar").Delete 'if it already exists delete it
On Error GoTo 0

Set customBar = CommandBars.Add("SortBar", msoBarTop)
Set newButton = customBar.Controls.Add(msoControlButton)
With newButton
.OnAction = "ShowSortDialog"
.FaceId = 928
End With
customBar.Visible = True
customBar.Enabled = True

End Sub
Sub ShowSortDialog()

ActiveSheet.Unprotect 'need to test if was protected and what was protected
On Error Resume Next ' so you don't get a runtime error 1004 if selection
contains no data - also ignores other runtime errors, so that could be a
problem
Application.Dialogs(xlDialogSort).Show
If Err = 1004 Then
MsgBox "nothing to sort" ' if the error was 1004 (no data selected) then
kind of reproduce the standard Excel error message for this
End If
On Error GoTo 0
ActiveSheet.Protect 'but what was protected before and was there a password?

End Sub

hth,

Doug