Printing from a control button

[Brian]

I am trying to get a WorkBook to print from a user Form Control Button. I am
not exactly sure how to get the Print Dialog Box to come up, so I can choose
the printer, Print Rage, Print What, Number of Copies & Print Preview.

The Name of the WorkBook will Change each time, I am not sure how to
acomplish this task.

Control Button = Print_Eng_Spec_12_Click()

This is the save method I am using, So you can see what information is being
saved as the Workbook Name.

' Save Eng Spec 11 Control Button
Private Sub Save_Eng_Spec_11_Click()

Dim strFile As String
Dim bk As Workbook

Set bk = ActiveWorkbook

strFile = "SPEC " & TEO_No_1.Value _
& Space(1) & CLLI_Code_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile)

If FileToSave = False Then

MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S."

Exit Sub

End If

End Sub

 

[Dave Peterson]

Excel has a dialogs collection that you can use.

I don't think you'd want the 2nd or 3rd ones--since there's a print preview on
the top dialog.

Me.Hide
Application.Dialogs(xlDialogPrint).Show
'Application.Dialogs(xlDialogPrinterSetup).Show
'Application.Dialogs(xlDialogPrintPreview).Show
Me.Show

 

[Brian]

Can I be more spec, about what file to print.

Like the open Spec File:

strFile = "SPECL " & TEO_No_1.Value _
& Space(1) & CLLI_Code_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

 

[Brian]

Hi Dave,

Nice to here from you again. I hope you had a nice Christmas.

By the way thanks for all your help on this project.

Brian

 

[Brian]

Is it possible to be more specific as to which file to print, based on the
file name?

I am really not sure how to do this because the file name will vary. The
only constant will be "Spec" in the name. I was thinking maybe a pop up list
of the documents open with "Spec' in the name and then being able to pick
which one to print.

strFile = "SPEC " & TEO_No_1.Value _
& Space(1) & CLLI_Code_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

If you have any ideas on this please tell me.

 

[Dave Peterson]

I haven't followed your posts about your project. I'm not sure what you're
printing.

Are you going to open a different workbook in a specified folder using that
strFile variable and print one (or more) of the sheets?

If that's close, maybe this will get you closer...

Dim wkbk as workbook
'stuff to determine the name of the workbook

