Putting contents of a cell in a Message Box

[Jimmy P]

I want to use a Message Box to display the contents of a worksheet cell in between a text string. I keep getting an error "Compile Error" "Syntax Error".

MsgBox "Sample # Sheets("Calc").Range("A2").value "Loaded" & vbNewLine & "Click OK to Load", & vbNewLine, "Next Sample", vbONnly, "Load Complete"

If I remove Sheets("Calc").Range("A2").value it works OK to display the text strings.

HELP!!!!!

 

[Rick Rothstein]

I want to use a Message Box to display the contents of a worksheet cell in between a text string. I keep getting an error "Compile Error" "Syntax Error".

MsgBox "Sample # Sheets("Calc").Range("A2").value "Loaded" & vbNewLine & "Click OK to Load", & vbNewLine, "Next Sample", vbONnly, "Load Complete"

If I remove Sheets("Calc").Range("A2").value it works OK to display the text strings.

I think this is what you want...

MsgBox "Sample # " & Sheets("Calc").Range("A2").Value & "Loaded" & vbNewLine & "Click OK to Load" & vbNewLine, "Next Sample", vbONnly, "Load Complete"

 

[hateme28]

try this:

"Sample [COLOR=#0000cd]#" & Sheets("Calc").Range("A2").value &[/COLOR] "Loaded" &

 

[Rick Rothstein]

try this:

"Sample [COLOR=#0000cd]#" & Sheets("Calc").Range("A2").value &[/COLOR] "Loaded" &

There is an extra comma in the OP's text that needs to be removed as well.

 

[Jimmy P]

No joy! Now I get a "Type Mismatch" error. Here the whole Sub. Range "A2" is a number that currently = 1 so the MsgBox should display a "1".

Sub LoadNextSample()
'
Sheets("Samples").Select
'
If Sheets("Calc").range("A2:A2") > 0 Then ' tests value to see if sample count > 0
Sheets("Samples").Select ' selects Sheet Samples
range("B1:B1").Select ' selects Range B1
Application.Goto (ActiveWorkbook.Sheets("Samples").range("B1:B1").Offset(0, Sheets("Calc").range("A2")))
'Offsets from B1 by value in cell A2, which is current sample #

End If
If ActiveCell > Sheets("Calc").Range("TSam") Then ' tests to see if CurrentCell is > Max cells in Row 1:
Sheets("Calc").Select
MsgBox "Sample Run Completed" & vbNewLine & "Click OK to Continue", vbONOnly, "End of Samples" 'If CurrentCell is = TSam, put up MsgBox
End If

Range("B2:B2").Select
Application.Goto (ActiveWorkbook.Sheets("Samples").range("B2:B20").Offset(0, Sheets("Calc").range("A2"))) ' Offset by Value of A2
Selection.Copy
Sheets("Calc").Select
Range("B2:B20").Select
ActiveSheet.Paste ' Pastes calculated results from Calc
Sheets("Samples").Select
Range("B1:B1").Select
Application.Goto (ActiveWorkbook.Sheets("Samples").range("B1:B1").Offset(0, Sheets("Calc").range("A2"))) ' Offsets Value of A2
Selection.Copy
Sheets("Calc").Select
Range("A2").Select
ActiveSheet.Paste

" Crashes here with "Type Mismatch" error
MsgBox "Sample #" & Sheets("Calc").range("A2").Value & "Loaded" & vbNewLine & "Click OK to Load", vbNewLine, "Next Sample", vbONnly, "Load Complete"

End Sub

 

[Rick Rothstein]

" Crashes here with "Type Mismatch" error
MsgBox "Sample #" & Sheets("Calc").range("A2").Value & "Loaded" & vbNewLine & "Click OK to Load", vbNewLine, "Next Sample", vbONnly, "Load Complete"

What is the red highlighted text supposed to be? It starts with "vb" so it looks like it is supposed to be a built-in VB constant, but VB does not have one with that name.

 

[Jimmy P]

What is the red highlighted text supposed to be? It starts with "vb" so it looks like it is supposed to be a built-in VB constant, but VB does not have one with that name.

Put "OK" in MsgBox. Works great. I'm using Excel V 2007.

 

[Rick Rothstein]

Put "OK" in MsgBox. Works great. I'm using Excel V 2007.

On my system, the argument to display the OK button is vbOKOnly (not vbNnly as you spelled it). In any event, this works properly for me...

MsgBox "Sample #" & Sheets("Calc").Range("A2").Value & "Loaded" & vbNewLine & "Click OK to Load" & vbNewLine & "Next Sample", vbOKOnly, "Load Complete"

 

[Jimmy P]

Works great. Many thanks for your quick and precise solution. Goes in my VB crib notes. I'm learning VBA by trial and error.

Made a typo on vbOKOnly. After mucho hours on this project I'd reached the point where my Brain didn't accurately process what my eyes saw.

Used to live in Sparta (Lake Mohawk), NJ. Loved it except for the winters. Brrrrr!