Visual Basic Question in Excel

[meths]

I've written a macro to remove any autofilters from a workbook but is there any way to get it to run automatically when the workbook is saved either by a click event and/or by using the save as option from the menu?

 

[meths]

Whoops, wrong forum.

Could a don move this to the programming forum please.

 

[MorningView]

Yep, thats perfectly possible.

Open up the VBA editor (from Tools), I'm assuming you know your way arround the VBA editor.

Then double click on ThisWorkbook on the left and copy this code in...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "Saving now..."
End Sub

Replace Msgbox "Saving now..." with a call for the macro you wish to execute.

Let me know how you get on.
Matt

 

[meths]

I realised that by doing this it didn't allow the user to quicksave the workbook and keep the autofilters on while they continued working so I put it into a before close event like this...



Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Not ActiveWorkbook.Saved Then
a = MsgBox("This workbook contains unsaved changes. Do you wish to save?", vbYesNoCancel)

If response = vbYes Then
[Execute main code to remove the autofilters and save the workbook]

Else: response = vbCancel
Cancel = True

End If

End If

ThisWorkbook.Saved = True

End Sub


Thanks for the initial help though mate. It would have taken me ages to figure out how to use the before close command without it.