[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 wrote:

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" wrote:

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????

Dave Peterson wrote:

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.

Brian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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."

--

Dave Peterson

[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" wrote:

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 wrote:

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" wrote:

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????

Dave Peterson wrote:

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.

Brian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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" wrote:

Excel has a dialogs collection that you can use.

[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 wrote:

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" wrote:

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 wrote:

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" wrote:

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????

Dave Peterson wrote:

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.

Brian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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" wrote:

Excel has a dialogs collection that you can use.

--

Dave Peterson

[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" wrote:

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 wrote:

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" wrote:

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 wrote:

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" wrote:

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????

Dave Peterson wrote:

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.

Brian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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

[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?



Brian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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" wrote:

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????

Dave Peterson wrote:

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.

Brian wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson