nested if statement in msgbox

[theintern]

Is there a way to have an if statement inside a msgbox? i tried just
throwing it in there and it didn't like it too much. see code below.

MsgBox "In " & MonthBox.Value & " " & YearBox.Value & ":" _
& vbCrLf & " SM hours: " & (totSM) _
& vbCrLf & " SM jobs: " & numSM _
& vbCrLf & " SM ave: " & Format(totSM / numSM,
"0.00") & " hours per job" _
& If Not total=0 then
& vbCrLf & vbCrLf & " Relative Percentages SM: " &
Format(totSM /total, "0.00%") _
& vbCrLf & " PIP: "
& Format(totPIP / total, "0.00%") _
& vbCrLf & " PLB: "
& Format(totPLB / total, "0.00%") _
end if


thanks!
scott

 

[Jan De Messemaeker]

Hi,

First, shouldn't that be InputBox? I thought a MsgBox didn't accept input;
Second, even in an input boix you canot have this sort of logic.
You can have it behind a form reacting f.i. on the entering of the data.
HTH

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[Jack Dahlgren]

In VBA the If statement does not return a value, it is just a control loop.
Declare a variable and use the if statement before the Msgbox to determine
the value of that variable, then concatenate it with the rest of the string
For example:

dim mystring as string
mystring = ""
dim t as task
dim child as task
if t.summary then
for each child in t.outlinechildren
mystring = mystring & ", " child.name
next child
else
mystring = "this task has no child tasks"
End if

msgbox "here is the list of child tasks: " & mystring


It is always a better idea to build complex strings outside the messagebox
statement.
It is easier to debug and you can re-use it if you need to.

-Jack Dahlgren

 

[theintern]

I use a user form to get a month and year from the user, do some calculations
based on that, and display results. the problem is if there is division by
zero, for example when calculating averages. I need to be able to test if
certain variables (like numSM and total) equal zero, and if they do then not
do certiain calculations or display certain results. i could do this with
many large if statements and msgboxes in each one, but i'm hoping to put the
if statements inside the msgbox to save on code. make sense?

thank jan
scott

 

[theintern]

I agree it is better to do the calculations outside the msgbox. I wasn't
going to do this at first since i didn't think there would be many of them,
but then it grew. So I restructured things a bit, calculated everything
outside the msgbox, assigning a value of zero if division by zero was going
to occur, and then just had one nice msgbox statement. beautiful.

thanks!
scott

 

[Jan De Messemaeker]

Hi Scott,

Let's agree on vocabulary first.
A MsgBox is a very simple type of "form" that allows no data entry
An InputBox in VBA allows data entry but no logic.
A UserForm can have many types of controls and each control can have several
procedures attached to it (such as _Click, _Change, etc.)
You need the latter.

In that case you can (for instance) attach a control procedure to the OK
button checking there will be no zerodividsion, and if yes, refuig to close
the form and display a message, etcetera.
HTH

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

I use a user form to get a month and year from the user, do some
calculations
based on that, and display results. the problem is if there is division
by
zero, for example when calcul

ating averages. I need to be able to test if