on error resume next
set wkbk = workbooks.open(filename:="C:\somepath\" & strFile & ".xls")
on error goto 0

if wkbk is nothing then
msgbox "That file couldn't be opened--in use or doesn't exist???"
else
wkbk.worksheets("Somesheetnamehere").printout preview:=true
end if

But that's just a guess.

 

[Brian]

I have a User Form with alot of Information on it. From this User Form I can
Open, Update, Save & Print each of the 3 Workbooks.

1: Eng Spec
2: Job Forms
3: Folder Label

Each Funtion on the User Form has 3 Control Buttons.
Functions are as follows:
1: Open New Documents (Used for opening New Workbooks)
2: Open Exsisting Documnets (Used for Editing exsisting Workbooks)
3: Update Documents (Used for Putting info from User Form
to Doc)
4: Save Documents (Used for Saving Workbooks, Auto
Naming)
5: Print Documents (Used for Printing each type
Workbook)

3 Control Buttons for each Function
1: Eng Spec
2: Job Forms
3: Folder Label

When each Document Updates it Automatically updates Workbook Info including
Header & Footnotes on all worksheets. If you think about how long it would
take just to Update the Header/Footnote (2 Workbooks @ 20 Sheets each). Now
it is auotmated. 2 of the 3 Works are completed and ready to print just form
updating off the User Form (Job Forms & Folder Label).

Before this project I was having to type all this information 3 seperate
times taking hours and hours. I only have a few items left to address on this
project.

1: Print Control Buttons
2: Progress Bar for when Updating (Takes a min or two to complete)
3: Final Clean Up & Minor Changes

Oh course, as with all software programs "Are they really ever Done"? I am
sure there will updates and changes after the fact, but that should be minor.

 

[Brian]

What I am trying to do is set up the print Control Buttons.

Control Button = Print_Eng_Spec_12

When this button is clicked it provide a list of open Wookbooks with the
"Spec" in the Name.

Now in order for this to work the wookbook would have to be saved first
since my save Control Button automatically assigns the Name as the following:

strFile = "SPEC " & TEO_No_1.Value _
& Space(1) & CLLI_Code_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

That way when the Print Button looks at the open Workbooks it see's the
"Spec" in the name and puts it on the list of possible Workbook's to print.
Then the user can pick which Workbook to print.

Does that make any sense?

 

[Dave Peterson]

I created a small userform with a listbox, a label and two commandbuttons.

The listbox holds the names of the files that start with SPEC.

The label is for error/warning messages.

The first commandbutton is used to look through the open workbooks and create
the entries for the listbox.

The second commandbutton is used to print the selected items in that listbox.
(I used a msgbox rather than any printing code.)

Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim wkbk As Workbook

Me.ListBox1.Clear 'clear existing entries.

iCtr = 0
For Each wkbk In Application.Workbooks
If LCase(Left(wkbk.Name, 4)) = LCase("spec") Then
iCtr = iCtr + 1
Me.ListBox1.AddItem wkbk.FullName 'or just name
End If
Next wkbk

Me.Label1.Caption = ""
If iCtr = 0 Then
'no matches found
Me.Label1.Caption = "No names meet criteria"
Me.CommandButton2.Enabled = False
End If

End Sub
Private Sub CommandButton2_Click()

Dim iCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
MsgBox "Print " & .List(iCtr) & " code here"
End If
Next iCtr
End With

End Sub

Private Sub ListBox1_Change()

Dim iCtr As Long
Me.CommandButton2.Enabled = False
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.CommandButton2.Enabled = True
Exit For
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 1
.RowSource = ""
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
End With

Me.Label1.Caption = ""

With Me.CommandButton1
.Caption = "Get names of SPEC files"
.Enabled = True
End With

With Me.CommandButton2
.Enabled = False
.Caption = "Print SPEC Files"
End With

End Sub




Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim wkbk As Workbook

Me.ListBox1.Clear 'clear existing entries.

iCtr = 0
For Each wkbk In Application.Workbooks
If LCase(Left(wkbk.Name, 4)) = LCase("spec") Then
iCtr = iCtr + 1
Me.ListBox1.AddItem wkbk.FullName 'or just name
End If
Next wkbk

Me.Label1.Caption = ""
If iCtr = 0 Then
'no matches found
Me.Label1.Caption = "No names meet criteria"
Me.CommandButton2.Enabled = False
End If

End Sub
Private Sub CommandButton2_Click()

Dim iCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
MsgBox "Print " & .List(iCtr) & " code here"
End If
Next iCtr
End With

End Sub

Private Sub ListBox1_Change()

Dim iCtr As Long
Me.CommandButton2.Enabled = False
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
Me.CommandButton2.Enabled = True
Exit For
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 1
.RowSource = ""
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
End With

Me.Label1.Caption = ""

With Me.CommandButton1
.Caption = "Get names of SPEC files"
.Enabled = True
End With

With Me.CommandButton2
.Enabled = False
.Caption = "Print SPEC Files"
End With

End Sub

 

[Brian]

Where does all this code go? I created the "UserForm3" exactly as you
described it. Will this automatically list all the open files with 'Spec in
the Name?

 

[Dave Peterson]

The code has to be incorporated into your userform--I didn't want to recreate
your userform, so I created my own little userform.

And it only includes the workbooks that have names that start with SPEC.

You could use instr() or like if that SPEC string could be any place in the
workbook name.

 

[Dave Peterson]

ps. You could create a new form and hide the calling form and show this one--or
you could use a multipage form--or even just add a few controls to the existing
form????

 

[Brian]

What Controls would I need to add or change? As of now I have the Following:
You will see each control button has a number, that is the frame number the
button is located in.

Open new Documents Frame 8 - For Opening New Templetes
1: Eng Spec Sheet Open_New_Engineer_Spec_8
2: Install Forms Open_New_Installer_Forms_8
3: Folder Label Open_New_Job_Folder_Label_8
Open Exsisting Documents Frame 9 - For Old Documents for editing
1: Eng Spec Sheet Open_Existing_Engineer_Spec_9
2: Install Forms Open_Existing_Installer_Forms_9
3: Folder Label Open_Existing_Job_Folder_Label_9
Update Documents Frame 10 - For Sending Info to Workbooks
1: Eng Spec Sheet Update_Engineer_Spec_10
2: Install Forms Update_Installer_Forms_10
3: Folder Label Update_Job_Folder_Label_10
Save Documents Frame 11 - For Assigning and Saving files
1: Eng Spec Sheet Save_Engineering_Spec_11
2: Install Forms Save_Installer_Forms_11
3: Folder Label Save_Job_Folder_Label_11
Print Documents Frame 12 - For Printing Open Workbooks
1: Eng Spec Sheet Print_Engineering_Spec_12
2: Install Forms Print_Installer_Forms_12
3: Folder Label Print_Job_Folder_Label_12

It might be easier to just have 1 Print Control button that lists all open
workbooks to print. That way all open files are shown and the user can pick
which one to print. The only reason i did 3 Print buttons was because
sometimes I only need to print 1 and not all 3, so that gives me max
flexibility, but is good if all oopen file are shown.

 

[Dave Peterson]

I would think the save and print options would need some kind of "which of the
open files do you want to save/print" question associated with them.

So maybe you could add another listbox to them. And populate the names when
that multipage(?) is selected???

Then the button to save/print would check the listbox to see what's selected and
save/print just those selected items.

 

[Brian]

Can you check your code you sent to me. I am having a hard time following it.
Is it possible that some of the code is duplicate? I don't know enough to
make that determination. Some of it is shown 2 times. Did it need to be in
there twice?

 

[Dave Peterson]

You're right.

I pasted the code in twice. It's identical.

Just ignore the second copy of the code. Each starts with "Option Explicit".

 

[Brian]

Can you look at this code, it seemed work yesterday. Today for some reason it
wants to save the file as "all'files". It is suposed to let me choose what
type extension to assign it (xls, xlsm,xlst, etc....)

It assignes the correct file name, but the extension is not available.


' Save Engineering Spec 11 Control Button
Private Sub Save_Engineering_Spec_11_Click()

Dim strFile As String
Dim bk As Workbook

Set bk = ActiveWorkbook

strFile = "SPEC " & TEO_No_1.Value _
& Space(1) & CLLI_Code_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile)

If FileToSave = False Then

MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S."

Exit Sub

End If

End Sub

 

[Dave Peterson]

From what I've read on Ron de Bruin's site, you'll want to specify the
FileFormat parm on the .saveas line.

http://www.rondebruin.nl/saveas.htm

If this doesn't help, what are you typing into that dialog?