Using UserForm to set PageSetup properties

[suiathon]

I'm just getting started with dabbling with UserForm and the few examples I've seen don't really make sense to me. I don't know how time consuming it would be for someone to help me out with this, but it would definitely make it easier to learn with my own examples. What I'd like to do is customize Page Setup using user inputs through a UserForm. If you have resources for me, that'd be helpful as well.

A few questions:
1) Is it possible to set the user inputs to variables to use within the PageSetup code? All inputs will be used in the left and right header and footer of the excel page.
2) Client fiscal year is commonly December or June, but sometimes it is an odd date. Is it possible to set it up as a custom user input, i.e. add another box below if those dont apply? I was able to populate the combobox with dates, but those errored out when I clicked on it.
3) If a user exits the screen, I'd like it to stop the script and not proceed.
4) How would I tell the UserForm to show up in my PageSetup code? Would it be as simple as calling the form name?
5) Is it possible to save user inputs in long-term? i.e. When they enter a new client name it saves to an array for future use? This seems more complicated than I need now, but would be cool to learn how to do.

I think once the UserForm coding is setup, I should be able to read it and understand what is going on. I just don't know how to start or what's necessary to get these things working. If this isn't possible, I'll just use my old template which requires users to go into PageSetup and edit.

HeaderandFooter.JPG

[6StringJazzer]

All of this is possible. Please attach your current file so we can see what you have so far. See yellow banner at top.

Describe what information you want in each header and footer field. Your screen shot shows some of the data you want to collect but not where you want to put it.

5) Is it possible to save user inputs in long-term? i.e. When they enter a new client name it saves to an array for future use? This seems more complicated than I need now, but would be cool to learn how to do.

Do you one file that is used for all clients? Or do you make a copy of this and use a separate one for each client?

The answer to this question is that it is possible, but the method depends on how you use the file. If you have one file for all clients, then you can have a worksheet that collects this information for each client.

[suiathon]

Give me a few minutes and I'll add some more explanations, but to answer your questions - each client often has multiple/separate excel files that have similar header/footers. Client name and fiscal year would be the same for that project, but the other 3 might change depending on who is completing the paper and what area they are working on.

I modified the userform to add a few other options I forgot to include. And changed to use Radiobuttons. I'll upload as soon I make some notes.

[suiathon]

Attached in prior post. I included end result in page, see Page Setup or Ctrl P to view print out.

Oh, I suppose another option would be to keep prior settings saved rather than saving it into an array. It only needs to be saved for the project being worked on.

[6StringJazzer]

In the code for the form, you have a sub called HeaderAndFooter_Initialize. You never call this. I am not sure what you intend there. For this code to run automatically when the form is created, it must be called UserForm_Initialize.

In that sub you have a number of references in the code for the form that don't exist, so it won't compile and therefore the form won't open if we fix the name. For example

Please Login or Register  to view this content.

There is no CityListBox.

There are two strategies for using a UserForm to collect data for something. One is to show the userform and do all the work in the UserForm. The other is to show the userform, then close it, then extract the data from the userform controls. I will be doing the first. I am moving your called HeaderFooter in Module1 into the userform code to run on the click event for the OK button.

You must always validate user input. I added that at the beginning of the sub.

[suiathon]

Woops, sorry - I must have copied in text from an example I found and forgot to delete it. I was going to see if I could figure it out while I waited for a reply. Thanks for what help you've given already - I'll take a look in a few hours when I'm not busy.

[suiathon]

I took a look - most of it seems to be working, however, I noticed it wasn't saving over the old stuff, so I added some clear header/footer code. It doesn't look like the fiscal year date is working. It is printing out as blank. Any thoughts there?

[suiathon]

I have updated the worksheet for page orientation and print grid line. I took a swing at the dates issue, but I don't think I have it quite right. I'm not very good at the user validation part. Is there a reference guide on that for common validation coding?

I went ahead and attempted to place the form into a Tab in Excel after figuring out how to "Save" data to a worksheet using ControlSource. The issue I came across is that the worksheet doesn't seem to appear when in XLAM form. Is there a better way? It works perfectly in a normal worksheet, but the end goal is to place several macros into a tab for multiple people to use as shortcuts.

[6StringJazzer]

I will look at this when I have time but I am spread a bit thin and have limited time for free threads. My guide for validation coding is 40 years of software development experience so I can't point you to anything, but this is such a great question I think I'll write one.

I am not following what you mean by "place the form into a Tab." Do you mean reprogram the form so it uses worksheet cells instead of a UserForm? XLAM is an add-in; there is nothing visible to the user.

"the end goal is to place several macros into a tab for multiple people to use as shortcuts." By here I am completely lost. I might be able to give some guidance if you can detail out what you want this to look like and how you want it to work.

[suiathon]

I am not following what you mean by "place the form into a Tab."

I meant a custom Ribbon Tab with a button to call the form and include a selection of other commonly used tools/macros.

"the end goal is to place several macros into a tab for multiple people to use as shortcuts."

There are at least 4 people that would use this and it would be nice to have them separate from each other so they can save their own work. For example, if George fills out the UserForm for one project, it ideally saves that information for the next workbook so he doesn't have to redo everything. What I had setup included a hidden sheet linked to ControlSource that saved the information, but XLAM based on what you said XLAM doesn't allow that. I think that works with Personal workbooks, but from what I can remember it wasn't user friendly to share.

I'm using the XLAM extension because it seemed like it would be the most user-friendly way to share the macro tab.


Thanks for helping out! No worries on time. I'm just fiddling around in my spare time.

[suiathon]

I did some more searching and one individual suggested using Save and Get settings to save the data. Are you familiar with that?

Edit: I figured this out after some searching and a lot of trial and error. My issue at the moment is validation... I'll continue messing with it, but it's nice to know I can save my inputs now.

[suiathon]

Here's my most recent update.