Custom Footer

[lord_legrand]

Hi Guys!


I need your help in creating a macro to add a custom footer to the files that are saved on my hard disk (there's a lot of them). What I need is a macro that will the open the files that I shall choose. It would be better if I can choose multiple excel files at a time. Then if possible a dropdown will then appear containing the following values:

"Footer Label 1"
"Footer Label 2"
"Footer Label 3"
"Footer Label 4"


After that, I would then choose the appropriate dropdown and click ok (or continue?). The macro will then add a customer footer to those files based on the footer label I have chosen.


Thanks guys!!

 

[Trevor G]

You can use a userform to add a combo box based on your drop down information, and via the xl dialog box open you can select multiple workbooks to open and then apply to each workbook. But the question I would ask do you want the Footer to go on every single worksheet in each book that you open?

So I would imagine open selected workbooks then each worksheet in the activeworkbook add footer (which side, left, centre or right) with the value selected from the drop down on a userform, then Save the activeworkbook and close it.

What happens if the workbook already has a footer?

What needs to be in the drop down selection, data from a cell or written contents you add in the code side?

If you can supply answers then the code can be created.

 

[lord_legrand]

Thank you Trevor for taking time to answer my query. My answers are as follows:


You can use a userform to add a combo box based on your drop down information, and via the xl dialog box open you can select multiple workbooks to open and then apply to each workbook. But the question I would ask do you want the Footer to go on every single worksheet in each book that you open? - Im afraid I need to have the footer in every worksheet of each workbook that I shall choose.


So I would imagine open selected workbooks then each worksheet in the activeworkbook add footer (which side, left, centre or right) with the value selected from the drop down on a userform, then Save the activeworkbook and close it. Correct..It should be centre, and the activeworkbooks need to be saved and closed.


What happens if the workbook already has a footer?

Would it be possible to append the footers that I require? If not then we have to delete the old footers.


What needs to be in the drop down selection, data from a cell or written contents you add in the code side?

It should be data from a cell.

If you can supply answers then the code can be created.

 

[Trevor G]

I have created a userform in VBA and added 3 features, 1 Combo box and named it cboNames. 2 Command Buttons.

Code behind each feature is as follows:

Behind the form when it is shown, called Initialize, it fills the drop down from data on sheet 2, cahange to your sheet name.

Private Sub UserForm_Initialize()
Sheets("[COLOR=red]Sheet2[/COLOR]").Select
Range("B2").Select
    Do Until ActiveCell.Value = ""
        Me.cboNames.AddItem ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    Loop
    

End Sub

Code behind first button to show Open dialogue box, make sure you select from the drop down before clicking the button.

Private Sub cmdOpen_Click()
Application.Dialogs(xlDialogOpen).Show
End Sub

Next Command button to run code, this will run on the first open book and add the footer to each sheet, as you mentioned you might need to change the drop down, before you click the command button again to run on the next workbook, and so on.

Private Sub cmdAddFooter_Click()
Dim wksht As Worksheet
For Each wksht In Worksheets
    wksht.PageSetup.CenterFooter = Me.cboNames.Value
    
Next wksht
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

I hope it helps you out.

 

[lord_legrand]

Thanks Trevor!!