Can a message box title be coded?

[Ironman]

Hi

I've looked around online and I can only see text strings for the message box title. I was wondering if it's possible to code it e.g.

vbInformation, "Format(Sheets("Daily Tracking").Range("CG375").Value + 1000 - A, "#,##0") & " miles" Countdown"

When I tried that, it errored "syntax error" so I'm guessing the answer is no?

 

[Joe4]

First build it in a string variable. Then use that string variable in your code.
That also makes it east to verify you have created the string correctly (the end of yours looks a little off).

Dim ttl As String
ttl = "This is a test"
MsgBox "How are you?", vbInformation, ttl

 

[Ironman]

Ah great, thanks Joe, I'll give that a go.

 

[Ironman]

Hi Joe

I've tested your code out and your method works great as below:

ttl = Format(Sheets("Daily Tracking").Range("CG375").Value + 1000, "#,##0") & " Mile Countdown"

But could I just have a little help with the final bit please, which I just can't get right - it's this part:

Range("CG375").Value + 1000, "#,##0"

The value of Cell CG375 is 27,903 and the above code shows the value as 28,903, which isn't what I want. All I'm after is for the value of CG375 in the the above code to be rounded up to the nearest whole 1,000 i.e. until the value of CG375 reaches 28,001 I need it to show 28,000 and after that 29,000 and so on.

Thank you!

 

[Joe4]

Try this:

ttl = Application.WorksheetFunction.RoundUp(Sheets("Daily Tracking").Range("CG375"), -3) & " Mile Countdown"

 

[Ironman]

Hey, that's great, thanks a lot Joe - could I just finally please ask if you could include the 1,000 comma separator in the above?

Thanks again!

 

[Joe4]

Hey, that's great, thanks a lot Joe - could I just finally please ask if you could include the 1,000 comma separator in the above?

Thanks again!

That is where you add in the FORMAT function like you had in your original one:

ttl = Format(Application.WorksheetFunction.RoundUp(Sheets("Daily Tracking").Range("CG375"), -3), "#,##0") & " Mile Countdown"

 

[Ironman]

Awwww that's brilliant, I don't know why I didn't think of that.

Many thanks for all your help Joe!

Edit: I did try that but I assumed it needed another set of brackets, which is where I went wrong.

 

[Joe4]

You are welcome.
Glad I was able to help!