Enabling the Filter button in a class module/protected worksheet

D

Doug Glancy

I'm using a class to hook events for existing toolbar buttons as decribed
Professional Excel Development. The sheet that the code applies to is
protected with AllowFiltering. I want the user to be able to turn Filter on
and off. The only way I've found to do so is to set a dummy OnAction for
the filter button. This makes it available (all the time that the Worsheet
Menu Bar is visible, I think). Is there a better way? Here's the class
module code:

Public WithEvents filter_class_button_899 As CommandBarButton

Private Sub Class_Initialize()
Set filter_class_button_899 = Application.CommandBars.FindControl(ID:=899)
filter_class_button_899.OnAction = "dummy_sub"
End Sub

Private Sub Class_Terminate()
filter_class_button_899.Reset
End Sub

Private Sub filter_class_button_899_Click(ByVal Ctrl As
Office.CommandBarButton, CancelDefault As Boolean)
Call toggle_filter 'basically "range.Autofilter"
filter_class_button_899.State = Not filter_class_button_899.State
End Sub

Thanks,

Doug Glancy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top