01-27-2011, 04:40 AM
I have a userform set up which allows the user to choose some headers and footers, and apply them to either the active worksheet or to all worksheets in the workbook (using a simple radio button). What I would like to do is add a third option, "Let me select certain worksheets".
I anticipate that this could then be followed by another userform, populated by the list of worksheets with a check box next to each. This is the first thing I cannot work out.
Then, after the user has selected the worksheets, I'm not sure how to apply my headers and footers to only those that were checked.
For the "all worksheets" option, I have something like this:
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
Followed by the headers and footers code. I assume I could adapt this in some way, but I'm not exactly sure how.
Could anyone help at all?
Thanks a lot.
01-27-2011, 07:51 AM
This sounds like a fun project, I'm intrigued.
Anyway, the first issue can be resolved using a control called ListView. The ListView control is similar to a ListBox in terms of appearance, but it is much more powerful. When the user selects the third option, say "Selected Worksheets" the second form you mention could pop-up modally. During it's intialization process it could load all the worksheets of the current workbook. You can set the ListView to display checkboxes, meaning the user would be able to select the worksheets he/she wishes to add a header/footer to. Once the user is finished, you could store selected sheets in a public list to be accessed by the code when the header/footer is being created.
That's issue 1. The second 1 is much more simple. You would basically take the code you ahve just now for assigning headers and footers, and instead of using the current sheet, you would loop through each of the selected sheets.
Post some sample code if you want me to go into it further. Failing that, google the ListView control and you'll find how to work it and hopefully you should be able to do the needful from that.
01-27-2011, 08:22 AM
Thanks a lot for this - I'll let you know how I get on!
01-27-2011, 11:07 AM
Hm, I'm having some problems with this. Do you think you could point me towards an example ListView in Excel? I'm not even sure how to implement one at the moment, and googling isn't helping.
I also found this (http://www.officekb.com/Uwe/Forum.aspx/excel-prog/50033/Creating-Temporary-Userform-with-checkboxes) (which isn't a listview as far as I can tell), which seems to do something similar, though I can't work out how to adapt it.
Thanks for your help.
01-27-2011, 11:36 AM
Sorry I'm just running away from work although I will pick up later when I get home. But just quickly, if you create a blank excel workbook just for test purposes, create a userform called frmTest, create a command button called cmdDone and a ListView control called lvwWS and copy the following code:
Private Sub UserForm_Initialize()
' This code runs when the form is first initialized.
Dim ws As Worksheet
' Loop through each worksheet in the active workbook and add to the listvoew (lvwWS)
For Each ws In ActiveWorkbook.Worksheets
Private Sub cmdDone_Click()
' Now the user has selected what worksheets they require, we can store their names in the
' wsList array for retrieval later
Dim li As ListItem
Dim i As Integer
' First redim the wsList array to clear previous data and ensure it is ready to receive data
' Loop through all worksheets in the listview (lvwWS) and add the selected ones to wsList
For Each li In lvwWS.ListItems
If li.Checked Then
ReDim Preserve wsList(UBound(wsList) + 1)
wsList(UBound(wsList)) = li.Text
' We have successfully stored all the data. Dump to Immediate window and close the form.
For i = 1 To UBound(wsList)
Then create a standard module called modData and copy this code:
' Public variable used to store selected worksheets list
Public wsList() As String
Now if you run the macro, you will see a form populate with all the sheets within the current workbook - usually Sheet1, Sheet2 and Sheet3 for blank workbooks. When you hit done, it will print the names of the sheets you selected to the Immediate Debug Window. If you can't see this, press Ctrl + G when you're in Microsoft Visual Basic Editor.
Hopefully that shows how easy it is to get the sheet from the current workbook and also pass them to other parts of the program.
Have a little play arround with diferent numbers of sheets and changing their names and let me know how you get on and I'll check later when I get home.