Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenation in MsgBox
Excel 2007 - this works just as I expect it to, but I would like to do a
little mo 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenation in MsgBox
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) On Sat, 15 Aug 2009 13:12:01 -0700, katjambor wrote: Excel 2007 - this works just as I expect it to, but I would like to do a little mo 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenation in MsgBox
On Aug 15, 2:12*pm, katjambor
wrote: Excel 2007 - this works just as I expect it to, but I would like to do a little mo 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenation in MsgBox
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! "Matthew Herbert" wrote: On Aug 15, 2:12 pm, katjambor wrote: Excel 2007 - this works just as I expect it to, but I would like to do a little mo 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenation in MsgBox
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! "Chip Pearson" wrote: 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) On Sat, 15 Aug 2009 13:12:01 -0700, katjambor wrote: Excel 2007 - this works just as I expect it to, but I would like to do a little mo 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenation help | Excel Worksheet Functions | |||
Concatenation | Excel Discussion (Misc queries) | |||
Concatenation | Excel Worksheet Functions | |||
concatenation | Excel Worksheet Functions | |||
Concatenation | Excel Programming |