CODEWITHSUNDEEP
excelvba
sircapalot
sircapalot

Reputation: 1

VBA code to display message based on cell value in a range of cells

I am very new to VBA. I am trying to create a macro that will display a message based on cell value from a range of cells on a different sheet.I already have a macro that essentially is a ctlr + F and the goal is that when that ID number is entered this new macro will display a desired message.

This is what I have and as you can tell I'm very new and probably isn't even close to what I need:

Sub Worksheet_Calculate()
    If Worksheets("worksheet").range("B2:B200") > 0 Then
        MsgBox "message needed"
    Else
        MsgBox "other message needed"
        Exit Sub
    End If
End Sub

In theory if there is a 0 in the cell that was searched from the range of cells in the first macro, then it will display a message, otherwise it will display an alternative message.

Upvotes: 0

Views: 1828

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

The Worksheet_Calculate handler (which should be Private) will execute every single time anything is calculated on that worksheet.

the goal is that when that ID number is entered this new macro will...

Use the Worksheet_Change handler for that. You'll get a ByVal Target As Range parameter that tells you which cell(s) was/were modified; you can use Application.Intersect to determine whether Target intersects with a particular range of cells, and act accordingly - there are plenty of examples on this site.

Now, this code:

If Worksheets("BLD Id List To Be Searched").Range("B2:B200") > 0 Then

Is implicitly taking the Value of a multi-cell Range object, which is a 2D variant array - and then comparing it to 0. That's a guaranteed type mismatch run-time error, because you can't compare a whole array with a single value like this.

Take @BigBen's advice here:

If Application.WorksheetFunction.CountIf([ActiveWorkbook.]Worksheets("name").Range("B2:B200"), ">0") > 0 Then

Note that if you mean to work with ThisWorkbook (i.e. the Excel document that contains the VBA code), and the worksheet "BLD ID list to be searched" exists at compile-time in ThisWorkbook, then the code would be more robust / user-proof if you used the sheet's code name instead of dereferencing it from the Worksheets collection of whatever workbook happens to be active at the time, using its user-modifiable "tab name".

Change the worksheet module's (Name) property to e.g. BLDListSheet, and then you can do this:

If Application.WorksheetFunction.CountIf(BLDListSheet.Range("B2:B200"), ">0") > 0 Then

And never need to worry about keeping the code in sync with the sheet's "tab name".

Now, if the macro runs every time a cell is modified, it doesn't make much sense to show a message and ignore which particular cell was modified, does it?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(BLDListSheet.Range("B2:B200"), Target) Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) Then
        If Target.Value > 0 Then
            ' modified cell has a new value greater than 0
        Else
            ' modified cell has a new value less than or equal to 0
        End If
    Else
        ' Target.Value may not be safe to compare with anything here.
        If IsError(Target.Value) Then Debug.Print Target.Address & " contains an error!"
    End If
End Sub

Upvotes: 1

Related Questions