 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008Before Print Problem
Hi, I have the following BeforePrint routine in my ThisWorkbook module which places a custom header on pages based on the value of cells in the sheet. It worked except now if the value of the cell changes, it is not picking up the change and stays with the old value in the header. This there something I am missing? Private Sub Workbook_BeforePrint(Cancel As Boolean) 'add custom header to pages before printing With SumbyLineItempg.PageSetup .CenterHeader = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _ "&4" & vbCrLf & "&12&A" & vbCrLf & " " End With With LineItemspg.PageSetup .CenterHeader = "&C&14&B" & LineItemspg.Range("C2").Value & _ "&4" & vbCrLf & "&12&A" & vbCrLf & " " End With With MainPagepg.PageSetup .CenterHeader = "&C&14&B" & MainPagepg.Range("B1").Value & _ "&4" & vbCrLf & "&12&A" & vbCrLf & " " End With End Sub  SignatureThanks for your help. Karen53 Are you sure you have the correct addresses in your code? (You may want to give them nice range names and use those instead--it may be safer if rows/columns can be inserted/deleted.) Are you sure you still have .enableevents set to true before you print/print preview? It could be other code that turns that off--and doesn't turn it back on. > Hi, > [quoted text clipped - 27 lines] > Thanks for your help. > Karen53 SignatureDave Peterson Hi Dave, Thanks for your help. Yes, enable events is on and it is the correct address. I have found that if I select 'Print Preview' it corrects the problem and the page prints correctly. If I change the cell value and do not 'Print Preview' first, the header does not update with the new value. What is this? Do you know? SignatureThanks for your help. Karen53 > Are you sure you have the correct addresses in your code? > [quoted text clipped - 37 lines] > > Thanks for your help. > > Karen53 Without any testing at all... How about adding this right before the End Sub DoEvents Maybe it'll allow some time for excel to catch up before the actually printing. But I really don't have a good reason why print preview works and print doesn't. > Hi Dave, > [quoted text clipped - 53 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave, I added DoEvents right before the End Sub of my BeforePrint routine but it didn't make any difference. This is really frustrating. SignatureThanks for your help. Karen53 > Without any testing at all... > [quoted text clipped - 63 lines] > > > > > > Dave Peterson Hi Dave, I have added these two statements right before my print command and I am still having the same problem.. Application.EnableEvents = True Application.ScreenUpdating = True I've named the range as you suggested and am using that instead. So, it is the correct location. Do you have any other suggestions? SignatureThanks for your help. Karen53 > Are you sure you have the correct addresses in your code? > [quoted text clipped - 37 lines] > > Thanks for your help. > > Karen53 The named ranges won't fix the problem. But it may avoid future problems. > Hi Dave, > [quoted text clipped - 57 lines] > > > > Dave Peterson SignatureDave Peterson Try assigning it to a variable so the string can be fully resolved before it goes into the header, e.g. Dim HdrVal As String HdrVal = "&C&14&B" & SumbyLineItempg.Range("E2").Value & _ "&4" & vbCrLf & "&12&A" & vbCrLf & " " Debug.Print HdrVal .CenterHeader = HdrVal Then you can inspect it too to help narrow down the problem. SignatureTim Zych SF, CA > Hi, > [quoted text clipped - 27 lines] > > End Sub Hi, Thank you for the suggestion, Tim. It was a great idea. My immediate window shows the code executing and returns the correct Header, but the printed copy still shows the old header. Here are the results from my immediate window... Starting BeforePrint, HdrVal = &C&14&BDayna's Mall&4 &12&A &C&14&BDayna's Mall&4 &12&A SignatureThanks for your help. Karen53 > Try assigning it to a variable so the string can be fully resolved before it > goes into the header, e.g. [quoted text clipped - 38 lines] > > > > End Sub This is so frustrating! What is going on? The immediate window shows the code executing with the correct value for the header variable, yet the header on the worksheet does not change. If I do a print preview, the worksheet header changes. I've run out of things to try. SignatureThanks for your help. Karen53 > Try assigning it to a variable so the string can be fully resolved before it > goes into the header, e.g. [quoted text clipped - 38 lines] > > > > End Sub Hmm, it works for me. If you try this in a new workbook, does the same behavior occur? Does this occur for a different printer? I'm wondering if there's a page setup configuration / zoom factor / page breaks / preview mode / which sheet is active / how is printing being invoked or some other page-setup issue that is causing the code to be buggy. SignatureTim Zych SF, CA > This is so frustrating! What is going on? > [quoted text clipped - 54 lines] >> > >> > End Sub Just to muddy the waters... What version of excel are you using? What type of view are you using: Normal, page break preview, print layout? If you create a new workbook with just enough data for testing and plop the code in there, does that look ok? I don't have a guess why it's not working for you. > This is so frustrating! What is going on? > [quoted text clipped - 58 lines] > > > Thanks for your help. > > > Karen53 SignatureDave Peterson Hi, So far I have tried this at home and at work with the same result. I have created a new workbook and the code works. I thought perhaps it was because the workbook was protected. So, I tried it and it still worked. There has to be something different with this workbook. I am going through what I can think of one at a time to see if it makes a difference. It executes the code but doesn't make the change. So far I haven't fouind the difference. I'll continue tying difference things tomorrow. There are only so many things I know of to try, though. I am using Excel 2003 SP2 at home. At work I believe it is version 2003 as well. I'll check tomorrow. This is so strange. I don't have a lot of experience with this. Your help is very appreciated. --- Thanks for your help. Karen53 > Just to muddy the waters... > [quoted text clipped - 67 lines] > > > > Thanks for your help. > > > > Karen53 I still can't duplicate it. I don't have another guess. > Hi, > [quoted text clipped - 92 lines] > > > > Dave Peterson SignatureDave Peterson Hi, Ok, it works as long as I use the print button on the toolbar. As soon as I use code to print the page it stops working. I have tried the printing code two ways. With Sheets(ShName) .Range(Startcol & StartRow & ":" & EndCol & RptEndRow).PrintOut _ Copies:=1, Collate:=True End With With Sheets(ShName) .Range(Startcol & StartRow & ":" & EndCol & RptEndRow).Select .PageSetup.PrintArea = Startcol & StartRow & ":" & EndCol & RptEndRow Selection.PrintOut Copies:=1, Collate:=True End With SignatureThanks for your help. Karen53 > I still can't duplicate it. I don't have another guess. > [quoted text clipped - 94 lines] > > > > > > Dave Peterson I wouldn't use the .select and Selection lines. To select a range, the sheet that owns that range has to be active--and that may not be true. But I'm not sure what you mean by stops working. Does this mean that the code doesn't even run--or it runs, but the headers don't change (or printareas don't change). And if you're running this stuff using code, is there any chance that you've disabled events before doing the printout. If you did, then the workbook_beforeprint won't even fire. > Hi, > [quoted text clipped - 120 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave, Yes, what I mean is the code executes but the header does not change. The immediate window shows the new header but the page's header does not change. I tried adding an Application.EnableEvents = True before it but it still did not change. SignatureThanks for your help. Karen53 > I wouldn't use the .select and Selection lines. To select a range, the sheet > that owns that range has to be active--and that may not be true. [quoted text clipped - 131 lines] > > > > > > Dave Peterson Hi, Also it's the same for both workbooks. It was working because I was using the print button on the toolbar. SignatureThanks for your help. Karen53 > I still can't duplicate it. I don't have another guess. > [quoted text clipped - 94 lines] > > > > > > Dave Peterson Does it work when you use: File|print or File|Print preview > Hi, > [quoted text clipped - 106 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave, Yes, it changes if I use either File/Print or Print Preview/Print. It works everywhere but for my code. SignatureThanks for your help. Karen53 > Does it work when you use: > File|print [quoted text clipped - 111 lines] > > > > > > Dave Peterson Hi Dave, It will not work if it is in ThisWorkbook's 'BeforePrint' routine. However, if I move the code into the individual print routines in the general modules, it works just fine so far. SignatureThanks for your help. Karen53 > Does it work when you use: > File|print [quoted text clipped - 111 lines] > > > > > > Dave Peterson I still don't have a guess. Sorry. > Hi Dave, > [quoted text clipped - 124 lines] > > > > Dave Peterson SignatureDave Peterson HI Dave, Since it's working in the general modules, I just dumped the 'BeforePrint'. Why fight city hall. SignatureThanks for your help. Karen53 > I still don't have a guess. > [quoted text clipped - 128 lines] > > > > > > Dave Peterson Well, you lasted longer than I would have <vbg>. > HI Dave, > [quoted text clipped - 140 lines] > > > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.