Prev: Procedure to list subs and fuctions in a custom xla
Next: insert rows and copying cells in column b,c, and d into new rows
From: Dave Peterson on 28 Dec 2009 20:07
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
From: Brian on 28 Dec 2009 22:24
I have a User Form with alot of Information on it. From this User Form I can
Open, Update, Save & Print each of the 3 Workbooks.

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

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

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

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

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

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

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




"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
> .
>
From: Brian on 28 Dec 2009 22:40
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
> .
>
From: Dave Peterson on 29 Dec 2009 08:38
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
From: Brian on 29 Dec 2009 10:22
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
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Procedure to list subs and fuctions in a custom xla
Next: insert rows and copying cells in column b,c, and d into new rows