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

Thread: VBA display message box with values from sheet range

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA display message box with values from sheet range

    Hi,

    Sheet1 has a button that when clicked shows a message box. I would like to show in the message box values from sheet2 withing range A1:A4. This should be in a format of list with lines ...
    line 1 = cell A1
    line 2 = cell A2
    .
    .

    This range has dynamic values and sometimes some cells are empty, depening on values from different cells so for example sometimes there are walues in four cells A1,A2,A3,A4 but sometimes there are values in two cells eg A1,A4.
    If there are values in A1 and A4 i would like to display them in the message box without empty spaces/lines eg.
    line 1 = cell A1
    line 2 = cell A4

  2. #2
    New Member
    Join Date
    May 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA display message box with values from sheet range

    Don't know if your values are numeric or text.
    But the code below works for my example.
    You can adjust it to your liking.

    Code:
    Sub Temp()
    
    
    Dim sh1 As Worksheet
    Set sh1 = ThisWorkbook.Sheets("Blad1")
    
    
    Dim Message As String
    Message = ""
    
    
    Dim Counter As Integer
    Counter = 0
    
    
    Dim Validation As Variant
    
    
    For X = 1 To 4
        Validation = sh1.Range("A" & X).Value
        If Validation <> "" Then
            If Counter = 0 Then
                Message = Validation & " [Line " & X & "]"
                Counter = Counter + 1
            Else
                Message = Message & vbNewLine & Validation & " [Line " & X & "]"
            End If
        End If
    Next X
    
    
    MsgBox Message
    
    
    End Sub

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA display message box with values from sheet range

    Great!

    My values are text.

    Much appreciated!

  4. #4
    New Member
    Join Date
    Apr 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA display message box with values from sheet range

    Is there a way to insert a links to the displayed txt to so on click you go to another msgbox ? Links depend on the cell values ... so cell A1 has it's own link , cell B2 it's own etc . ?

    Cheers A

  5. #5
    New Member
    Join Date
    May 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA display message box with values from sheet range

    I cannot find a way to realize this in a MsgBox control.
    It is possible with an UserForm.

  6. #6
    New Member
    Join Date
    Apr 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA display message box with values from sheet range

    thank you !

    One more question though ... is there a way to not to display msgbox if the message is empty meaning that there is nothing the specified range ?

  7. #7
    New Member
    Join Date
    May 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA display message box with values from sheet range

    Yes, that is possible. Use the code below.

    Code:
    Sub Temp()
    
    Dim sh1 As Worksheet
    Set sh1 = ThisWorkbook.Sheets("Blad1")
    
    
    
    
    Dim Message As String
    Message = ""
    
    
    Dim Counter As Integer
    Counter = 0
    
    
    Dim Validation As Variant
    
    
    For X = 1 To 4
        Validation = sh1.Range("A" & X).Value
        If Validation <> "" Then
            If Counter = 0 Then
                Message = Validation & " [Line " & X & "]"
                Counter = Counter + 1
            Else
                Message = Message & vbNewLine & Validation & " [Line " & X & "]"
                Counter = Counter + 1
            End If
        End If
    Next X
    
    
    If Counter = 0 Then Exit Sub
    
    
    MsgBox Message
    
    
    End Sub

  8. #8
    New Member
    Join Date
    Apr 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA display message box with values from sheet range

    Thanks for a prompt reply!

User Tag List

Tags for this Thread

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
  •  


DMCA.com