Do Events Examples in VBA

22 October 2008 by Ross McLean

JP asked if I could expand on the Cancel post. I thought that I had already posted a DoEvent example, but I couldn’t find it on this blog, although when I dug out the org. file it did have a link to a blog post that didn’t work!!!! Maybe my blog is losing content!

Anyway here is an example file which shows what I mean:
MIE DoEvents Example 2008.xls

Tags: , ,
Categories: General • Permalink • 3 Comments »

Comments

  1. JP says:
    23 October 2008 at 9:29 pm

    I get it. Adding DoEvents to your loop allows the Click event of the other button to occur. Thanks.

    –JP

    Reply
  2. john says:
    13 March 2009 at 6:05 pm

    Great example – thanks

    Reply
  3. bERNARD says:
    3 April 2009 at 5:57 am

    Hello the file is great. I tried unsuccessfully to use a DoEvents command within a for next loop
    which (should) updates labels controls on a worksheet. The sheet is always printed with the first value I give to the label caption.
    [VBA]
    Sub Print_All_Cover()
    intTagAll = 1
    For intIndList = 0 To usrfrmStudentsReport.lstStaff.ListCount – 1
    Call PrintCover
    Next intIndList
    End Sub
    Sub PrintCover()
    Dim strAnswer As String
    Dim strMynote As String
    Dim intNoPrint%
    Call OpenCover
    Call TitleNameID
    intMidI = usrfrmStudentsReport.chkMidI ‘intMidI%, intMidII%, intFinI%, intFinII%
    intMidII = usrfrmStudentsReport.chkMidII
    intFinI = usrfrmStudentsReport.chkFinalI
    intFinII = usrfrmStudentsReport.chkFinalII
    intMidI = intMidI + intMidII + intFinI + intFinII ‘=-1: only Mid Term 1;= -2: Final I etc.
    Select Case intMidI
    Case -1
    strTerm = “First Term – Mid Term Examination”
    Case -2
    strTerm = “First Term – Final Examination”
    Case -3
    strTerm = “Second Term – Mid Term Examination”
    Case -4
    strTerm = “Second Term – Final Examination”
    End Select
    With Workbooks(“Cover.xlsx”).Sheets(“Cover”) ‘OLEObjects(“Caption_Label” & 10 – iCount).Object.Caption =
    .lblSchoolYear.Caption = “School Year ” & strSchoolYear
    .lblTerm.Caption = strTerm
    .lblName.Caption = strCoverName ‘ ALWAYS SHOWS THE FIRST NAME…
    .lblGrade.Caption = strGrade
    End With
    DoEvents
    With Workbooks(“Cover.xlsx”).Sheets(“Cover”)
    ‘To confirm that we print the right one…
    strMynote = “Printing” & ” ” & strCoverName
    ‘Display MessageBox
    strAnswer = MsgBox(strMynote, vbQuestion + vbYesNo, “CAU – Grade Reports”)
    If strAnswer = vbYes Then
    .PrintOut Copies:=1
    End If
    .Range(“J18:P21″).ClearContents
    .lblSchoolYear.Caption = “”
    .lblTerm.Caption = “”
    .lblGrade.Caption = “”
    .lblName.Caption = “”
    End With
    End Sub
    [/VBA]

    Reply

Submit a Comment