Change Header & Footer Info w/ Worksheet Option Buttons

[Ryan H]

I have a workbook named QUOTES that uses a referenced add-in workbook named
Add-In which contains all code to manipulate QUOTES. I have 2 option buttons
on Sheet1 in QUOTES. One named optAdTech and the other optFormetco. These
option buttons represent two different companies. I need to change the
Header and Footer information to whichever company option button is true when
the quote is printed in the BeforePrint Event in QUOTES.

What is the best way to do this? Note: Left Header is company logo, Right
Header is company address, Center Footer is text.

Would it be possible to save the AdTech's header/footer information and
Formetco's header/footer information in the Add-In and then apply it in
QUOTES? For example:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Select Case True

Case optAdTech
' call AdTech header/footer data from add-in

Case optFormetco
' call Formetco header/footer data from add-in

End Select

End Sub

 

[Dave Peterson]

I think I'd have the addin create a toolbar (or modify the ribbon in xl2007 if
you're industrious!) that gave the user a way to print the sheet. And disable
printing in that workbook (all workbooks that use the addin).

Private Sub Workbook_BeforePrint(Cancel As Boolean)
cancel = true
msgbox "Print this sheet by using the button on the toolbar!"
end sub

Then you could have your print routine in the addin work against the
activesheet--or activeworkbook. You could keep all your data in each individual
workbook.

If the data is always the same, I would keep a single copy in the addin
instead. I don't like having to update lots of workbooks because a company
changed names (or didn't like my typos!).

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

=============================
Another alternative that is a little more complex (I think) is to have an addin
that monitors things at an application level.

I'd add something unique to each workbook that can use the addin (a hidden name
or a value on a hidden sheet -- anything that you can check to make sure you're
processing a workbook that qualifies for your _BeforePrint routine.

You can read more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.aspx

If you want to try, then this would go in the ThisWorkbook module of your addin
(or merged into those subroutines--or in a new addin????).

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)

Dim TestRng As Range
Set TestRng = Nothing
On Error Resume Next
Set TestRng = Wb.Worksheets("hiddensheet").Range("a1")
On Error GoTo 0

If TestRng Is Nothing Then
'not one of the "good" workbooks
Exit Sub
End If

'check to see if the workbook can/should be printed
'this could be a formula that validates input???
If LCase(TestRng.Value) <> LCase("ok") Then
Cancel = True
MsgBox "Not available (yet???) to print"
Exit Sub
End If

'do your work here
MsgBox "work to do"

End Sub