Msgbox based on contents of a range

Topic

New Reply

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

Reply | Quote

Replies

Braddy,

Try this, worked for me. :cheers:

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!
Computer Specs

Reply | Quote

Hi RetiredGeek

Thank you for the Code

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

Regards

Braddy

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote