Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revised btnprint1_click()
yesterday, I posted a similar routine where I was trying
to pass along a control option to a print range, so that I could print selected reports based on selecting a print range that was passed along from this procedure. I was seeking help with that, but I think I've figured out another route that I still need help with. I have five options on a frame with the default option being the print all ranges. Someone suggested before I did the following cases that the caption in this script had to be a valid range name, but I didn't want to name them that way. So, I devised this attempt at using cases to name the print range based on the caption I listed in these cases. Where it appears to hang now when I step through it is in the line: Range(Printthisrange).Select As far as I could tell, if "Printhisrange" were defined as a range, then shouldn't I be able to pass along a name to that variable based on my cases here. And then when the cases selected the value of "printthisrange", shouldn't I be able to pass that along to the line listed above? And then once I've solved that mystery, really what I need to do is provide for the "Print All OBA Pages" case in which I need to select all four previous ranges for the print. I'm still very much a newbie at Visual Basic, but I think I'm learning quickly. Private Sub btnprint1_Click() Dim myOption As Control Dim Printoption Dim Printthisrange As Range With ActiveSheet.PageSetup .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 End With For Each myOption In Frame1.Controls If myOption.Value = True Then Printoption = myOption.Caption End If Next myOption Select Case Selectprnrange Case 1 Printoption = "Cantera Natural Gas" Printthisrange = "OBACGG" Case 2 Printoption = "DEFS" Printthisrange = "OBADEFS" Case 3 Printoption = "Agave" Printthisrange = "OBAAgave" Case 4 Printoption = "TWML" Printthisrange = "OBATWML" Case 5 Printoption = "Print All OBA Pages" End Select Unload Me Range(Printthisrange).Select Selection.PrintPreview Range("a1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revised btnprint1_click()
Bruce,
Since PrintThisRange is dimed as Range than Printthisrange = "OBACGG" should be Printthisrange = Range("OBACGG") you may also want to qualify the sheet Printthisrange = Sheets("Sheet1").Range("OBACGG") steve "Bruce Roberson" wrote in message ... yesterday, I posted a similar routine where I was trying to pass along a control option to a print range, so that I could print selected reports based on selecting a print range that was passed along from this procedure. I was seeking help with that, but I think I've figured out another route that I still need help with. I have five options on a frame with the default option being the print all ranges. Someone suggested before I did the following cases that the caption in this script had to be a valid range name, but I didn't want to name them that way. So, I devised this attempt at using cases to name the print range based on the caption I listed in these cases. Where it appears to hang now when I step through it is in the line: Range(Printthisrange).Select As far as I could tell, if "Printhisrange" were defined as a range, then shouldn't I be able to pass along a name to that variable based on my cases here. And then when the cases selected the value of "printthisrange", shouldn't I be able to pass that along to the line listed above? And then once I've solved that mystery, really what I need to do is provide for the "Print All OBA Pages" case in which I need to select all four previous ranges for the print. I'm still very much a newbie at Visual Basic, but I think I'm learning quickly. Private Sub btnprint1_Click() Dim myOption As Control Dim Printoption Dim Printthisrange As Range With ActiveSheet.PageSetup .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 End With For Each myOption In Frame1.Controls If myOption.Value = True Then Printoption = myOption.Caption End If Next myOption Select Case Selectprnrange Case 1 Printoption = "Cantera Natural Gas" Printthisrange = "OBACGG" Case 2 Printoption = "DEFS" Printthisrange = "OBADEFS" Case 3 Printoption = "Agave" Printthisrange = "OBAAgave" Case 4 Printoption = "TWML" Printthisrange = "OBATWML" Case 5 Printoption = "Print All OBA Pages" End Select Unload Me Range(Printthisrange).Select Selection.PrintPreview Range("a1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revised btnprint1_click()
Steve:
I made the change you suggested to put quotes around the line "Printthisrange = "OBACGG" including the sheet selection part as well, but it still stops with the same line "Range(Printthisrange).Select", and it didn't help if I put quotes around the printrange in the parenthesis. Also, I still don't know how I'm going to pass something out of case 5 to select all 4 print ranges to have them printed at the same time. So I need ideas on that also. -----Original Message----- Bruce, Since PrintThisRange is dimed as Range than Printthisrange = "OBACGG" should be Printthisrange = Range("OBACGG") you may also want to qualify the sheet Printthisrange = Sheets("Sheet1").Range("OBACGG") steve "Bruce Roberson" wrote in message ... yesterday, I posted a similar routine where I was trying to pass along a control option to a print range, so that I could print selected reports based on selecting a print range that was passed along from this procedure. I was seeking help with that, but I think I've figured out another route that I still need help with. I have five options on a frame with the default option being the print all ranges. Someone suggested before I did the following cases that the caption in this script had to be a valid range name, but I didn't want to name them that way. So, I devised this attempt at using cases to name the print range based on the caption I listed in these cases. Where it appears to hang now when I step through it is in the line: Range(Printthisrange).Select As far as I could tell, if "Printhisrange" were defined as a range, then shouldn't I be able to pass along a name to that variable based on my cases here. And then when the cases selected the value of "printthisrange", shouldn't I be able to pass that along to the line listed above? And then once I've solved that mystery, really what I need to do is provide for the "Print All OBA Pages" case in which I need to select all four previous ranges for the print. I'm still very much a newbie at Visual Basic, but I think I'm learning quickly. Private Sub btnprint1_Click() Dim myOption As Control Dim Printoption Dim Printthisrange As Range With ActiveSheet.PageSetup .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 End With For Each myOption In Frame1.Controls If myOption.Value = True Then Printoption = myOption.Caption End If Next myOption Select Case Selectprnrange Case 1 Printoption = "Cantera Natural Gas" Printthisrange = "OBACGG" Case 2 Printoption = "DEFS" Printthisrange = "OBADEFS" Case 3 Printoption = "Agave" Printthisrange = "OBAAgave" Case 4 Printoption = "TWML" Printthisrange = "OBATWML" Case 5 Printoption = "Print All OBA Pages" End Select Unload Me Range(Printthisrange).Select Selection.PrintPreview Range("a1").Select End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revised btnprint1_click()
Bruce,
Let's see if we have this right - Name = MyRange refers to Sheets1!A1:G5 (made up the range) than Range("MyRange").Select will select MyRange Since Printthisrange is dimmed as Range, you need Set Printthisrange = Range("MyRange") note: "Set" since this is an object than Printthisrange.Select will select MyRange Now you might want to incorporate Selection.PrintOut Copies:=1, Collate:=True to print out a selected range. ------------------------- Now your Select Case doesn't look quite right (but I am on shakey ground here) I think you want to change this Select Case Selectprnrange Case 1 Printoption = "Cantera Natural Gas" Printthisrange = "OBACGG" to this Select Case Printoption Case "Cantera Natural Gas" Printoption = "Cantera Natural Gas" Set Printthisrange = Range("OBACGG") and after everything is done Set Printthisrange = Nothing -------------------------------------------------- For Case 5, if you are trying to print the entire sheet and if you don't have a PrintArea defined ActiveWindow.SelectedSheets.PrintOut Copies:=1 will print everything hope some of this helps... steve "Bruce Roberson" wrote in message ... Steve: I made the change you suggested to put quotes around the line "Printthisrange = "OBACGG" including the sheet selection part as well, but it still stops with the same line "Range(Printthisrange).Select", and it didn't help if I put quotes around the printrange in the parenthesis. Also, I still don't know how I'm going to pass something out of case 5 to select all 4 print ranges to have them printed at the same time. So I need ideas on that also. -----Original Message----- Bruce, Since PrintThisRange is dimed as Range than Printthisrange = "OBACGG" should be Printthisrange = Range("OBACGG") you may also want to qualify the sheet Printthisrange = Sheets("Sheet1").Range("OBACGG") steve "Bruce Roberson" wrote in message ... yesterday, I posted a similar routine where I was trying to pass along a control option to a print range, so that I could print selected reports based on selecting a print range that was passed along from this procedure. I was seeking help with that, but I think I've figured out another route that I still need help with. I have five options on a frame with the default option being the print all ranges. Someone suggested before I did the following cases that the caption in this script had to be a valid range name, but I didn't want to name them that way. So, I devised this attempt at using cases to name the print range based on the caption I listed in these cases. Where it appears to hang now when I step through it is in the line: Range(Printthisrange).Select As far as I could tell, if "Printhisrange" were defined as a range, then shouldn't I be able to pass along a name to that variable based on my cases here. And then when the cases selected the value of "printthisrange", shouldn't I be able to pass that along to the line listed above? And then once I've solved that mystery, really what I need to do is provide for the "Print All OBA Pages" case in which I need to select all four previous ranges for the print. I'm still very much a newbie at Visual Basic, but I think I'm learning quickly. Private Sub btnprint1_Click() Dim myOption As Control Dim Printoption Dim Printthisrange As Range With ActiveSheet.PageSetup .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 End With For Each myOption In Frame1.Controls If myOption.Value = True Then Printoption = myOption.Caption End If Next myOption Select Case Selectprnrange Case 1 Printoption = "Cantera Natural Gas" Printthisrange = "OBACGG" Case 2 Printoption = "DEFS" Printthisrange = "OBADEFS" Case 3 Printoption = "Agave" Printthisrange = "OBAAgave" Case 4 Printoption = "TWML" Printthisrange = "OBATWML" Case 5 Printoption = "Print All OBA Pages" End Select Unload Me Range(Printthisrange).Select Selection.PrintPreview Range("a1").Select End Sub . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revised btnprint1_click()
Steve:
I'm sorry if I appear to be dense today, but I think I followed your last post. Maybe I missed part of your instruction. However, the thing still stalls out at the same point each time. I put in your change which was: SET Printthisrange = Range("OBACGG").select The stalling line again is: Range("Printthisrange").Select Here reposted below is the entire Sub as it appears at this point. With regards to your Case 5 suggestion, I can't Print all 4 ranges by printing the entire sheet because these 4 reports are all on separate sheets. So, is there a way to select multiple ranges with the Set statement, something like this?: Set Printthisrange = Range("OBACGG"), Range("OBADEFS"), Range("OBAAgave"), Range("OBATWML") And then, would the line: Selection.printpreview work with all them at the same time? __________________________________________________ _________ Private Sub btnprint1_Click() Dim myOption As Control Dim Printoption Dim Printthisrange As Range For Each myOption In Frame1.Controls If myOption.Value = True Then Printoption = myOption.Caption End If Next myOption Select Case Selectprnrange Case 1 Printoption = "Cantera Natural Gas" Set Printthisrange = Range("OBACGG") Case 2 Printoption = "DEFS" Set Printthisrange = Range("OBADEFS") Case 3 Printoption = "Agave" Set Printthisrange = Range("OBAAgave") Case 4 Printoption = "TWML" Set Printthisrange = Range("OBATWML") Case 5 Printoption = "Print All OBA Pages" End Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 End With Unload Me Range("Printthisrange").Select Selection.PrintPreview Set Printthisrange = Nothing Range("a1").Select End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revised btnprint1_click()
Bruce,
I hope this doesn't sound harsh, it isn't meant to be. Just straight forward. Hope the below gets the crinkles out. If not, post back... steve First not SET Printthisrange = Range("OBACGG").select but SET Printthisrange = Range("OBACGG") remember when using Set Set objectname = object in your case object is a range --------------------------------- Select Case Value Case Value1 whatever you want Case Value2 whatever you want not Select Case Selectprnrange Case 1 Printoption = "Cantera Natural Gas" Set Printthisrange = Range("OBACGG") Case 2 Printoption = "DEFS" Set Printthisrange = Range("OBADEFS") Case 3 Printoption = "Agave" Set Printthisrange = Range("OBAAgave") Case 4 Printoption = "TWML" Set Printthisrange = Range("OBATWML") Case 5 Printoption = "Print All OBA Pages" but Select Case Printoption Case "Cantera Natural Gas" Set Printthisrange = Range("OBACGG") Case "DEFS" Set Printthisrange = Range("OBADEFS") Case "Agave" Set Printthisrange = Range("OBAAgave") Case "TWML" Set Printthisrange = Range("OBATWML") Case "Print All OBA Pages" Set Printthisrange = ?????? ' (i don't know what you need here) "Bruce Roberson" wrote in message ... Steve: I'm sorry if I appear to be dense today, but I think I followed your last post. Maybe I missed part of your instruction. However, the thing still stalls out at the same point each time. I put in your change which was: SET Printthisrange = Range("OBACGG").select The stalling line again is: Range("Printthisrange").Select Here reposted below is the entire Sub as it appears at this point. With regards to your Case 5 suggestion, I can't Print all 4 ranges by printing the entire sheet because these 4 reports are all on separate sheets. So, is there a way to select multiple ranges with the Set statement, something like this?: Set Printthisrange = Range("OBACGG"), Range("OBADEFS"), Range("OBAAgave"), Range("OBATWML") And then, would the line: Selection.printpreview work with all them at the same time? __________________________________________________ _________ Private Sub btnprint1_Click() Dim myOption As Control Dim Printoption Dim Printthisrange As Range For Each myOption In Frame1.Controls If myOption.Value = True Then Printoption = myOption.Caption End If Next myOption Select Case Selectprnrange Case 1 Printoption = "Cantera Natural Gas" Set Printthisrange = Range("OBACGG") Case 2 Printoption = "DEFS" Set Printthisrange = Range("OBADEFS") Case 3 Printoption = "Agave" Set Printthisrange = Range("OBAAgave") Case 4 Printoption = "TWML" Set Printthisrange = Range("OBATWML") Case 5 Printoption = "Print All OBA Pages" End Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 End With Unload Me Range("Printthisrange").Select Selection.PrintPreview Set Printthisrange = Nothing Range("a1").Select End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revised btnprint1_click()
Bruce,
Almost forgot. Since Printthisrange is declared as a range you can use Printthisrange.Select steve "Bruce Roberson" wrote in message ... Steve: I'm sorry if I appear to be dense today, but I think I followed your last post. Maybe I missed part of your instruction. However, the thing still stalls out at the same point each time. I put in your change which was: SET Printthisrange = Range("OBACGG").select The stalling line again is: Range("Printthisrange").Select Here reposted below is the entire Sub as it appears at this point. With regards to your Case 5 suggestion, I can't Print all 4 ranges by printing the entire sheet because these 4 reports are all on separate sheets. So, is there a way to select multiple ranges with the Set statement, something like this?: Set Printthisrange = Range("OBACGG"), Range("OBADEFS"), Range("OBAAgave"), Range("OBATWML") And then, would the line: Selection.printpreview work with all them at the same time? __________________________________________________ _________ Private Sub btnprint1_Click() Dim myOption As Control Dim Printoption Dim Printthisrange As Range For Each myOption In Frame1.Controls If myOption.Value = True Then Printoption = myOption.Caption End If Next myOption Select Case Selectprnrange Case 1 Printoption = "Cantera Natural Gas" Set Printthisrange = Range("OBACGG") Case 2 Printoption = "DEFS" Set Printthisrange = Range("OBADEFS") Case 3 Printoption = "Agave" Set Printthisrange = Range("OBAAgave") Case 4 Printoption = "TWML" Set Printthisrange = Range("OBATWML") Case 5 Printoption = "Print All OBA Pages" End Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 End With Unload Me Range("Printthisrange").Select Selection.PrintPreview Set Printthisrange = Nothing Range("a1").Select End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revised btnprint1_click()
-----Original Message----- 1) First not SET Printthisrange = Range("OBACGG").select but SET Printthisrange = Range("OBACGG") I had already done this inside my case selections so I don't see any other places that you were referring to here that need correcting. __________________________________________________ _________ Not Select Case Selectprnrange but Select Case Printoption I did this correction. I didn't understand that the name of the case had to be the same as the object you are testing. I guess that is what you are saying here. Bottom line: I did the correction with Case name, and the other Set Printhistrange without the .select was already correct in the last post. But, where it hangs is still on the same line which now reads per your last post: printthisrange.select |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revised btnprint1_click()
Bruce,
OK! Now I am stumped. One last thing to try. Go into the code and step thru it using the F8 key. This will highlight each line in yellow before processing it. Place the mouse over each of your variable (after processing each line) and see what pops up. If the is a problem - the pop up will show = "" I would be most concerned with Printoption and Printthisrange. Also - do you have Option Explicity at the top of the module? This is a big help in finding problems with variables and code. steve "Bruce Roberson" wrote in message ... -----Original Message----- 1) First not SET Printthisrange = Range("OBACGG").select but SET Printthisrange = Range("OBACGG") I had already done this inside my case selections so I don't see any other places that you were referring to here that need correcting. __________________________________________________ _________ Not Select Case Selectprnrange but Select Case Printoption I did this correction. I didn't understand that the name of the case had to be the same as the object you are testing. I guess that is what you are saying here. Bottom line: I did the correction with Case name, and the other Set Printhistrange without the .select was already correct in the last post. But, where it hangs is still on the same line which now reads per your last post: printthisrange.select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revised q on report from tables | Excel Worksheet Functions | |||
3rd and last Revised WorkBook | Excel Discussion (Misc queries) | |||
REVISED Nexted IF and Countif | Excel Worksheet Functions | |||
Adding in Excel Revised | New Users to Excel | |||
n or U Revised ? | Excel Worksheet Functions |