Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Msgbox based on cell value (Office 2003 or 97)

    Hi

    I have no problem with standard MsgBox commands, however I would like to create one that runs when the worksheet opens and the cell H5 contains a number greater than 10.

    Any help would be appreciated.

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Msgbox based on cell value (Office 2003 or 97)

    Activate the Visual Basic Editor (Alt+F11)
    Double click ThisWorkbook in the project explorer.
    Select Workbook from the dropdown list in the upper left corner of the module window.
    This will create a Workbook_Open event procedure.
    Make it look like this:

    Private Sub Workbook_Open()
    If Worksheets("Sheet1").Range("H5") > 10 Then
    MsgBox "Watch it, mate!", vbInformation
    End If
    End Sub

    where Sheet1 is the name of the worksheet.
    Switch back to Excel and save the workbook.
    Next time you open it, the event handler will run automatically.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Msgbox based on cell value (Office 2003 or 97)

    Hi Hans

    Once again I am grateful for your reply and once again I have stupidly not given you enough information,

    This won't just apply to sheet1 I have 77 sheets and I would like it to run on each sheet when opend, if applicable. I do hope I am not wasting your valuable time.
    But sometimes until you see the reply you don't realise you are not giving the full requirement information.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Msgbox based on cell value (Office 2003 or 97)

    What do you mean by "to run on each sheet when opened"? Would you like it to run whenever a worksheet is activated? That is possible, but it seems to me the user would soon become irritated - each time (s)he switches to another worksheet, the message box might be displayed (if H5 > 10 on that worksheet)

    Perhaps you could tell us why you want to do this; there might be a less obtrusive way of accomplishing your goal.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Msgbox based on cell value (Office 2003 or 97)

    Hi Hans

    It would only be an initial irritant because on seeing the box the are meant to adjust the figures to bring H5 below 10.

    Without going in to too much detail its a matter of adjusting percentages to get the figure below 10.

    Thanks again for your reply.

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Msgbox based on cell value (Office 2003 or 97)

    But still, do you really want to pop up a message box each time the user switches to a worksheet that has H5 > 10? It might be better to display a general warning once, when the workbook is opened. Or, if this is a one-time operation, just instruct the user to review all worksheets.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Msgbox based on cell value (Office 2003 or 97)

    Hi Hans

    You may be correct , maybe we should close this thread, I will settle for the reminder with the opening of the workbook.

    I am grateful for your input.

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Msgbox based on cell value (Office 2003 or 97)

    If you want that message when you open the workbook for each worksheet in the workbook where H5>10, then this modification to Hans' code will do that:

    <pre>Private Sub Workbook_Open()
    Dim oSht As Worksheet
    For Each oSht In Worksheets
    If oSht.Range("H5") > 10 Then
    MsgBox "Watch it, mate! " & oSht.Name, vbInformation
    End If
    Next oSht
    End Sub
    </pre>


    If you want the message when you open the workbook only once if any worksheet in the workbook has H5>10, then use this:

    <pre>Private Sub Workbook_Open()
    Dim oSht As Worksheet
    For Each oSht In Worksheets
    If oSht.Range("H5") > 10 Then
    MsgBox "Watch it, mate! " & oSht.Name, vbInformation
    Exit Sub
    End If
    Next oSht
    End Sub
    </pre>

    Legare Coleman

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Msgbox based on cell value (Office 2003 or 97)

    Hi Legare

    Thanks for your reply I will give it a try before I decide which way to go, I think Hans may be correct It may be a bit overpowering.

    Once again Thanks for your reply.

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Msgbox based on cell value (Office 2003 or 97)

    Here's a lightly tested approach that will create a worksheet listing the sheets with values in excess of 10:

    Private Sub Workbook_Open()
    Dim wks As Worksheet, wksErrors As Worksheet
    Dim boolError As Boolean
    Dim lngC As Long

    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("ErrorList").Delete
    Set wksErrors = ThisWorkbook.Worksheets.Add(Worksheets(1))
    wksErrors.Name = "ErrorList"

    For Each wks In ThisWorkbook.Worksheets
    If wks.Range("H5") > 10 Then
    boolError = True
    wksErrors.Cells(lngC + 1, 1).Value = wks.Name
    lngC = lngC + 1
    End If
    Next wks

    If Not boolError Then wksErrors.Delete
    Application.DisplayAlerts = True

    Set wks = Nothing
    Set wksErrors = Nothing
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Msgbox based on cell value (Office 2003 or 97)

    Hi

    I would like to thank Hans and Legare for their help.

    I have managed to achieve what I needed.

    Thanks again

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Msgbox based on cell value (Office 2003 or 97)

    Hi John

    Thanks very much I have certainly received a wealth of information on this one.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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