Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cases/Set object to take coordinates of Print Range
I'm staring over with this post, and see if I can explain
it another way. This procedure is taking a user choice from an option button and attempting to assign a predefined print range from the choice a user selects from one of five option buttons. I do know from stepping through this procedure that the object printoption is taking on a value from the line "printoption = myoption.caption" as shown below. However, when it gets down to the cases, and attempts to set the object "Printthisrange", it apparently isn't happening like it should. Therefore, when the procedure gets down to the line "Printthisrange.select", it acts as if Printthisrange was never populated, which it wasn't according to the debug watch I put on this range. I have played with it and Steve Bell has tried to help, but it still doesn't work, so I just thought it might help if I posted all I know about what is happening and what is not happening. Hope this help so someone can diagnose the problem I am having. __________________________________________________ _________ 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 Printoption 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 Printthisrange.Select Unload Me Selection.PrintPreview Set Printthisrange = Nothing Range("a1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cases/Set object to take coordinates of Print Range
Bruce,
Guess I wasn't clear - Instead of Case 1, Case 2 .... You want Case "Cantera Natural Gas", Case "DEFS", etc. And you can get rid of each of the Printoption = .... lines I am not sure what to put in for all pages. Also OBACGG, OBADEFS, OBAAgave, OBATWML MUST be defined names on the worksheet! This works for me... steve --------------------------------------------------- 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 = Range(????????) End Select "Bruce Roberson" wrote in message ... I'm staring over with this post, and see if I can explain it another way. This procedure is taking a user choice from an option button and attempting to assign a predefined print range from the choice a user selects from one of five option buttons. I do know from stepping through this procedure that the object printoption is taking on a value from the line "printoption = myoption.caption" as shown below. However, when it gets down to the cases, and attempts to set the object "Printthisrange", it apparently isn't happening like it should. Therefore, when the procedure gets down to the line "Printthisrange.select", it acts as if Printthisrange was never populated, which it wasn't according to the debug watch I put on this range. I have played with it and Steve Bell has tried to help, but it still doesn't work, so I just thought it might help if I posted all I know about what is happening and what is not happening. Hope this help so someone can diagnose the problem I am having. __________________________________________________ _________ 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 Printoption 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 Printthisrange.Select Unload Me Selection.PrintPreview Set Printthisrange = Nothing Range("a1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cases/Set object to take coordinates of Print Range
Well, you would of thought that would be the end of this
problem. However...... Printthisrange still refused to work with this approach so..... I got some more help and I have gotten a little farther with the following procedure. Private Sub btnprint1_Click() Dim Printthisrange Select Case True Case OptCNGOBA.Value Set Printthisrange = Range("OBACGG") Case OptDEFSOBA.Value Set Printthisrange = Range("OBADEFS") Case OptAgaveOBA.Value Set Printthisrange = Range("OBAAgave") Case OptTWMLOBA.Value Set Printthisrange = Range("OBATWML") This way it tests for a true value on the option button, and then it actually set the printrange like it was supposed to. The rest of the procedure is what I'm experimenting with right now. I can get it to print preview the range, but I am having trouble with the fact that each range is on a separate sheet, and I need to select either the range or the worksheet so I can do a with statement for the formatting. For some reason, the statement "Printthisrange.select" sometimes refuses to select as I select it when testing it with each option. It just gives me this run time error '1004': and then the next line of the box says "Select method of Range class failed" Maybe I have to hold my tongue a certain way perhaps for this to work on each time? This is the full procedure as I have it now: Private Sub btnprint1_Click() Dim Printthisrange Select Case True Case OptCNGOBA.Value Set Printthisrange = Range("OBACGG") Case OptDEFSOBA.Value Set Printthisrange = Range("OBADEFS") Case OptAgaveOBA.Value Set Printthisrange = Range("OBAAgave") Case OptTWMLOBA.Value Set Printthisrange = Range("OBATWML") End Select Printthisrange.Select 'With Worksheets(Printthisrange.Parent.bane).PageSetup ' .PrintTitleRows = "" ' .PaperSize = xlPaperLegal ' .FitToPagesWide = 1 ' .FitToPagesTall = 1 ' .Orientation = xlLandscape 'End With Unload Me Printthisrange.PrintPreview Printthisrange = "Nothing" Range("a1").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cases/Set object to take coordinates of Print Range
I'd love to do somethign with a Sheets command, but it
always gives me an idiot message, in this case Run time message 13 Private Sub btnprint1_Click() Dim Printthisrange Dim mysheet As Worksheet Select Case True Case OptCNGOBA.Value Set Printthisrange = Range("OBACGG") Case OptDEFSOBA.Value Set Printthisrange = Range("OBADEFS") Case OptAgaveOBA.Value Set Printthisrange = Range("OBAAgave") Case OptTWMLOBA.Value Set Printthisrange = Range("OBATWML") End Select __________________________________________________ _______ Well how do I get it to recognize "Mysheet" as a reference? Sheets("mysheet").Printthisrange.Select With Sheets("MySheet").Printthisrange .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape End With Unload Me Printthisrange.Select Printthisrange.PrintPreview Printthisrange = "Nothing" Range("a1").Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cases/Set object to take coordinates of Print Range
Bruce,
You need to substitute the actual name of the sheet in place of MySheet. (MySheet is just a generic). Excel is looking for the name you have assigned to the sheet. You may see things like MyRange, MySheet, MyBook in posted code. You must always replace these with the names you are using. You may also see references to $A1:$G$5 in the posted code. Again you must replace this with the range you are using. Remember we don't always know what your workbook looks like (or for that matter, what other peoples workbooks look like). So we construct code with generic references. Unfortunately many of the new comers aren't familiar with this and get caught. Hope this helps... steve "Bruce Roberson" wrote in message ... I'd love to do somethign with a Sheets command, but it always gives me an idiot message, in this case Run time message 13 Private Sub btnprint1_Click() Dim Printthisrange Dim mysheet As Worksheet Select Case True Case OptCNGOBA.Value Set Printthisrange = Range("OBACGG") Case OptDEFSOBA.Value Set Printthisrange = Range("OBADEFS") Case OptAgaveOBA.Value Set Printthisrange = Range("OBAAgave") Case OptTWMLOBA.Value Set Printthisrange = Range("OBATWML") End Select __________________________________________________ _______ Well how do I get it to recognize "Mysheet" as a reference? Sheets("mysheet").Printthisrange.Select With Sheets("MySheet").Printthisrange .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape End With Unload Me Printthisrange.Select Printthisrange.PrintPreview Printthisrange = "Nothing" Range("a1").Select End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cases/Set object to take coordinates of Print Range
Steve:
Then shouldn't I be able to assign a sheet name to mysheet having already declared that as a worksheet object? For the last case option, the sheet name in question would be OBA TWML (Space intentional betw A and T). So, right after that statement, I have tried various syntax with no luck so far. Case OptTWMLOBA.Value Set Printthisrange = Range("OBATWML") Set mysheet = Worksheet("OBA TWML") If it takes that, then I should be able to select the sheet and/or the range itself, right? Then, if I get all accomplished, then I can do what I need to with the print settings. This shouldn't have been this hard. I have a lot of other stuff to do they may make this look like a walk in the park. So, it is real frustrating that the learning curve is so steep going from QPW to Excel. There wasn't a whole lot in QPW that I couldn't do. Bruce Case -----Original Message----- Bruce, You need to substitute the actual name of the sheet in place of MySheet. (MySheet is just a generic). Excel is looking for the name you have assigned to the sheet. You may see things like MyRange, MySheet, MyBook in posted code. You must always replace these with the names you are using. You may also see references to $A1:$G$5 in the posted code. Again you must replace this with the range you are using. Remember we don't always know what your workbook looks like (or for that matter, what other peoples workbooks look like). So we construct code with generic references. Unfortunately many of the new comers aren't familiar with this and get caught. Hope this helps... steve "Bruce Roberson" wrote in message ... I'd love to do somethign with a Sheets command, but it always gives me an idiot message, in this case Run time message 13 Private Sub btnprint1_Click() Dim Printthisrange Dim mysheet As Worksheet Select Case True Case OptCNGOBA.Value Set Printthisrange = Range("OBACGG") Case OptDEFSOBA.Value Set Printthisrange = Range("OBADEFS") Case OptAgaveOBA.Value Set Printthisrange = Range("OBAAgave") Case OptTWMLOBA.Value Set Printthisrange = Range("OBATWML") End Select __________________________________________________ _______ Well how do I get it to recognize "Mysheet" as a reference? Sheets("mysheet").Printthisrange.Select With Sheets("MySheet").Printthisrange .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape End With Unload Me Printthisrange.Select Printthisrange.PrintPreview Printthisrange = "Nothing" Range("a1").Select End Sub . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cases/Set object to take coordinates of Print Range
Bruce,
Excel can be very tempermental. Variables have to be defined carefully. For your needs: 1. Your workbook must be the active workbook. 2. The worksheet must be selected. 3. Than you can select the range. ----------------------------------------------------- Dim mysheet As Worksheet, Printthisrange as Range ' Set mysheet = Worksheets("OBA TWML") Set Printthisrange = mysheet.Range("G5:M25") ' (reset the range per your needs) mysheet .Select Printthisrange.select Set Printthisrange = Nothing Set mysheet = Nothing ----------------------------------------- If this still doesn't work. Double check that all your variables are picking up the correct values. Make sure that Option Explicit is at the top of the module. And Debug Compile your project to see if Excel can pick up any problems. Put the code above in a module all by itself and see if it runs. This shouldn't have been this hard. I have a lot of other stuff to do they may make this look like a walk in the park. So, it is real frustrating that the learning curve is so steep going from QPW to Excel. There wasn't a whole lot in QPW that I couldn't do. Part of the problem was the problem you were having with the syntax. Had to repeat myself a few times (myfault) about setting the Case statements. And than you didn't realize that you needed to replace some of my "names" with your "names". This stuff happens... And sometimes typo's and such really get in the way. This is why debugging and stepping through code is so important. I don't know what QPW is but I don't think you can really compare the two. At the beginning Excel VB can be a bit confusing. But once you get on with it - it does get easier. It is a steep curve because there is a lot to learn! Spend as much time as you can reading as many of the posts in the ng and you will be surprised. steve "Bruce Roberson" wrote in message ... Steve: Then shouldn't I be able to assign a sheet name to mysheet having already declared that as a worksheet object? For the last case option, the sheet name in question would be OBA TWML (Space intentional betw A and T). So, right after that statement, I have tried various syntax with no luck so far. Case OptTWMLOBA.Value Set Printthisrange = Range("OBATWML") Set mysheet = Worksheet("OBA TWML") If it takes that, then I should be able to select the sheet and/or the range itself, right? Then, if I get all accomplished, then I can do what I need to with the print settings. This shouldn't have been this hard. I have a lot of other stuff to do they may make this look like a walk in the park. So, it is real frustrating that the learning curve is so steep going from QPW to Excel. There wasn't a whole lot in QPW that I couldn't do. Bruce Case -----Original Message----- Bruce, You need to substitute the actual name of the sheet in place of MySheet. (MySheet is just a generic). Excel is looking for the name you have assigned to the sheet. You may see things like MyRange, MySheet, MyBook in posted code. You must always replace these with the names you are using. You may also see references to $A1:$G$5 in the posted code. Again you must replace this with the range you are using. Remember we don't always know what your workbook looks like (or for that matter, what other peoples workbooks look like). So we construct code with generic references. Unfortunately many of the new comers aren't familiar with this and get caught. Hope this helps... steve "Bruce Roberson" wrote in message ... I'd love to do somethign with a Sheets command, but it always gives me an idiot message, in this case Run time message 13 Private Sub btnprint1_Click() Dim Printthisrange Dim mysheet As Worksheet Select Case True Case OptCNGOBA.Value Set Printthisrange = Range("OBACGG") Case OptDEFSOBA.Value Set Printthisrange = Range("OBADEFS") Case OptAgaveOBA.Value Set Printthisrange = Range("OBAAgave") Case OptTWMLOBA.Value Set Printthisrange = Range("OBATWML") End Select __________________________________________________ _______ Well how do I get it to recognize "Mysheet" as a reference? Sheets("mysheet").Printthisrange.Select With Sheets("MySheet").Printthisrange .PrintTitleRows = "" .PaperSize = xlPaperLegal .FitToPagesWide = 1 .FitToPagesTall = 1 .Orientation = xlLandscape End With Unload Me Printthisrange.Select Printthisrange.PrintPreview Printthisrange = "Nothing" Range("a1").Select End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for Cases=144 * # of Cases + Pieces | Excel Discussion (Misc queries) | |||
Used to be able to attach range of labels to xy coordinates - no m | Charts and Charting in Excel | |||
Print Word Object in Excel | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Print Macro : Object not set | Excel Programming |