Re: Force Single-Sided Printing with Printout Method
I'm really sorry guys and/or gals. It appears I am a doofus.
I found yet another (this makes 4) place to change the double-sided option in the printer properties. I changed it to single-sided and now everything works perfectly.
Thank you so much for your time.
Don
Re: Force Single-Sided Printing with Printout Method
Ok this code is really bad as I'm self taught, but here are the 2 modules involved with the actual printing. I have also attached the workbook in case you need more context.
'
Dim rngPrint As Range
Dim rngTemp As Range
Dim rngAdd As Range
Private Sub add_to_rngprint()
On Error Resume Next
'rngTemp.column will only be 500 if it is the first time the routine has been run since variables were reset
If rngTemp.Column = 500 Then
'if true then rngAdd becomes the first page in rngPrint
Set rngPrint = rngAdd
'rngTemp must equal rngPrint when routine is completed
Set rngTemp = rngPrint
Exit Sub
End If
'only get this far if false, meaning this is not the first time through
'add rngAdd to end of rngTemp (remember rngTemp = rngPrint until this point)
Set rngPrint = Union(rngTemp, rngAdd)
'rngTemp must equal rngPrint when routine is completed
Set rngTemp = rngPrint
'go back to quick_print
End Sub
Sub Quick_Print()
On Error Resume Next
'checks each page for print needed and if true then adds page to running range "rngPrint"
'split into cutlists and inventory, pullsheets, drawings as 3 seperate print jobs
'set rngtemp = "SF1" for check in add_to_rngprint sub
Set rngTemp = Range("SF1")
'check if print needed = true
If Worksheets("Cut List").Range("N1").Value > 0 Then
'if true then send page to rngAdd to be used in add_to_rngprint
Set rngAdd = Range("Purchase")
Call add_to_rngprint
End If
'repeat for every page in MCL
If Worksheets("Cut List").Range("N63").Value > 0 Then
Set rngAdd = Range("Deductions")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("AU298").Value > 0 Then
Set rngAdd = Range("Shipping_List")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("A7").Value > 0 Then
Set rngAdd = Range("Cutlist_1")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("A68").Value > 0 Then
Set rngAdd = Range("Cutlist_2")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("A129").Value > 0 Then
Set rngAdd = Range("Cutlist_3")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("A190").Value > 0 Then
Set rngAdd = Range("Cutlist_4")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("A251").Value > 0 Then
Set rngAdd = Range("Cutlist_5")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("A312").Value > 0 Then
Set rngAdd = Range("Cutlist_6")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("A373").Value > 0 Then
Set rngAdd = Range("Cutlist_7")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("AT1").Value > 0 Then
Set rngAdd = Range("Banding_Detail1")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("AT65").Value > 0 Then
Set rngAdd = Range("Banding_Detail2")
Call add_to_rngprint
End If
'must print now because pull sheets need qty 2
rngPrint.PrintOut
Set rngPrint = Nothing
Set rngTemp = Range("SF1")
'begin adding pull sheets to rngPrint
If Worksheets("Cut List").Range("sheetcover_trigger").Value > 0 Then
Set rngAdd = Range("Sheetcover")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("andy_ran_trigger").Value > 0 Then
Set rngAdd = Range("andy_ran")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("maxx_biesse_trigger").Value > 0 Then
Set rngAdd = Range("maxx_biesse")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("cutting_trigger").Value > 0 Then
Set rngAdd = Range("Cutting")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("bumper_trigger").Value > 0 Then
Set rngAdd = Range("Bumper_Edge")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("counter_panel_trigger").Value > 0 Then
Set rngAdd = Range("Counter_Panel")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("cut_sew_trigger").Value > 0 Then
Set rngAdd = Range("Cut_Sew")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("upholstery_trigger").Value > 0 Then
Set rngAdd = Range("Upholstery")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("laminating_trigger").Value > 0 Then
Set rngAdd = Range("Laminating")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("wood_assembly_trigger").Value > 0 Then
Set rngAdd = Range("Wood_Assembly")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("solid_surface_trigger").Value > 0 Then
Set rngAdd = Range("Solid_Surface")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("metal_trigger").Value > 0 Then
Set rngAdd = Range("Metal")
Call add_to_rngprint
End If
If Worksheets("Cut List").Range("shipping_trigger").Value > 0 Then
Set rngAdd = Range("Shipping")
Call add_to_rngprint
End If
If Range("Rocky_Trigger").Value > 0 Then
Set rngAdd = Range("Rocky_PS")
'Yo, Adrian! I did it!
Call add_to_rngprint
End If
If Range("extra_ps_trigger").Value > 0 Then
Set rngAdd = Range("Extra_PS")
Call add_to_rngprint
End If
If Range("extra_ps2_trigger").Value > 0 Then
Set rngAdd = Range("Extra_PS2")
Call add_to_rngprint
End If
'must print now or drawings will not print. idk why
rngPrint.PrintOut copies:=2, collate:=False
'reset range variables
Set rngPrint = Nothing
Set rngAdd = Nothing
Set rngTemp = Range("SF1")
'begin adding pages from drawings tab to rngPrint
Dim nameDrawing As Name
'loop through all named ranges
For Each nameDrawing In ActiveWorkbook.Names
If nameDrawing.RefersToRange.Visible = True Then 'check that the named range is visible (hidden named ranges are sometimes created by excel in workbooks using iferror() or sumifs()
If nameDrawing.RefersToRange.Worksheet.Name = "Drawings" Then 'check if the named range is on the "Drawings" tab
If nameDrawing.RefersToRange.Item(1, 10).Value > 0 Then 'check that the top right cell is greater than 1
Set rngAdd = nameDrawing.RefersToRange 'add range to print set
Call add_to_rngprint
End If
End If
End If
Next nameDrawing
'print pages from drawings tab
rngPrint.PrintOut
End SubI have several instances of identically structured workbooks that choose which pages (ranges) to print based on set criteria. A macro runs through the list of pages and adds all which meet the criteria to a variable named rngPrint using union. When all pages have been checked and either added or skipped, I simply use rngPrint.printout. This method has been working great until we got a new printer with a double-sided print option. Now, everything prints double-sided even though the setting is turned off in the printer menu AND the printer properties within Excel. These documents cannot be double-sided as many pages go to different places.
Is there any way to force excel to print single-sided with the printout method?
My current workaround is to send every page as a separate job, but that is not a good long term solution since it takes so much longer and we print hundreds of pages a day.
Thanks in advance!
Don
Re: Add-in not loading everytime for some users.
Thanks for the reply. Sorry I didn't see it sooner.
The issue actually seems to have resolved itself. I did not change anything, but it just started working. Now every user has the add-ins without any issues reported. Ghosts in the machine I guess.
Thanks again!
Hey All,
I am using Decision Models's "AddLoaderV2.xla" ( http://decisionmodels.com/downloads.htm ) to distribute add-ins to multiple users at work (right now that is only 4). The AddLoader and all add-ins are stored in the same shared folder on our network. For 2 of the users, the add-ins are only loaded if the user loads excel first and then opens a workbook, rather than double-clicking on a workbook to open Excel and the workbook simultaneously, the other 2 users get the add-ins no matter how they open Excel.
Is it a problem with the way I loaded the add-in or is it something wrong with the individual user's personal settings or something else altogether?
Thanks!
Re: userform textbox, restricting special character entry
Try pasting this into the code of the userform. Obviously, this only works for "textbox1"
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc("A") To Asc("Z")
Case Asc("a") To Asc("z")
Case Else
KeyAscii = 0
End Select
End SubRe: ActiveWorkbook.Names returns "=#NAME?"
Quote from rory;773158IIRC whenever you use a new (i.e. post 2003) function, a hidden name is created. I assume it has something to do with backwards compatibility - possibly how they made the compatibility pack work.
Just an update: You turned out to be 100% right. I am using the iferror() function and it is causing excel to add the named range specified earlier. I just wrote a simple if to skip over any hidden named ranges since it appears sumifs() and other new functions can cause similar errors.
Thanks!
Re: ActiveWorkbook.Names returns "=#NAME?"
Thanks for the reply.
Here was the output for the errant range:
_xlfn.IFERROR =#NAME? False
I didn't think of the possibility of a hidden range, as I have never used them. I ran the following code:
Sub test()
Dim nameDrawing As Name
For Each nameDrawing In ActiveWorkbook.Names
nameDrawing.Visible = True
Next nameDrawing
End Subthen deleted the offending range from the "Name Manager".
Hopefully, my other code will work now that there are no errant ranges.
Thanks, Rory!
Don
Hey All,
I am trying to set up some code that loops through the named ranges in a workbook and am encountering some errors. Some digging led me to discover that the the first named range the code finds is "=#NAME?"
Example:
Sub test()
Dim nameDrawing As Name
For Each nameDrawing In ActiveWorkbook.Names
Debug.Print nameDrawing.Value
Next nameDrawing
End Subreturns
=#NAME?
='Cut List'!$AU$1:$BF$63
='Cut List'!$AU$65:$BF$127
='Cut List'!$AF$82:$AP$107
etc....
in the immediate window.
The name manager window does not show any named ranges with any errors that I can see.
If anyone has any ideas on what could be causing the errant named range, I would appreciate the help.
Thanks, Don