CODEWITHSUNDEEP
excelvba
mf2022
mf2022

Reputation: 36

How to print cell "values" in MsgBox?

I have VBA code that goes to a reference workbook and makes a report based on sheet names needed.
After this I output into cells all the sheets it couldn't find. I want to put it in a MsgBox.

Output of those missing sheets:

On Error Resume Next
Do While y \<= x
    Workbooks(maker).Activate
    Z = Range("u10:u" & cnt).Find(what:=y, LookIn:=xlValues, lookat:=xlWhole).Select
    If Err \> 0 Then
        V = Range("E10:E" & cnt).Find(what:=y, LookIn:=xlValues, lookat:=xlWhole).Select
        t = Selection.Offset(0, 1)
        'This is where the not found worksheets are printed in column w
        Range("w" & q).Value = t
        q = q + 1
        y = y + 1
    Else
        t = Selection.Offset(0, -1)
        Workbooks(Filename).Sheets(t).Copy After:=Workbooks(temp).Sheets(Workbooks(temp).Sheets.Count)
        Workbooks(maker).Activate
        y = y + 1
    End If
Loop
On Error GoTo 0

How would I make that Range("w" &q).Value = t into a message box that lists the worksheet names?

Upvotes: 1

Views: 182

Answers (2)

Cameron Critchlow
Cameron Critchlow

Reputation: 1827

First, What line pops the error message?

Either way, this is a way to build a message from multiple lines. I realize you're finding sheets that don't exist, so the whole For Each WS thing doesn't apply... but it's useful to demonstrate.

Sub MessageFromCellValues()

    Dim Msg As String
    Dim maker As String
    Dim WS As Worksheet
    Dim X As Long
    Dim Y As Long
    
    maker = "Your Worksheet Name.xlsm"
    
    'Do While Y <= X
        
        ' ... Your Code
        
    'Loop
    
    Msg = " This is the list of Sheet Names " & vbCrLf & vbCrLf
    
    For Each WS In Workbooks(maker).Worksheets
        Msg = Msg & " - " & WS.Name & vbCrLf
    Next WS '
    
    MsgBox Msg, vbOKOnly, "Sheets List"
    
End Sub

Example:
enter image description here

Upvotes: 0

Nick Abbot
Nick Abbot

Reputation: 501

On Error Resume Next
    sMsgTxt = "" ' Initialize msgbox string
Do While y <= x
Workbooks(maker).Activate
Z = Range("u10:u" & cnt).Find(what:=y, LookIn:=xlValues, lookat:=xlWhole).Select
If Err > 0 Then
V = Range("E10:E" & cnt).Find(what:=y, LookIn:=xlValues, lookat:=xlWhole).Select
t = Selection.Offset(0, 1)
'This is where the not found worksheets are printed in column w
Range("w" & q).Value = t
    sMsgTxt = sMsgTxt & t & vbCrLf ' Append to msgbox string
q = q + 1
y = y + 1
Else
t = Selection.Offset(0, -1)
Workbooks(Filename).Sheets(t).Copy After:=Workbooks(temp).Sheets(Workbooks(temp).Sheets.Count)
Workbooks(maker).Activate
y = y + 1
End If
Loop
    MsgBox sMsgTxt ' Output msgbox string
On Error GoTo 0

Upvotes: 1

Related Questions