[DrewMcK]

I have taken an existing macro which i found on this site for the "BeforeSave" time stamp and modified it.

I have run into a problem which I cannot solve. The code is listed below. My problem is with the filename (noted in red) I need this filename to be set through an input box. This filename will be in a six number format (long) ie. 456789

However, this input box only needs to be run once to get the filename, as the filename will be constant, but of course will vary from document to document. Essentially I dont want the input box popping up everytime you save, although if by some chance there is a typo in the filename, it would be nice to have some way of changing it.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each Sheet In ThisWorkbook.Sheets
Sheet.PageSetup.LeftFooter = "&""Arial,bold""&8Drew" & Chr(13) & "RDIMS" & " " & Filename & Chr(13) & "Last Edited: " & Format(Date, "dd-mm-yyyy") & " " & Time
Sheet.PageSetup.LeftHeader = "&""Arial,bold""&13&UIAS-SDA"
Sheet.PageSetup.RightHeader = "&""Arial,bold""&13&UDocument Title" & Chr(10) & "&9&U&BDraft - For Discussion Purposes Only"
Sheet.PageSetup.RightFooter = "&""Arial,bold""&8Page &P of &N"
Next Sheet
End Sub

Thank you for the original code found on this site, and also thanks for your help.

[Rollin_Again]

When the inputbox pops up the first time have it write the filename to an unused cell on in the workbook before saving. You can then include logic in your code to check if that cell is blank to determine whether or not to have the inputbox appear on subsequent saves. If there is a typo in the filename just clear the cell containing the filename and you will be prompted again.


Regards,
Rollin

[DrewMcK]

thanks Rollin, I have got it working perfectly.

Although there is now another twist.

Is it possible to set the font size for the header to be a cell value?

The purpose of this would be to allow users to easily set the size of the header they want, as the header is created pretty much entirly via the macro.

[Zack Barresse]

Yes. Post your code and the sheet/cell which the font size is located.

[Rollin_Again]

We always encourage people to try to learn how to solve their own issues. Here is something to get you started that shows how to do the left footer. Let's see if you can figure out the others. The code below assumes that the number that represents the font size is in cell A1.

As my good friend Zack B (Firefytr) always stresses, make sure to declare your variable vSize

vSize = Sheets("Sheet1").Range("A1").Value

Sheet.PageSetup.LeftFooter = "&" & Chr(34) & "Arial,bold" & Chr(34) & "&" & vSize & "Drew" & _
Chr(13) & "RDIMS" & " " & Filename & Chr(13) & "Last Edited: " & Format(Date, "dd-mm-yyyy") & " " & Time

Regards,
Rollin

[DrewMcK]

Got it, thanks.

The cell for the font size is on the last worksheet, specifically called "lists", is there anyway to have it set up so that when someone prints the entire workbook it will not print the "lists" worksheet, but when it is done printing the worksheet will still be visible?

I tried playing around with a beforeprint sub
sheets("lists").visible = false

to get it to at least hide it while printing, but I have had no luck.

Its not a major issue, it just screws up the page count and wastes ink/paper when you print the entire workbook. Thanks again to everyone who has contributed.

[Zack Barresse]

When you issue a print workbook command, it sends the entire workbook contents to the printer que. You would need to hide the sheet before the print goes, using a Workbook_BeforePrint routine. Do you already have one of those? If so, post it. If not, let us know so we can work on a solution.

[DrewMcK]

I have the basics... from the workbook code, not an individual sheet.

Public Sub Workbook_beforeprint(cancel As Boolean)

Sheets("lists").Visible = False

End Sub

it doesn't seem to hide it though.... If i throw a msg box in there it fires but......

I also need to unhide it after printing as there are cell values which will need to be changed. Although I didn't put anything like that in the code i was just trying to at least hide it first, then work on unhiding it.

Sorry about the poor code, i wasn't sure how else to hide it.....

Thanks Again

[Zack Barresse]

The problem comes into play where you do not know what is being sent to print. So trying to control a print event [content] is difficult and next to impossible with the current OM. What you are better off doing is writing routines to print what you want, or setting up a userform to emulate the Print dialog box and having the controls do what you want them to do. That will give you the level of control you are asking for.

[Rollin_Again]

I agree with Zack. If you are the only one using this workbook I would add a seperate print button to your workbook or a form that will fire your own custom code telling the application which sheets to print. You can use a simple loop to go through each worksheet one by one and print only the sheets you want. If other people will be using the workbook you may want to disable the print menu in the workbook and only allow printing via button/form which will fire code similar to what is below.

Public Sub PrintSheets()

For Each ws In ActiveWorkbook.Sheets

If UCase(ws.Name) <> "LISTS" Then

ws.PrintOut

End If

Next

End Sub

Regards,
Rollin

[Zack Barresse]

You know, come to think of it, you could use an old dialog sheet to choose what sheets you want to print. Aaron Blood came up with the original idea and code. I amended it a short while back to add a "Print All" button to it. It works for the ActiveWorkbook. I commented the code up as well. Here is the code...

