Posts by slickity

Re: Execute code after printing with the BeforePrint event

hey, I just added a few lines to your code, works perfectly for what I'm trying to do. Thanks.

Re: Best fit print area

The code for printing a single sheet versus the whole book is as follows (sure you knew this already).

Sub printSingle()


    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
Sub printAll()


    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub

So in light of there being two different commands, are there then two different versions of Before_Print for them?

Re: Best fit print area

Yeah, that's the same conclusion I've come to, and that's what I've done, with the print button being on the Project Review page. Now, the only other thing I'd like to do is if the entire workbook is being printed, that macro should run.

Re: Best fit print area

Nope. Something I'm trying to figure out is, can you call a print command from within the BeforePrint method, or will this always cause an infinite loop (since that new print command will itself call the BeforePrint (BP), etc.)?

The reason I ask is, I want some formatting changes to occur to the sheet "Projec Review" before printing, then be reversed after. There was another discussion about post-printing code here, but after thinking about the code I suggested, it seemed like it would make an infinite loop. The only way I can see of getting around this is if there's some more basic print command you can use in Excel that will ignore the BP method, so that you could call that from within BP.

Re: Execute code after printing with the BeforePrint event

I think I just realized why my idea won't work. If you do the above, I think you might get an infinite loop because your second print command will call the BeforePrint function again, and so forth. Sorry.

Re: Best fit print area

OK, I just spotted a problem with what I've done:

I only want this macro FitPages to run when the sheet "Project Review" is being printed, which will only occur if a normal print command is run when that sheet is active, or if the entire workbook is being printed. How would I work this in?

Re: Best fit print area

OK, thanks for the tip. I'll work that in. Also, ignore my last question. I was looking for something that looked like a seperate project. I found ThisWorkbook.
Thanks again.

Re: Best fit print area

I don't get a ThisWorkbook in VBA. I know what you're talking about because I've used the ThisOutlookSession project in Outlook VBA before, but in my project explorer in Excel VBA, I just see the current VBA project, which is just named after the file I'm in, and another one from the Auto-Save Add-In.

How should I access ThisWorkbook?

Re: Best fit print area

Can anyone suggest why the BeforePrint method is not working for me? I've tried a simple Hello World msgBox, and even that does not work.

Re: Execute code after printing with the BeforePrint event

Hey, I'm no expert here, so one of the pros here please correct me if I'm wrong, but could you not do something like the following:

Private Sub Workbook_BeforePrint(Cancel As Boolean)


ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


Call mySub


Cancel = True


End Sub

It seems to me that this sub will execute on a print command, then execute its own print command with the second line, then you insert more code where I have the line "Call mySub," or just call another sub of your own. After that sub ends, the Cancel boolean from the original Workbook_BeforePrint event is set to false by the second last line so that you won't get two copies. Then things should just end pleasently. I haven't tested this yet, but I will when I get a chance, it's just that the Workbook_BeforePrint event actually doesn't work for me right now, a problem which I'm trying to work out right now here.

Somebody please correct me if I'm wrong about the above idea.

Re: Best fit print area

OK, I've sorted out my latest issue (from my last post). I now have the following code. I wanted a confirm box before formatting the area. However, when I print, it just prints without popping up the message box. I don't know if this is a problem with my message box usage, or my BeforePrint usage. Can anyone tell me?
Incidentally, the FitPages sub has been tested and is working fine.
Thanks.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim response As String
    response = MsgBox("Format Costing Review before printing?", vbYesNoCancel, "Printing check")
    If response = vbYes Then
        Cancel = False
        Call FitPages
    ElseIf response = vbNo Then
        Cancel = False
        Exit Sub
    ElseIf response = vbCancel Then
        Cancel = True
    End If
End Sub




Sub FitPages()


Worksheets("Project Review").Activate


    Dim nRows As Integer, s As Double, i As Integer
    nRows = ActiveSheet.Cells(1, 256).End(xlToLeft).Column
    nRows = ActiveSheet.Cells(1, 256).End(xlDown).Column
    s = 0
    
    For i = 1 To nRows
        s = s + Rows(i).Width
    Next i
    s = s / 72    'total inches long
    i = Application.Round(s / 10, 0)    'number of pages long To Do
    
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$117"
    
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = i
    End With
End Sub

Re: Best fit print area

After running the above code, I now can't move my righ-hand vertical page break beyond the right side of column H, but I need it at the right side of column I. Can anyone help?
Thanks.

OK, after inspecting the code from the other thread mentioned above, I've sort of fit it to my needs as follows.

Sub FitPages()


Worksheets("Project Review").Activate


    Dim nRows As Integer, s As Double, i As Integer
    nRows = ActiveSheet.Cells(1, 256).End(xlToLeft).Column
    nRows = ActiveSheet.Cells(1, 256).End(xlDown).Column
    s = 0
    
    For i = 1 To nRows
        s = s + Rows(i).Width
    Next i
    s = s / 72    'total inches long
    i = Application.Round(s / 7, 0)    'number of pages long To Do
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesTall = i
        .FitToPagesWide = False
    End With
End Sub

This code now seems to make a "best-fit" for length. If I want the width fixed, say, coumns A through I, how would I modify this code?

I have a sheet with several rows which, during the normal use of the sheet, may get expanded height-wise, basically meaning the sheet may get longer when a use fill it out. I'm trying to figure out how to set up a macro to make the sheet print in a best-fit sort of manner, and that would run at print-time.

To explain a little further, the sheet right now has a bunch of page breaks which I guess were automatic Excel breaks. So, if a user fills in very little information, then some of the pages will have just a few lines, and the rest blank.

There was a discussion on this here , but I'm curious if there's a way to make a macro just insert a page break every 'x' number of pixels, rounding to the nearest full row, until it reaches the end of the sheet.

Thanks.

Re: Check for presence of a value in an array

OK, after inspecting the MatchFound property, it doesn't seem I can use it for this.

The code below is what I have so far. It gives me a Type Mistmatch error at the array initialization line, on the 3rd line of code. Any thoughts?

Sub isEmailNeeded(needsEmail)


    Dim adressExceptions As String
    adressExceptions = Array("Project Coordinator South")
    
    needsEmail = True
    If (ActiveCell.Value = "" And ActiveCell.Offset(0, 3).Value = "") Then
    needsEmail = False
    Else: If (Application.WorksheetFunction.Match(ActiveCell.Offset(0, 3).Value, adressExceptions, 0) = 0) Then needsEmail = False
    End If
    
End Sub


Sub testIsEmailNeeded()
Dim testMe As Boolean
testMe = True


Range("A18").Activate


Call isEmailNeeded(testMe)


MsgBox needsEmail


End Sub

Re: Check for presence of a value in an array

OK, and can an array be declared as simply a named Range? Like, if I have values in the range B5 to B10, and I name that "myRange," and I want to use that as the array?

Also, since I'm really just looking for a boolean out of this, not an actual location value, would I be able to use the MatchFound property instead for my purposes?

Is there some built-in function that will quickly check to see if a particular value is in an array?

For example, say I have an Array("1", "2", "3"), is there some boolean-returning function that would give me a false if I passed in that array and the number 4?

If not, is there some easy way to implement this. Obviously you can use a loop to iterate through the array, checking each value, but that seems a little long.

Thanks.

Re: Send mail without attaching workbook

I'm not sure what you mean by delete it. The whole point of this exercise is to send an email, so I tried SendEmail, as you suggested, and that did not work either.

With either Send or SendEmail, I get a run-time error, although as I've pointed out, the email somehow still sends.
I think this might be a security issue related to how my companie's server is setup. I'm gonna contact IT and see what they say.

Re: Send mail without attaching workbook

OK, I got your code working now, but the code I had much earlier was doing this, without having to include the OE library.

My question now is that I want to do this without actually going through Outlook if I could, or at least avoiding that confirmation Yes/No box that pops up when you send through Oulook. Your code still gives me this.

Could you please review the code in the my second last post (the last one where I actually included code)?

The thing about that code is, it crashes on the .Send line, but it actually does send an email without actually opening Outlook. So it's sort of partially working.

Re: Send mail without attaching workbook

.SendMail gives the error "Object doesn't support this property or method."

How do you reference the Outlook Object Library? I'm pretty much completely new to Oulook VBA, so you kinda of have to walk me through this one.