Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Message Box containing different variables.
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)!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Message Box containing different variables.
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 " wrote: 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)!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Message Box containing different variables.
Spotted minor error:
Case Else (for Volume) should be Vr="OK" not Wr="OK". Sorry! "Toppers" wrote: 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 " wrote: 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)!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Message Box containing different variables.
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 " wrote: 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)!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create an automatic worksheet with two variables | Excel Worksheet Functions | |||
create line graph with 3 variables? | Excel Discussion (Misc queries) | |||
How do I create a Excel bar chart with 3 variables? | Charts and Charting in Excel | |||
Create possible combinations from three variables | Excel Discussion (Misc queries) | |||
Variables Accross two Message boxes | Excel Discussion (Misc queries) |