Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: message box

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yup,

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


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




    HTH

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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




  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Mark works Great

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •