Userform as message box

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,267
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:

Code:
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:


aOa5zSmX.jpeg


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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
If you want to emulate a MsgBox, you could use code like this in the Userform
Code:
' 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
Code:
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top