[hollinshead]

Hi there, I wounder if you can help. I am creating a sales system in a database and have on one sheet a list of command buttons for which I use as a switch board type setup. On another sheet I have a range of columns holding relevant sales data. I would like to create a macro or bit of code for one of the command buttons on the main menu. Once selected this opens up the sales pipeline sheet and filters the data based on the data stored in a certain column. The column name is 'Business Area' which has repeating data such as 'software solutions' and 'Risk and Resilience' etc. Can you help?

Thanks for your time and efforts in advance.

[JBeaucaire]

Sure, post up your sample workbook and make sure it's evident where you want to set the controls to filter by, and what you want to trigger the activity.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

========
NOTE: when you cross-post the same question on multiple forums, it is customary for you to provide links to the same question on the other forums. This insures people who decide to try and help can click over and see what other solutions have been offered, or see if you've already solved the query altogether.

The last thing you want to do is waste people's time, yes? So add a link to your question on the other forum(s), too.

[hollinshead]

Thanks ever so much, I have attached a copy of my spreadsheet with some of the data in. I have also highlighted areas where I need work on, so if you could give me any guidance, I would really appreciate it. Thanks again.

[JBeaucaire]

1) Just fix the protection so that macros are free to work when the sheet is protected. The easiest way is to put a Worksheet_Activate macro into the Pipeline sheet so that each time you activate the sheet, the protections are corrected.

Private Sub Worksheet_Activate()
    Me.Protect , UserInterfaceOnly:=True, AllowFiltering:=True
End Sub

If you wanted to put in an actual password:

Private Sub Worksheet_Activate()
    Me.Protect "password", UserInterfaceOnly:=True, AllowFiltering:=True
End Sub

2) Notice the code above fixed the AutoFilter problem, too?

3) This is better code for the FILTER button, it converts the button into a toggle and removes the unattractive "selecting".

Sub Button238_Click()
    Range("A7:BG97").AutoFilter
End Sub

4) The TIMELINE button only unhid the columns, it would work better as a toggle, no? This version of the macro flips the columns hidden/unhidden each time you click it:

Sub DisplayTimeLine()
    Columns("AB:BF").Hidden = Not Columns("AB:BF").Hidden
    Beep
End Sub

5) I've added the following CF formula to B8:AB96:
Condition1: Formula Is:
=AND($A8<>"", B8="")
Format... pattern: RED.

6) You have MAIN MENU buttons on each sheet, but you created a separate macro each time to do the same thing. No need. Just use one macro ToMain() and attach all of those buttons to it.

[ScottGem]

May I suggest that you are stretching the limits of Excel with this. I think you should consider porting this over to Access.