Multiple options messagebox

**koltregaskes** · 06-28-2011, 12:00 PM

I'd like to have a message box appear when I run a macro that asks me if I want to use overdue OR due today OR due tomorrow OR due this week OR due this month.

Depending on the selection I'd like the macro to then filter a report in a certain way. I've created a YesNoCancel message box already and the code looks like this:

    iReply = MsgBox(Prompt:="Do you wish to use ALL the data (else macro will use this month's data)?", _
            Buttons:=vbYesNoCancel, Title:="Past Dates Breakdown")
    If iReply = vbYes Then
        ActiveSheet.Range("$A$2:$BG$1500").AutoFilter Field:=25, Criteria1:=Array( _
         "Deployment South", "Site Share South", "Special Coverage South"), Operator:= _
            xlFilterValues
    ElseIf iReply = vbNo Then
        ActiveSheet.Range("$A$2:$BG$1500").AutoFilter Field:=25, Criteria1:=Array( _
         "Deployment South", "Site Share South", "Special Coverage South"), Operator:= _
            xlFilterValues
        ActiveSheet.Range("$A$2:$BK$1500").AutoFilter Field:=6, Criteria1:="<>"
    Else 'They cancelled (VbCancel)
        Exit Sub
    End If

How could I change this to allow me to select one of the 5 options I mentioned at the top? Checkbox?

**Leith Ross** · 06-28-2011, 12:21 PM

Hello koltregaskes,

The easiest and most flexible solution is to create a UserForm. Place 5 buttons on the UserForm and have each button execute one of the five macros you have written.

**koltregaskes** · 06-29-2011, 09:38 AM

I'm trying to avoid that if possible. Could the macro display a form itself? I see there is a frmMsgBox.MsgCstm command but upon quickly trying it I couldn't get it to work. Could I create a form using frmMsgBox.MsgCstm?

**royUK** · 06-29-2011, 09:59 AM

Why avoid the simplest solution? You can create a UserForm on the fly, but it's easier to create the form first

**royUK** · 06-29-2011, 10:51 AM

Sample code to produce a userform with OptionButtons to run code

'---------------------------------------------------------------------------------------

' DateTime  : 09/05/2007 08:43
' Author    : Roy Cox (royUK)
' Website   : click here for more examples and Excel Consulting
' Purpose   : Add a temporaryform to with Option Buttons to call macros' 
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
Option Explicit

Dim uFrm
Dim x      As Integer
Dim iX          As Integer

'---------------------------------------------------------------------------------------
' Procedure : CreateForm
' DateTime  : 25/05/2009 13:43
' Author    : Roy Cox
' Website   : click here for more examples and Excel Consulting
' Purpose   : Main code to create the form, run from button or menu
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
'
Sub CreateForm()
'run this macro to create the form, this will call the other procedures in the right order
 On Error GoTo exit_handler
    With Application
        .VBE.MainWindow.Visible = False
        'designing UserForm1
        uFrm_Create
        uFrm_Controls_Create
        uFrm_Codes
        uFrm_Show
        
        uFrm_Remove        'To Keep userform1 then comment this line
    End With
End Sub

'---------------------------------------------------------------------------------------
' Procedure : uFrm_Create
' DateTime  : 25/05/2009 13:42
' Author    : Roy Cox
' Website   : click here for more examples and Excel Consulting
' Purpose   : Add temporary Form
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
'
Function uFrm_Create()

    uFrm_Remove    ' If Userform1 exist then remove it.

    With ThisWorkbook.VBProject.VBComponents
        Set uFrm = .Add(3).Designer    ' Create & set form designer
    End With

End Function

'---------------------------------------------------------------------------------------
' Procedure : uFrm_Controls_Create
' DateTime  : 25/05/2009 13:41
' Author    : Roy Cox
' Website   : click here for more examples and Excel Consulting
' Purpose   : Add Controls to the Form
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
'
Function uFrm_Controls_Create()
    Dim Opt   As MSForms.Control
    
    With uFrm.Controls
        x = 5
        For iX = 1 To 5
            Set Opt = .Add("Forms.OptionButton.1")
            With Opt
                .Top = x
                .Left = 25
                .Width = 90
                .Visible = True
                .Caption = Choose(iX, "macro1", "macro2", "macro3", "macro4", "macro5")
            End With
          x = x + 35
        Next iX
    End With
    Set Opt = Nothing
End Function

'---------------------------------------------------------------------------------------
' Procedure : uFrm_Codes
' DateTime  : 25/05/2009 13:40
' Author    : Roy Cox
' Website   : click here for more examples and Excel Consulting
' Purpose   : Add code to CommandButton
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
'
Function uFrm_Codes()


    With ThisWorkbook.VBProject.VBComponents("Userform1").CodeModule
        
        .InsertLines 2, "Sub OptionButton1_Click()"
        .InsertLines 3, "macro1"
        .InsertLines 4, "End Sub"
        .InsertLines 5, "Sub OptionButton2_Click()"
        .InsertLines 6, "macro2"
        .InsertLines 7, "End Sub"
        .InsertLines 8, "Sub OptionButton3_Click()"
        .InsertLines 9, "macro3"
        .InsertLines 10, "End Sub"
        .InsertLines 11, "Sub OptionButton4_Click()"
        .InsertLines 12, "macro4"
        .InsertLines 13, "End Sub"
        .InsertLines 14, "Sub OptionButton5_Click()"
        .InsertLines 15, "macro5"
        .InsertLines 16, "End Sub"
    End With
End Function

'---------------------------------------------------------------------------------------
' Procedure : uFrm_Show
' DateTime  : 25/05/2009 13:40
' Author    : Roy Cox
' Website   : click here for more examples and Excel Consulting
' Purpose   : Set Form Properties & display form
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
'
Function uFrm_Show()
    With UserForm1
        .Caption = "Run a macro"
        .Height = x * 1.05
        .Width = 50
        .Show
    End With
End Function

'---------------------------------------------------------------------------------------
' Procedure : uFrm_Remove
' DateTime  : 25/05/2009 13:41
' Author    : Roy Cox
' Website   : click here for more examples and Excel Consulting
' Purpose   : Delete the Form
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
'
Function uFrm_Remove()
    On Error Resume Next
    With ThisWorkbook.VBProject
        .VBComponents.Remove .VBComponents("Userform1")
    End With
    Err.Clear
End Function