Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I shorten this?
Is there a way to shorten this so that I do not have to type so muc out? Because I have a couple cells I need it to read with differen From and To's. And it would be a very long VBA. Private Sub CommandButton1_Click() Select Case Range("Quanity").Value Case 1 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1 Collate _ :=True Case 2 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=2 Collate _ :=True Case 3 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=3 Collate _ :=True Case 4 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=4 Collate _ :=True Case 5 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=5 Collate _ :=True Case 6 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=6 Collate _ :=True Case 7 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=7 Collate _ :=True Case 8 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=8 Collate _ :=True Case 9 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=9 Collate _ :=True Case 10 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=10 Collate _ :=True Case 11 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=11 Collate _ :=True Case 12 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=12 Collate _ :=True End Select End Sub Thanks in advance, TyeJa -- TyeJa ----------------------------------------------------------------------- TyeJae's Profile: http://www.excelforum.com/member.php...nfo&userid=723 View this thread: http://www.excelforum.com/showthread.php?threadid=32030 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I shorten this?
One way:
ActiveWindow.SelectedSheets.Printout _ From:=1, _ To:=1, _ Copies:=Range("Quantity").Value, _ Collate:=True In article , TyeJae wrote: Is there a way to shorten this so that I do not have to type so much out? Because I have a couple cells I need it to read with different From and To's. And it would be a very long VBA. Private Sub CommandButton1_Click() Select Case Range("Quanity").Value Case 1 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _ :=True Case 2 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=2, Collate _ :=True Case 3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I shorten this?
Hi TyeJae, You should be able to use the range value more directly since the valu corresponds exactly, rather than going to the trouble of the Selec Case: Dim numCopies as integer numCopies = Range("Quanity").Value ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=numCopies _Collate:=Tru -- talkswithnumbe ----------------------------------------------------------------------- talkswithnumber's Profile: http://www.excelforum.com/member.php...fo&userid=1684 View this thread: http://www.excelforum.com/showthread.php?threadid=32030 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I shorten this?
It says it is having a syntax error when I try that formula...an suggestions? Thanks, TyeJa -- TyeJa ----------------------------------------------------------------------- TyeJae's Profile: http://www.excelforum.com/member.php...nfo&userid=723 View this thread: http://www.excelforum.com/showthread.php?threadid=32030 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I shorten this?
nm I figured it out, you had _Collate when it should be Collate_. Tha works GREAT and will save me a ton of time...thank you. TyeJa -- TyeJa ----------------------------------------------------------------------- TyeJae's Profile: http://www.excelforum.com/member.php...nfo&userid=723 View this thread: http://www.excelforum.com/showthread.php?threadid=32030 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I shorten this?
Sorry, I copied the line from your post but didn't realize the _ wa still there after I added the numCopies bit. Glad it worked ou though.. -- talkswithnumbe ----------------------------------------------------------------------- talkswithnumber's Profile: http://www.excelforum.com/member.php...fo&userid=1684 View this thread: http://www.excelforum.com/showthread.php?threadid=32030 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there away to shorten | Excel Worksheet Functions | |||
Can I shorten this any? | Excel Worksheet Functions | |||
Any way to shorten this up? | Excel Worksheet Functions | |||
Shorten A Name | Excel Discussion (Misc queries) | |||
Is there a way to shorten this? | Excel Programming |