How to print cell "values" in MsgBox?-CodePudding

I have VBA code that goes to a reference workbook and makes a report based on sheet names needed. After this is done I have a output into cells of all the sheets it couldnt find and I want to put it in a MsgBox that pops up with the list of worksheets not found. Here is the 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 go about making that Range("w" &q).Value=t into a message box that lists the worksheet names?

I have been scouring google looking for ideas or solutions but am having issues with formulating this solution. Any help or guidance is appreciated.

CodePudding user response：

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

CodePudding user response：

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