Results 1 to 7 of 7
  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 contents of a range

    Hi

    Can anyone help please I would like to code a Msgbox to appear based on the contents in a range.
    i.e. if any cell in the range I3 to I34 contains the text "Duplicate Booking" then Msgbox appears saying not available.

    Thanks in advance

    I am using Excel 2010
    If you are a fool at forty, you will always be a fool

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Braddy,

    Try this, worked for me.
    Code:
    Sub RangeMsg()
      
      Dim rngFound As Range
      With Range("I3:I34")
        Set rngFound = .Find("Duplicate Booking")
        If Not rngFound Is Nothing Then MsgBox "Not Available"
      End With
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Thank you for the Code

    Could I impose on you to explain exactly where to place this code, Many Thanks.

    Regards

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

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by braddy60 View Post
    Could I impose on you to explain exactly where to place this code, Many Thanks.
    Braddy,

    That depends on how you want it to be called. As it stands it is a standalone module and could be called from any other code in your VBA project. The code itself, w/o the Sub & End Sub lines could be incorporated directly into any existing code you have. You could also have it fire off of a WorkSheet change event it you want it to run any time one of the Cells in the range change value.

    Let me know.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    I tried it in the sheetchange event but it runs every time the sheet changes whether I3 to I34 contains Duplicate Booking or not.

    Regards

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

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can test for an intersection first:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       Dim rngFound          As Excel.Range
       If Not Intersect(Target, Range("I3:I34")) Is Nothing Then
          With Range("I3:I34")
             Set rngFound = .Find("Duplicate Booking")
             If Not rngFound Is Nothing Then MsgBox "Not Available"
          End With
       End If
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

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

    I think I have a problem elsewhere because I cannot get your code to work. I will have to do some checking.

    Many Thanks for the code.

    Regards
    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
  •