Use Userform instead of Msgbox

[cyliyu]

Hello,
I have a button to execute the following macro.
I would like to know how to replace and use a user form(using listbox) instead.
Any help, please?

Sub test()
  For Each row In ........
  If row.Offset(, 6) = .......
  If IsError........
  msg = msg & row.Value & vbTab & ..........
  End If
  End If
  Next
  MsgBox "C1" & vbTab & "C2" & vbTab & "S1" & vbNewLine & msg
End Sub

 

[Belleke]

No example and half pieces of code and then you want help. Afther 187 messages you should know beter.

 

[cyliyu]

Hi Belleke,

Noted & Thanks.
Attached file extracted from my worksheet.
Need some pointer on how to use user form.

 

[Ajesh]

This should give you a start:

Sub test()

Dim wSht As Worksheet
Dim LastRow As Long
Dim i As Long
Dim MyArray()

Set wSht = ThisWorkbook.Sheets("Sheet1")

LastRow = wSht.Cells(wSht.Rows.Count, 2).End(xlUp).Row

With wSht
    ctr = 0
    For i = 8 To LastRow
        If .Cells(i, 8) = "OK" Then
            If IsError(Application.Match(.Cells(i, 2).Value, ThisWorkbook.Sheets("Sheet2").Columns(3), 0)) Then
                ReDim Preserve MyArray(0 To 2, 0 To ctr)
                MyArray(0, ctr) = .Cells(i, 2).Value
                MyArray(1, ctr) = .Cells(i, 3).Value
                MyArray(2, ctr) = "Missing"
                ctr = ctr + 1
            End If
        End If
    Next i
   
    UserForm1.Show
    With UserForm1.ListBox1
        .Clear
        .ColumnHeads = False
        .ColumnCount = 3
        .ColumnWidths = "50;50;50"
        .List = Application.WorksheetFunction.Transpose(MyArray)
        .TopIndex = 0
    End With
End With

End Sub

BR/Ajesh

 

[cyliyu]

Thanks, Ajesh.
This gives me a good start to apply to my other code.