From: Dave Peterson on
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."
> > > > > > >
> > > > > > > Exit Sub
> > > > > > >
> > > > > > > End If
> > > > > > >
> > > > > > > End Sub
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > > .
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > > .
> > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
From: Dave Peterson on
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."
> > > > > > > >
> > > > > > > > Exit Sub
> > > > > > > >
> > > > > > > > End If
> > > > > > > >
> > > > > > > > End Sub
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > > .
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > > .
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

--

Dave Peterson
From: Brian on
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."
From: Dave Peterson on
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
From: Brian on
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.