Hi all,

I am hoping someone can solve this little problem. I have a workboo
that has a footer changing all the time and the users arent to
computer literate, especially at changing footers.

I would like to automate the process with possibly a prompt box o
pop-up where a user simply inserts the footer info. It can be triggere
upon opening or a command button???

Please, any ideas?????

Cheers!!!

--
Message posted from http://www.ExcelForum.com

Sure! Just use the InputBox function in the Workbook_Open event of the
ThisWorkbook object, like so:

Option Explicit
Public strFooter As String

Private Sub Workbook_Open()
strFooter = InputBox("Please enter the footer:")
End Sub


Regards,
Wes

Hi Wes,

Thank you!!!!

Does the information typed into the input box actually copy as a heade
because nothing transferred when i typed in information.

Cheers!!

--
Message posted from http://www.ExcelForum.com

Hello, gavmer!

Well, actually you do need a little more code to move the text from the input
box to the header or footer. I wasn't sure I should get into that without
knowing how many worksheets are in the workbook or which header/footer you
wanted to change.

Let's assume the workbook contains only one sheet and that you want to modify
the center footer. Here's how that program code would look.

Option Explicit
Public strFooter As String
Private Sub Workbook_Open()
strFooter = InputBox("Please enter the footer:")
ActiveSheet.PageSetup.CenterFooter = strFooter
End Sub

If you wish to modify any other of the headers or footers, just substitute one
of the following keywords for ".CenterFooter" in the above example.

.LeftHeader
.CenterHeader
.RightHeader
.LeftFooter
.RightFooter

If the workbook contains two or more worksheets, you'll have to specify the
name of the one you want to change, as in the following example.

Option Explicit
Public strFooter As String
Private Sub Workbook_Open()
strFooter = InputBox("Please enter the footer:")
Sheets("your worksheet name").PageSetup.CenterFooter = strFooter
End Sub

I hope this explains it better.

Regards,
Wes

That was spot on. Thank you very much!!!!

--
Message posted from http://www.ExcelForum.com

Hi all,

I have another problem unfortunately. This code is listed within
template:

Option Explicit
Public strFooter As String
Private Sub Workbook_Open()
strFooter = InputBox("Please enter the footer:")
Sheets("your worksheet name").PageSetup.CenterFooter = strFooter
End Sub

I would like for this to apply only when the template is opened and no
when the saved workbook is opened. problem is the footer is a referenc
and when a user opens the workbook, the original reference from th
template can be altered.

Any ideas??

--
Message posted from http://www.ExcelForum.com

Option Explicit

Private Sub Workbook_Open()
Dim strFooter As String
if me.path = "" then 'it's never been saved, so it's a template
strFooter = InputBox("Please enter the footer:")
Sheets("your worksheet name").PageSetup.CenterFooter = strFooter
end if
End Sub

(I moved the strFooter to inside the procedure. From your snippet, it looked
like it was local to that procedure.)

"gavmer <" wrote:

Hi all,

I have another problem unfortunately. This code is listed within a
template:

Option Explicit
Public strFooter As String
Private Sub Workbook_Open()
strFooter = InputBox("Please enter the footer:")
Sheets("your worksheet name").PageSetup.CenterFooter = strFooter
End Sub

I would like for this to apply only when the template is opened and not
when the saved workbook is opened. problem is the footer is a reference
and when a user opens the workbook, the original reference from the
template can be altered.

Any ideas???

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Hi Dave,

Thanks for the assistance. Im having trouble implementing this. When
place this code into my template and save it, it disappears when th
template is opened next. The template should prompt the user each tim
the template is opened. The tempate is basically the tool to attach
reference in the footer and is not relevant to the workbook as it neve
should be altered.

Could you please assist??

Cheers!!

--
Message posted from http://www.ExcelForum.com

Are you placing it in the template or are you putting in a workbook based on
that template?

Make sure you open the .xlt file--not File|new.



"gavmer <" wrote:

Hi Dave,

Thanks for the assistance. Im having trouble implementing this. When i
place this code into my template and save it, it disappears when the
template is opened next. The template should prompt the user each time
the template is opened. The tempate is basically the tool to attach a
reference in the footer and is not relevant to the workbook as it never
should be altered.

Could you please assist??

Cheers!!!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Dave, you must pardon my idiocy. I have a copy on my desktop i as usin
but was saving the changes as a template which defaulted to the fil
located in my templates folder....not the desktop item!!!!!

All is ok but i have another request, how do i save the formatting i
font size:

size 9, arial, bold italic.....it seems to be defaulting to size 12??

Thanks again Dave!!

--
Message posted from http://www.ExcelForum.com

I've never had a problem saving the font attributes.

If you're trying to set this for each cell in the worksheet, make sure you
select your range first (ctrl-A to get all the cells), then save (to the correct
spot <vvbg).

(ctrl-A twice in xl2003)

"gavmer <" wrote:

Dave, you must pardon my idiocy. I have a copy on my desktop i as using
but was saving the changes as a template which defaulted to the file
located in my templates folder....not the desktop item!!!!!

All is ok but i have another request, how do i save the formatting ie
font size:

size 9, arial, bold italic.....it seems to be defaulting to size 12??

Thanks again Dave!!!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson