Excel Macro help!?

Question

Im trying to display cell b3 in a message box. The cell is in a sheet called Data.

So far i got:
MsgBox "The answer is "....Now i want to display cell b3 but i cant. Please help!

Answer 1

You do it like this:

MsgBox ("The answer is " & _
Worksheets("Data").Range("B3").Value & ".")

However, I would recommend naming the range, then using that to reference the cell in your macro. For example, you could call the cell "MyAnswer", then write the following for your macro.

MsgBox ("The answer is " & _
Range("MyAnswer").Value & ".")

The space followed by an underscore represents a line continuation for a line of code. It tells the computer to accept the following line of cone as if it was part of that line of code.

To name the cell, select the cell then go up to the Name Box which is just above cell A1. It should state B3 in the Name Box if you have cell B3 selected. Put your cursor in the Name Box and type in MyAnswer and then hit the enter button. You will have just named cell B3 on the Data worksheet as MyAnswer. You will no longer need to specify the worksheet because that name refers to that cell range wherever that cell range is in the workbook. And if someone decides to insert a row or column that moves the contents in cell B3, because the cell range is named the macro will follow the cell range to wherever it moves to. If you use the absolute cell reference of just B3 it won't move along with the data it will return whatever is in cell B3 no matter what. It also will allow the macro to reference that data even if someone changes the worksheet name. If you don't name the range, then when somebody changes the name of the worksheet, the macro will no longer work.

Or to simplify my second example even further:

MsgBox "The answer is " & [MyAnswer].Value & "."

The brackets are shortcut notation to specify that MyAnswer is a range. And really since Value is the default property of a Range you don't need to specify the Value property although it is a good idea to.

So the most simple form of my second example would be:

MsgBox "The answer is " & [MyAnswer] & "."

If you use Al's example it will give you the answer from B3 on whatever worksheet is currently active and that may or may not be the Data worksheet.

Answer 2

Msgbox "The answer is " & Worksheets("Data").Range("B3") .Value

Answer 3

Sub Macro1()
MsgBox "The answer is " & [B3].Value
End Sub