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:

MsgBox Sheets("Sheet1").Range("A1").Value

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.

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

[Paul B]

Thanks Mark works Great