Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MsgBox (Excel 2000)

    Afternoon Everyone!

    Yet another ?.....Although I think I have this one almost solved.....(boy I wish all this were Access...LOL)

    PCDue is a spreadsheet. A column "Phone Call Due" ("H" and Date field) and a column "Phone Call Made" ("I" and Date Field) are in this spreadsheet. There will be a lot of data here....however, if "H's" DATE is TODAY (or past) and "I" is null, I would like a MsgBox to appear OnOpen of the spreadsheet.

    I have the following code (snippet I should say) but I'm not sure where to go at this point.

    Private Sub Workbook_Open()
    Dim wshSource As Worksheet
    Dim strRange As String

    strRange = Range("PCDue!A2:A30")
    ' If strRange="","",MsgBox("There are Phone Calls Due")
    Sheets("PCDUE").Visible = True
    Sheets("PCDUE").Select
    Sheets("Main").Visible = False
    End Sub

    Any guidance from the masters would be greatly appreciated.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MsgBox (Excel 2000)

    What do you want? A message box for each offendng cell pair? (Informative, but might be a tremendous nuisance) Or a warning for the first offending cell pair only? Try this code (in the Workbook _Open event procedure):

    <code> Dim oRange As Range</code>
    <code> Dim oCell As Range</code>
    <code> With Worksheets("PCDue")</code>
    <code> Set oRange = .Range(.Range("H2"), .Range("H65536").End(xlUp))</code>
    <code> End With</code>
    <code> For Each oCell In oRange</code>
    <code> If oCell < Date And oCell.Offset(0, 1) = "" Then</code>
    <code> MsgBox "Cell " & oCell.Offset(0, 1).Address & " should be filled.", vbInformation</code>
    <code> Exit For</code>
    <code> End If</code>
    <code> Next oCell</code>
    <code> Set oCell = Nothing</code>
    <code> Set oRange = Nothing</code>

    This version will stop after one message. If you want a message for each offender, comment out the Exit For line by inserting an apostrophe ' before it.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MsgBox (Excel 2000)

    Hans,

    I put your code in the OnOpen event and nothing happens. In H2 I've put both 6/1/04 and 6/16/04. I've looked at your code and I can tell where oRange is SET but not oCell, would that make a difference?

    Thanks for the help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MsgBox (Excel 2000)

    I tried Hans' code and it worked perfectly for me. I can think of a couple of things that might be causing your problem.

    1- You did not get the code properly in the Workbook_Open event routine in the module behind the ThisWorkbook object. To put the code in the Workbook_Open event routine, go to the VB Editor. In the Project Explorer window, right click on the ThisWorkbook object and then click on View Code. In the Code window, select Workbook in the left drop down list and then select open in the right drop down list. This should give you a dummy routine where Hans' code can be placed.

    2- Column I is not really empty cells.
    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: MsgBox (Excel 2000)

    I tested it and it works fine for me.: dates in H2 and lower in H and nothing in I

    Todays date (6/16/04) won't trigger it since it is not less than "Date" = Today.

    oCell is "set" via the statement:
    <pre>For each oCell in </pre>


    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •