MsgBox () - Put Numbers In?!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a macro i have written that combines a multidue of variables into one
string which will be pasted into comments to update our internal systems.

To replicate the issue i have put all the code into one msgbox so you can
see what i mean.

Sub Vehicle_Collection()

Dim num, dat, amp, trans, runn, runna, addr, adds, ampa As Variant

num = InputBox("Please Enter the Number Debtor Contacted On (Eg/ 1,2 or
3)", "Please Enter the TD Number")
dat = InputBox("Please Enter the Date of Collection", "Date Of Vehicle
Collection")
amp = MsgBox("What Time is the Collection (Yes = AM, No = PM)", vbYesNo)
trans = InputBox("Please Enter the Transporter Company Who Will Collect
The Vehicle", "Transporter Company")
runn = MsgBox("Is the Vehicle A Runner", vbYesNo)
addr = MsgBox("Select 'YES' for Screen Address, and 'NO' for Other
Address", vbYesNo)

If addr = vbYes Then
adds = "SCREEN ADDRESS"
Else
adds = InputBox("Please Enter The Address Of Vehicle Collection")
End If

If runn = vbYes Then
runna = "IS"
Else
runna = "IS NOT"
End If

If amp = vbYes Then
ampa = "AM"
Else
ampa = "PM"
End If

all = "SPOKE TO DEBTOR TD" & num & ". ARRANGED VEHICLE COLLECTION FOR "
& dat & " - " & amp & ampa & " FROM " & addr & adds & " BY " & trans & ".
VEHICLE " & runn & runna & " A RUNNER, CONFIRMATION LETTER SENT TO DEBTOR.
E-MAIL SENT TO VEHICLE COLLECTIONS."

MsgBox (all)

End Sub

With this the end string puts either the number 6 or 7 infront of the
following variables: - amp, runn and addr.

Is there a way to get round this, or is my code flawed?

Any help much appricaited

PaulW
 
You have included the response from your msgbox - vbYes or vbNo (the numbers
6 and 7). Just remove those from the string

all = "SPOKE TO DEBTOR TD" & num & ". ARRANGED VEHICLE COLLECTION FOR "
& dat & " - " & ampa & " FROM " & adds & " BY " & trans & ".
VEHICLE " & runna & " A RUNNER, CONFIRMATION LETTER SENT TO DEBTOR.
E-MAIL SENT TO VEHICLE COLLECTIONS."

or if you want to show yes or no

all = "SPOKE TO DEBTOR TD" & num & ". ARRANGED VEHICLE COLLECTION FOR "
& dat & " - " & iif(amp=vbYes,"Yes "," No ") & ampa & " FROM " &
iif(addr=vbYes,"Yes ","No ") & adds & " BY " & trans & ".
VEHICLE " & iif(runn=vbYes,"Yes ","No ") & runna & " A RUNNER, CONFIRMATION
LETTER SENT TO DEBTOR.
E-MAIL SENT TO VEHICLE COLLECTIONS."
 
Ah the simple things!!!

Thank you very much for your help on this, very much appriciated!

PaulW
 
Back
Top