×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using variables in a message box function with VBA

Using variables in a message box function with VBA

Using variables in a message box function with VBA

(OP)
I have an Excel document that I am adding VBA code for and I need to display the values of two variables in the message box so the user can see what the remaining stock values are for a certain product when they place an order.  I entered the commands in just as my reference book says but the message box doesnt show the value of the variables.  Does anybody know how to make this work.  I have the code pasted below.  Thanks a million.

Sub Insert_Order()
'
' Insert_Order Macro
'

'
Dim Item As String
Dim Ordered As Integer
Dim Instock As Integer
Dim Remaining As Integer
Item = Worksheets("Order Form").Range("C6").Value
Ordered = Worksheets("Order Form").Range("C8").Value
Instock = Application.WorksheetFunction.VLookup(Range("C5"), Range("Inventory"), 6, F)
Remaining = Instock - Ordered

If Remaining >= 0 Then
    Sheets("Submitted Orders").Select
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Order Form").Select
    Range("C4:C11").Select
    Selection.Copy
    Sheets("Submitted Orders").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("Order Form").Select
    Range("C14:C19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Submitted Orders").Select
    Range("I3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Range("A1").Select
    Sheets("Order Form").Select
    Range("C4").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C5").Select
    Selection.ClearContents
    Range("C8").Select
    Selection.ClearContents
    Range("C14:C19").Select
    Selection.ClearContents
    Range("C4").Select
    MsgBox "You have Remaining Item remaining in stock.", vbInformation, "Order Entered"
Else
    MsgBox "There are only Instock Item in stock.", vbCritical, "Order Rejected"
End If

    
        
End Sub

RE: Using variables in a message box function with VBA


Hi

That's easy:

use the &-operator (+ also works with strings)

example:
MsgBox "text" & numbervariable & "more text"


Kim

RE: Using variables in a message box function with VBA

Try this:

MsgBox "You have " & Remaining & " Item remaining in stock.", vbInformation, "Order Entered"

It is not considered good practice to use the + operator with strings as VB will first try to process your strings as numbers, which can lead to confusion.

msgbox "1" + "2" ' yields 3

msgbox "1" & "2" ' yields 12

RE: Using variables in a message box function with VBA

(OP)
Oh I see. It works now. Thanks for the help everybody. I really appreciate it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources