Three line center header macro using input box

[rockymtngirl]

Can an input prompt just be given for the first line of a three line center header? How would you put in the rest of the script so that one is not having to "input" it each time too?

Domski replied to a previous post from a similarly posed question in 2012 wherein the following code was given in his answer:

Please Login or Register  to view this content.

What if you only want an input prompt for the subject of a three line center header such as in:

(Prompt for subject)
For Month Ended
November 30, 2016

My macro header is used for many workbooks, so the only thing that is consistent is the "For Month Ended" and the date scripts. Although I do have to go in to the code and change the date when a new month has arrived, I know of no other way to do this since I wouldn't want to have to type it in at each input prompt either.


Thanks!

[CK76]

For header with 3 lines. I'd do something like below.

Please Login or Register  to view this content.

DateSerial part is used to return last date for previous month (from current date).

[rockymtngirl]

Thank you so much. Sorry this is a late response...I get pulled away with other work for days on end.

This fix works very well, and was so simple to see. These things elude me as a novice VBAer. I very much appreciate the DateSerial part. However, in order to make it a string result instead of a numeric output (December 31, 2016 rather than 12/31/2016, for example), I used both a DateValue and a Format function. I had to do some digging to figure this out and still may not have it technically correct, but it worked for me.

I used the DateValue in front of the DateSerial part and then also found that I had to use a Format code in front of the DateValue part to get it the way I needed it.

Like so:

It's possible I have more than I need with that coding, but it did work.

Thank you kindly for your help.