MsgBox

[Animalised]

Hi Everyone,

Im trying to have a message box state how much someone has overspent by, which works.. but it specifies that everyone has overspent (overspending by -£200), meaning that it opens a MsgBox for each person specified in the document (opening 18 MsgBox's in total).

Is there any way i can only open a message box if the person HAS actually overspent. I've tried to end the If function if the value = 0 but that isn't working!

A cut out of the code, along with my attempt, is below:

        If Range(Cells(12, "D")) > 200 Then
MsgBox "Jack Holdsworth has OVERSPENT by £" & Range("D12").Value - 200
        ElseIf Range(Cells(12, "D")).Currency = 0 Then
    End If

The full code is below, if interested:

    If Range(Cells(7, "D")) > 200 Then        MsgBox "Peter Beardsall has OVERSPENT by £" & Range("D7").Value - 200
    End If
        If Range(Cells(12, "D")) > 200 Then
        MsgBox "Jack Holdsworth has OVERSPENT by £" & Range("D12").Value - 200
        ElseIf Range(Cells(12, "D")).Currency = 0 Then
    End If
        If Range(Cells(17, "D")) > 200 Then
        MsgBox "J/S Honley has OVERSPENT by £" & Range("D17").Value - 200
    End If
        If Range(Cells(22, "D")) > 200 Then
        MsgBox "Paul Meldrum has OVERSPENT by £" & Range("D22").Value - 200
    End If
        If Range(Cells(27, "D")) > 200 Then
        MsgBox "John Wilson has OVERSPENT by £" & Range("D27").Value - 200
    End If
        If Range(Cells(32, "D")) > 200 Then
        MsgBox "Steve Armstrong has OVERSPENT by £" & Range("D32").Value - 200
    End If
        If Range(Cells(7, "H")) > 200 Then
        MsgBox "Nick Dyson has OVERSPENT by £" & Range("H7").Value - 200
    End If
        If Range(Cells(12, "H")) > 200 Then
        MsgBox "Stephen Helm has OVERSPENT by £" & Range("H12").Value - 200
    End If
        If Range(Cells(17, "H")) > 200 Then
        MsgBox "Andy Lindley has OVERSPENT by £" & Range("H17").Value - 200
    End If
        If Range(Cells(22, "H")) > 200 Then
        MsgBox "Robert Speight has OVERSPENT by £" & Range("H22").Value - 200
    End If
        If Range(Cells(27, "H")) > 200 Then
        MsgBox "Kevin Taylor has OVERSPENT by £" & Range("H27").Value - 200
    End If
        If Range(Cells(32, "H")) > 200 Then
        MsgBox "Ian Walsh has OVERSPENT by £" & Range("H32").Value - 200
    End If
        If Range(Cells(7, "L")) > 200 Then
        MsgBox "Danny Greenwood has OVERSPENT by £" & Range("L7").Value - 200
    End If
        If Range(Cells(12, "L")) > 200 Then
        MsgBox "Simon Hepworth has OVERSPENT by £" & Range("L12").Value - 200
    End If
        If Range(Cells(17, "L")) > 200 Then
        MsgBox "Saj has OVERSPENT by £" & Range("L17").Value - 200
    End If
        If Range(Cells(22, "L")) > 200 Then
        MsgBox "Stephen Wroe has OVERSPENT by £" & Range("L22").Value - 200
    End If
        If Range(Cells(27, "L")) > 200 Then
        MsgBox "Paul Hirst has OVERSPENT by £" & Range("L27").Value - 200
    End If
        If Range(Cells(32, "L")) > 200 Then
        MsgBox "Ben Andrewan has OVERSPENT by £" & Range("L32").Value - 200
    End If

Any help is very much appreciated, thank you!

 

[Andy2016]

Do your cell references work?

They seem to be missing a second reference, but then it should crash at this point rather than generate incorrect message boxes. Unless you have some error statement that we cant see

For example I think the reference in your short example should be:

Range(Cells([COLOR=#333333]12[/COLOR], "D"), Cells([COLOR=#333333]12[/COLOR], "D"))

This was the fist thing I noticed when looking at your question

 

[GTO]

Do you have On Error Resume Next someplace ahead of the code shown? If so, rid it.

Try something like:

Option Explicit
  
Sub example()
Dim WS As Worksheet
  
  Set WS = ThisWorkbook.Worksheets("Sheet1")
  
  With WS
    If .Cells(12, "D").Value > 200 Then
      MsgBox "Jack Holdsworth has OVERSPENT by £" & Range("D12").Value - 200
    End If
    'etc
  End With
  
End Sub

Hope that helps,

Mark

 

[Animalised]

It has the same result:

(as stated in the MsgBox)

"Jack Holdsworth has OVERSPENT by£-200"

 

[GTO]

Hi there,

Who are you replying to?

Mark

 

[svendiamond]

Try changing this:

If Range(Cells(12, "D")) > 200 Then
         MsgBox "Jack Holdsworth has OVERSPENT by £" & Range("D12").Value - 200
ElseIf Range(Cells(12, "D")).Currency = 0 Then
End If

to this:

If Range("D12").Value > 0 Then
         MsgBox "Jack Holdsworth has OVERSPENT by £" & Range("D12").Value - 200
End If

Edit: Actually wait why are you checking to see if it's over 200? What exactly are the qualifications here on whether or not someone has "overspent"?

 

[Animalised]

Svendiamond: I'm checking to see if the VALUE is greater than 200.

But I'm also trying to state that if nothing has been spent or if the value doesn't exceed then it will not present the message box.

 

[GTO]

Did you try the code at #3? In a blank workbook...