Printing from a control button
in [Programming]
|
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 29 Dec 2009 19:33 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
From: Brian on 29 Dec 2009 20:29 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? > > > > > > > > > > >
From: Dave Peterson on 29 Dec 2009 20:59
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 |