Concatenation in MsgBox

[katjambor]

Excel 2007 - this works just as I expect it to, but I would like to do a
little more:

Sub HisSales()
Dim intSales As Integer
intSales = Range("O3")
Range("O3").Select
If ActiveCell.Value = 0 Then
If MsgBox("He has no sales this period.", vbOKOnly, "No sales!") =
vbOKOnly Then
End If
ElseIf MsgBox("He has " & intSales, vbOKOnly, "His sales!") = vbOK
Then
End If
End Sub

Cell O3 contains a CountIf formula which tells how many sales he has made
this period, and since he currently has 9, running the code displays a
message box that says, "He has 9". I would like it to say, "He has 9 sales
this period." but can't figure out how to put more text after intSales.

 

[Chip Pearson]

A few things come to mind. First, declare intSales as Long rather than
Integer, just in case the value is greater the 32K. Next, you don't
seem to be doing anything with the result of the MsgBox (the value of
the button clicked), so you can streamline that. Finally, there is no
need to select O3 and use ActiveCell. Try...


Sub HisSales()
Dim intSales As Long
intSales = Range("O3").Value
If intSales = 0 Then
MsgBox "No Sales", vbOKOnly
Else
MsgBox "Has Sales of: " & _
Format(intSales, "#,##0"), vbOKOnly, "Has Sales"
End If
End Sub



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Matthew Herbert]

Excel 2007 - this works just as I expect it to, but I would like to do a
little more:

Sub HisSales()
Dim intSales As Integer
    intSales = Range("O3")
    Range("O3").Select
    If ActiveCell.Value = 0 Then
        If MsgBox("He has no sales this period.", vbOKOnly, "No sales!") =
vbOKOnly Then
        End If
        ElseIf MsgBox("He has " & intSales, vbOKOnly, "His sales!") = vbOK
Then
        End If
    End Sub

Cell O3 contains a CountIf formula which tells how many sales he has made
this period, and since he currently has 9, running the code displays a
message box that says, "He has 9".  I would like it to say, "He has 9 sales
this period." but can't figure out how to put more text after intSales.

katjambor,

You concatenated the first part, so you can use the same method, i.e.
"He has " & intSales & " this period.". Also, I'm not sure why you
have the MsgBox embedded in the If Then statement, especially since
nothing is done regardless of the outcome. You can change this to be
something like the following:

If ActiveCell.Value = 0 Then
MsgBox "He has no sales this period.", vbOKOnly, "No sales!"
Else
MsgBox "He has " & intSales & " this period.", vbOKOnly, "His
sales!"
End If

Best,

Matthew Herbert

 

[katjambor]

Thanks, Matthew - I kept trying to do just that but must have had a comma out
of place or such because I kept getting errors! I've just been looking at
this project to long I guess.

There are things happening after this code, BTW, but these lines were giving
me the issue. I'm self-teaching here & stumbling a bit!

 

[katjambor]

Thanks, Chip - I'm sure there are lots of things I SHOULD be doing to make my
code slicker. This is really my first foray into Excel programming, I guess
Access has spoiled me a little!

BTW, the boss recently sent us to an Excel Techniques class and you were
mentioned more than once in glowing terms!