message box

[Paul B]

Can a message box display what’s in a cell? Example sub test() msgbox = A1 end sub I want the message box to display what’s in cell A1

 

[Mark O'Brien]

Yup,

You just need to reference the cell properly. e.g. cell "A1" on "Sheet1" would be:

<pre>
MsgBox Sheets("Sheet1").Range("A1").Value

</pre>


HTH

 

[Paul B]

Thanks, thought I could take it from here but, can it list a name range say if A1:A10 is named "My_list" can it show whats in A1:A10 in the message box?

 

[Mark O'Brien]

Using "A1:A10" as the range "My_List" this code will throw up a message box with the contents of these cells. I hope this is what you want.

<pre>

Public Sub main()

Dim NameSht As Worksheet
Dim NameRng As String
Dim Rng As Range
Dim Msg As String
Dim SubStr As String
Dim Index As Long

'Create space instring
Msg = Space(10000)

Index = 1

Set NameSht = Application.Names("My_List").RefersToRange.Worksheet
NameRng = Application.Names("My_List").RefersToRange.Address


For Each Rng In NameSht.Range(NameRng)
SubStr = Rng.Value & vbNewLine
Length = Len(SubStr)
' stuff the string inside the result variable
Mid$(Msg, Index, Length) = SubStr
' advance index
Index = Index + Length
Next

'Trim extra characters off of Msg
Msg = Left$(Msg, Index - 1)

MsgBox Msg

End Sub

</pre>

 

[Paul B]

Thanks Mark works Great