Userform as message box

[Fishboy]

Hi All,

I have been trying to give an existing Excel 2010 workbook a bit of a makeover and part of this has been replacing the standard dull message boxes with client branded userform popups instead. On the whole this has mostly been simple enough, however I have some message boxes which read data from the sheet and display it as part of the message. An example of this can be found below:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
        Set KeyCells = Range("Y5:AQ1500")
            If Target.Cells.Count > 1 Then
                Exit Sub
            End If
                If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing And Target.Value <> "" Then
                    MsgBox Worksheets("Vetting Breakdown").Range("F" & Target.Row).Value _
                    & " " & Worksheets("Vetting Breakdown").Range("G" & Target.Row).Value _
                    & " has been updated." & vbCrLf & vbCrLf & "Status of Address Check is now " _
                    & Worksheets("Vetting Breakdown").Range("Y" & Target.Row).Value & "." & vbCrLf _
                    & "Status of Referencing is now " & Worksheets("Vetting Breakdown").Range("AC" & Target.Row).Value _
                    & "." & vbCrLf & "Status of CRB is now " & Worksheets("Vetting Breakdown").Range("AG" & Target.Row).Value & "." _
                    & vbCrLf & "Status of Financial Probity is now " & Worksheets("Vetting Breakdown").Range("AK" & Target.Row).Value _
                    & "." & vbCrLf & "Status of Sanctions Check is now " & Worksheets("Vetting Breakdown").Range("AO" & Target.Row).Value & "."
                End If
End Sub

Is there a way to have the above extracted information displayed in a userform instead? An example of my customer userform looks like this:

​

I am looking to have the information extracted to be displayed where it currently says "Named ranges have been expanded" in the above example.

Is this something that can be done or am I barking up the wrong tree here?

 

[My Aswer Is This]

I would think you should be able to do that. For that much data I would think you could use textbox1.value=
Or if you used a label it would be label1.value=

This would go into the UserForm initialization script when you open the UserForm
I use UserForms for a lot of things. Like launch pads for scripts.
The old message box is boring.

 

[mikerickson]

If you want to emulate a MsgBox, you could use code like this in the Userform

' in userform code module

Private Sub CommandButton1_Click()
    Me.Tag = CommandButton1.Caption
    Me.Hide
End Sub

Private Sub CommandButton2_Click()
    Me.Tag = CommandButton2.Caption
    Me.Hide
End Sub

Private Sub CommandButton3_Click()
    Me.Tag = CommandButton3.Caption
    Me.Hide
End Sub

Function ShowMsg(Prompt As String, Optional Title As String = "my Message Box", _
    Optional Button1Text As String = "OK", Optional Button2Text As String, Optional Button3Text As String) As String
    
    With Me
        .Caption = Title
        .Label1.Caption = Prompt
        .CommandButton1.Caption = Button1Text
        .CommandButton2.Caption = Button2Text
        .CommandButton3.Caption = Button3Text
        .CommandButton1.Visible = (.CommandButton1.Caption <> vbNullString)
        .CommandButton2.Visible = (.CommandButton2.Caption <> vbNullString)
        .CommandButton3.Visible = (.CommandButton3.Caption <> vbNullString)
        .CommandButton1.Default = True
        .Show
    End With
    
    With UserForm1
        ShowMsg = .Tag
    End With
    Unload UserForm1
End Function

And use it (in a normal module) like this

Sub test()
    UserForm1.ShowMsg "hello", Title:="Testing Mode"
    
    If UserForm1.ShowMsg("Should I do it?", Button1Text:="Yes", Button2Text:="No") = "Yes" Then
        MsgBox "do it"
    Else
        MsgBox "don't"
    End If
End Sub

The Button handling (and other features) could be refined and you can specify other options like you want.
If you have existing code that already uses MsgBoxes, you would probably want to match the arguments and returns from a MsgBox closely, making it easier to convert from Excel MsgBox to your custom box.

 

[Fishboy]

Thanks for the replies guys. I will hopefully have some time to test this all out today or tomorrow. I am hoping that now I have the basics outlined by you I can figure the specifics out myself.

If I have any issues I may well be back with some follow up questions.

Thanks again.

 

[Fishboy]

Thanks for the replies guys. I will hopefully have some time to test this all out today or tomorrow. I am hoping that now I have the basics outlined by you I can figure the specifics out myself.

If I have any issues I may well be back with some follow up questions.

Thanks again.

Many thanks again to both Mike and MyAnswerIsThis. Between you guys it really pointed me in the right direction and I managed to get something working.