Sub SelectSheets()
    
     'Dimension all variables
    Dim i As Long, TopPos As Long, SheetCount As Long
    Dim PrintDlg As DialogSheet, CurrentSheet As Worksheet
    Dim cb As CheckBox, arrSheets() As String, wsCurr As Object
    
     'Turn off screen flickering
    Application.ScreenUpdating = False
    
     'Check if workbook is protected, exit if so
    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If
    
     'Set variables
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add
    SheetCount = 0
    TopPos = 40
    
     'Loop through all worksheets
    For i = 1 To ActiveWorkbook.Worksheets.Count
        
         'Reset the varaible 'CurrentSheet'
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
        
         'Check if there is any data on the worksheet and if it is visible
        If Application.CountA(CurrentSheet.Cells) <> 0 And CurrentSheet.Visible Then
            
             'Add one to the variable if we know it is a good sheet
            SheetCount = SheetCount + 1
            
             'Add one to the array, preserving all past values
            ReDim Preserve arrSheets(1 To SheetCount)
            
             'Set the current iteration (of the array) value
            arrSheets(SheetCount) = CurrentSheet.Name
            
             'Add the checkbox and name it the name of the valid worksheet
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
            PrintDlg.CheckBoxes(SheetCount).Text = CurrentSheet.Name
            
             'Adjust the top position of the form, keeping uniform spacing
            TopPos = TopPos + 13
            
        End If
        
    Next i
    
     'Add a little more for the top position, separating the Print All button from the rest
    TopPos = TopPos + 13
    
     'Add the Print All button
    PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
    PrintDlg.CheckBoxes(SheetCount + 1).Text = "Print All"
    PrintDlg.Buttons.Left = 240
    
     'Set the area for the dialog sheet/form
    With PrintDlg.DialogFrame
        .Height = Application.WorksheetFunction.Max(68, PrintDlg.DialogFrame.Top + TopPos - 15)
        .Width = 230
        .Caption = "Select sheets to print"
    End With
    
     'Bring buttons to the front (unkown if needed???)
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront
    CurrentSheet.Activate
    
     'Turn screen updating back on
    Application.ScreenUpdating = True
    
     'Check if there were any valid sheets
    If SheetCount <> 0 Then
        
         'If the dialog sheet was created..
        If PrintDlg.Show Then
            
             'Loop through each checkbox
            For Each cb In PrintDlg.CheckBoxes
                
                 'Test if the checkbox value was checked or not
                If cb.Value = xlOn Then
                    
                     'Check if the checkbox value was the Print All button
                    If cb.Text = "Print All" Then
                        
                         'Set sheet to return to
                        Set wsCurr = ActiveSheet
                        
                         'Select all sheets in the array which met the conditions
                        Sheets(arrSheets).Select
                        
                         'Print out selected sheets
                        ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False
                        
                         'Re-activate the last active sheet
                        wsCurr.Activate
                        
                         'Exit sheet at end of Print All routine
                        Exit For
                        
                    End If
                    
                     'If not the Print All button, activate the sheet and print it
                    Worksheets(cb.Caption).Activate
                    ActiveSheet.PrintOut
                    
                End If
                
            Next cb
            
        End If
        
    Else
        
         'If no sheets were counted, give a message box
        MsgBox "All worksheets are empty."
        
    End If
    
     'Turn off events to delete worksheet, else an alert will display
    Application.DisplayAlerts = False
    PrintDlg.Delete
    
     'Turn alerts back on
     'THIS IS A NEW ADDITION, NOT SPOTTED BEFORE
    Application.DisplayAlerts = True
    
     'Select the first sheet again ("Corp" was specific to the last users solution)
    CurrentSheet.Select
    
End Sub

HTH

[DrewMcK]

Wow, thanks alot guys. That print code is interesting, although not quite what I am looking for right now I can see it coming in handy down the road.

I've now got both my word and excel templates ready.

Thanks again, you guys really know your stuff.

[Keebellah]

Hi, I'm new to this forum. I was looking for different dialog options and VBA code for my Excel sheets when I came upon the Printer.Dlg code where you can choose the sheet you want to print.
I have altered the code in such a manner that you can use the checkboxes to select one or more items from a specific range in any sheet and whereever it is located.
If there is anyone intersetd I will post it.
What is does is you call the function giving the sheetname, column letter, starting rownumber and endding rownumber:
Call SelectFromList("Sheet3", "D" , 5, 25)
This will result in a Dialog screen with a list of 20 values in the range which you can select and then write that part of the code you need that does something with this.
The code allows any number of rows, The max per dialog screen is 200 spread over 4 columns of 50.
It was fun to do.
I am beginning to like VBA routines in my Excel sheets thus permitting users to enter only that data which is requested of making only the permitted selections.

Thanks for the attention.

Cheers,

Hans

[Zack Barresse]

Hello Hans,

As it is quite relevant to the thread, post it up.

[Keebellah]

Hi Firefytr,
I started a new thread and added my vba module with the code as a zipfile.
Still working on additions for column selections as well.
Cheers,
Hans