create list in a message box from range

**jiminic** · 09-08-2008, 12:47 AM

Hi Everyone

I want to show a message that will contain the names of people in a range that is updated regularly. I am using the following code:

Sub Dist_List()
Application.ScreenUpdating = False

Range("D2").Select
    Dim strMsg As String
    Do Until IsEmpty(ActiveCell)
    strMsg = "The following people are currently on the Message Distribution list:" & vbCrLf & vbNewLine
    strMsg = strMsg & "     • " & Range("D2").Value & vbNewLine
    ActiveCell.Offset(1, 0).Select
      Loop
                  
    MsgBox strMsg, vbInformation, "Distribution List"

Application.ScreenUpdating = True
End Sub

The problem I have is , it will only show the person who's name is in cell D2. I have a named range (email2) which currently spans cells D2:D10, but as I said previously, the range can and will grow.

Does anyone have any suggestions?

**royUK** · 09-08-2008, 01:39 AM

Try

Option Explicit

Sub Dist_List()
    Dim rng    As Range
    Dim Cl     As Range
    Dim strMsg As String

    Set rng = Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp))

    strMsg = "The following people are currently on the Message Distribution list:" & vbCrLf & vbNewLine
    For Each Cl In rng
        strMsg = strMsg & "     • " & Cl.Value & vbNewLine
    Next Cl
    MsgBox strMsg, vbInformation, "Distribution List"

End Sub

**jiminic** · 09-08-2008, 01:42 AM

Thank you once again RoyUK, it works perfectly!

**royUK** · 09-08-2008, 03:57 AM

Glad it helped.

create list in a message box from range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

create list in a message box from range

Thread Information

Users Browsing this Thread

Similar Threads

Create combobox list from autofiltered range without advance filter.

List values from range, that aren't in range

vba code to create a list from data in one field

Create HTML link from a list of domains

List and Create List

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions