Wait for page setup and controls to run before printing? 2

[bluegnu]

I have the following code which loops through a list of worksheets, goes to those which have been selected, checks a checkbox which subsequently hides a few columns and rows and then prints out that sheet:

Private Sub CommandButton1_Click() 'PRINT
    Dim i As Long
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) = True Then
                If Me.HeaderInc = 0 Then
                    Sheets(Me.ListBox1.List(i)).CheckBox1.Value = True
                    Else
                    Sheets(Me.ListBox1.List(i)).CheckBox1.Value = False
                End If
            Sheets(Me.ListBox1.List(i)).Rows("1:1").EntireRow.Hidden = True
            Sheets(Me.ListBox1.List(i)).CheckBox6.Value = True
            With Sheets(Me.ListBox1.List(i)).PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
            .Orientation = xlPortrait
            .LeftFooter = "Printed on &D" ' Date
            .CenterFooter = "Section: &A"
            .RightFooter = "page &P of &N" ' page n of m
            .PrintTitleRows = ActiveSheet.Rows(14).Address
            End With
            Sheets(Me.ListBox1.List(i)).PrintOut
            Sheets(Me.ListBox1.List(i)).Rows("1:1").EntireRow.Hidden = False
            Sheets(Me.ListBox1.List(i)).CheckBox6.Value = False
        End If
    Next i
    Me.Hide
End Sub

Problem is it prints before it has a chance to adjust the columns.

I tried adding in a wait:

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

But that pauses for 3 seconds as soon as a press the command button, no matter where in the code I put it.

Is there something I can do to get this working?

thanks for your help

 

[GlennUK]

A checkbox doesn't "do" anything unless it has a routine assigned to it. Run that routine instead of checking the checkbox.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[bluegnu]

Thanks Glenn,

Within the each worksheet I have the same checkboxes. They use a bit of code in a module and they work fine. So for instance for checkbox6 the code I have is:

Private Sub CheckBox6_Click()
SIX
End Sub

Where SIX is the name of a public sub in my module.

It works fine when I mouse clcik the checkbox, how would I get it to work from within the code in my original post?

thanks again.

 

[bluegnu]

Hello again, still struggling with this. Any help would be appreciated.

thanks

 

[TonyJollans]

Unfortunately there is nothing built in which will really help you with this. You have no control over when event code is run and there is no guarantee of a consistent sequence. As the whole thing runs as a single thread, with Excel deciding which bits of code get run in what sequence within that thread, putting in a wait makes no difference as the whole thread waits.

You *might* have some joy with judicious use of DoEvents but what Glenn says is correct - if you explicitly call the code it will run as you direct rather than as Excel chooses - although if you actually want the checkboxes checked it's a bit more complicated. What is the reason you can't just code "SIX" in your originally posted code, just as you do in the Event code?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[bluegnu]

But copying the SIX code to the original code, it works fine! Simple things but for some reason I probably wouldn't have thought of doing that.

thanks

 

[GlennUK]

You don't need to copy the SIX code, just call it!

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.