CODEWITHSUNDEEP
vbaexcelmsgbox
Don Desrosiers
Don Desrosiers

Reputation: 143

Message Box referring to cell contents

I cant get the syntax right for a msgbox. I want the box to say:

You have indicated that" "employee name" (a range reference to a cell the worksheet) has worked for "hours" (a range reference to a cell the worksheet) doing "job" (a range reference to a cell the worksheet)

Is this information correct?

This is what I have (shortened slightly):

Public confirmation_yes_no()
    Dim yesornoanswertomessagebox As String
    Dim questiontomessagebox As String
    questiontomessagebox = "You have indicated that" & worksheets("dept 1 input").range("g12"),"worked at" & worksheets("dept 1 input").range("g16"), "for"  & worksheets("dept 1 input").range("g16"), vbinformation, "Are you sure that this data is correct?"
    yesornoanswertomessagebox = MsgBox(questiontomessagebox, vbYesNo, "FlightPlan for Profits PRO")
    If yesornoanswertomessagebox = vbNo Then
        MsgBox "Return to Data Input to correct error(s)", 0, "FlightPlan for Profits PRO"
    Else
        MsgBox "Great!  Press Enter", 0, "FlightPlan for Profits PRO"
    End If

End Sub

I am assuming, of course, that this is possible.

Upvotes: 0

Views: 1988

Answers (2)

SilentRevolution
SilentRevolution

Reputation: 1513

Couple of things with your code,

  • The opening line of your sub, Public confirmation_yes_no(), what is it, is it a sub, function or what, the way it's written right now it is a global variable declaration.
  • When combining elements into one like with your string, always use & but be sure to manually put spaces around it, otherwise it is not recognized. &var1 <> & var1
  • Be cautious when setting the arguments in a variable to be used later and definitely don't set them twice.
  • If you use a qualifier a lot, like Worksheets("dept 1 input"), consider using a With statement like below, this saves you from having to type the bit on the With statement over and over. Please note that to make use of the with statement, you write . in front of the code.
    .Range(... points to the sheet which is set by the With statement.
    Range(... points to the sheet which Excel considers to be active.
  • When combining variables with text, take into account that the variables most likely do not have leading and trailing spaces, and that you'll have to compensate for this in the string bits.
  • for readability you can add an _ to your code to indicate it continues on the next line instead of having a very, very long line.
  • You can use a message box directly in an If statement.

Corrected code

Public Sub confirmation_yes_no()
    Dim questiontomessagebox As String

    With ThisWorkbook.Worksheets("dept 1 input")
        questiontomessagebox = "You have indicated that " & .Range("G12") & " worked at " _
        & .Range("G16") & " for " & .Range("G16") & "." _ 
        & vbCr & vbCr _
        & "Are you sure that this data is correct?"
    End With

    If MsgBox(questiontomessagebox, vbYesNo, "FlightPlan for Profits PRO") = vbNo Then
        MsgBox "Return to Data Input to correct error(s)", 0, "FlightPlan for Profits PRO"
    Else
        MsgBox "Great!  Press Enter", 0, "FlightPlan for Profits PRO"
    End If

End Sub

Upvotes: 3

Moosli
Moosli

Reputation: 3230

Hi you missed the "&" signs. So i Correct it for you.

questiontomessagebox = ("You have indicated that " & Worksheets("dept 1 input").Range("g12") & " ,worked at " _
                    & Worksheets("dept 1 input").Range("g16") & " for " & Worksheets("dept 1 input").Range("g16")) & Chr(32) & _
                    vbInformation & vbNewLine & " Are you sure that this data is correct?"

Upvotes: 2

Related Questions