Create Message Box containing different variables.

[grumpyoldsod]

Using EXCEL 2000, I want to create a 'Message Box', within a macro
routine, that will open when someone navigates to another worksheet
using 'navigation' buttons I've created on the worksheets.

I have no problem with the usual 'Fixed' messages e.g."Please check
Inputs before proceeding to next page", but I want a message that will
be specific to certain Inputs.
e.g. a perfect item is :-
1. Weight = 5 - 10Kg
2. Volume = 0.5 - 1.0 cubic meter

Now, if the end user makes entries to the worksheet, that when
calculated, show that the box weighs 7Kg and equals 1.2 cubic meters,
then I want a 'Message Box' to open stating that Weight is within
parameters ('OK') but Volume is 0.2 cubic meters outside the
parameters. Whatever Weight & Volume is calculated, I need to show on
the 'Message Box' when and by how much the parameters have been missed,
so in the above example Weight & Volume could each be a - value, +
value or 'OK' value. I also want each parameter to be shown on a
separate line on the 'Message Box', not one long line.

How do I set-up a 'Message Box' to place any EXCEL calculated variable
figures within the Message?

Note: the only option on the 'Message Box' for the end-user to click
will be 'OK', not anything else like 'Yes', 'No', 'Cancel', unless
someone can tell me how to display a 'Message Box' for, say 5 seconds,
before the macro routine continues (i.e. navigates to the next page)!!!

 

[Guest]

Perhaps something like this:

Sub MessageBox()

Dim msg As String, Wr As String, Vr As String
Dim wl As Double, wu As Double, vl As Double, vu As Double, Weight As
Double, Volume As Double

msg = "The weight is % " & vbCrLf & vbCrLf & "The Volume is #"

wl = 5: wu = 10 ' Lower/Upper limits for weight
vl = 0.5: vu = 1 ' Ditto volume

Weight = 3 'Test data
Volume = 1.5

Select Case Weight
Case Is < wl
Wr = Trim(Str(Weight - wl)) & "kg"
Case Is > wl
Wr = "+" & Trim(Str(Weight - wu)) & "kg"
Case Else
Wr = "OK"
End Select

Select Case Volume
Case Is < vl
Vr = Trim(Str(Volume - vl)) & " cu.m"
Case Is > vl
Vr = "+" & Trim(Str(Volume - vu)) & " cu.m"
Case Else
Wr = "OK"
End Select

msg = Replace(msg, "%", Wr)
msg = Replace(msg, "#", Vr)

MsgBox msg

End Sub

HTH

 

[Guest]

Modified to improve formatting of the message:

Sub MessageBox()

Dim msg As String, Wr As String, Vr As String
Dim wl As Double, wu As Double, vl As Double, vu As Double, Weight As
Double, Volume As Double

msg = "The weight is % " & vbCrLf & vbCrLf & "The Volume is #"

wl = 5: wu = 10
vl = 0.5: vu = 1

Weight = 15 ' Test Data
Volume = 0.3

Select Case Weight
Case Is < wl
Wr = Format(Weight - wl, "#0.0") & " kg"
Case Is > wu
Wr = "+" & Format(Weight - wu, "#0.0") & " kg"
Case Else
Wr = "OK"
End Select

Select Case Volume
Case Is < vl
Vr = Format(Volume - vu, "#0.0") & " cu.m"
Case Is > vu
Vr = "+" & Format(Volume - vu, "#0.0") & " cu.m"
Case Else
Vr = "OK"
End Select

msg = Replace(msg, "%", Wr)
msg = Replace(msg, "#", Vr)

MsgBox msg

End Sub