Custom MsgBox. Buttons

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Evening,
I’m looking for a msgbox but with buttons as 1 & 2.
Is there a way to do this without it being a userform. Thanks
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try Googling DialogSheets. Here is a example of its use...

Code:
Sub ButtonAlternative()
  Const SheetID As String = "_Buttonz"
  Dim btnDlg As DialogSheet
   
  Application.ScreenUpdating = False
  On Error Resume Next
  Application.DisplayAlerts = False
  ActiveWorkbook.DialogSheets(SheetID).Delete
  Application.DisplayAlerts = True
  Err.Clear
   
  Set btnDlg = ActiveWorkbook.DialogSheets.Add
   
  With btnDlg
    .Name = SheetID
    .Visible = xlSheetHidden
     
    With .DialogFrame
      .Height = 100
      .Width = 280
      .Caption = "Buttons 1 and 2..."
    End With
     
    With .Buttons("Button 2")
      .BringToFront
      .Height = 20
      .Width = 60
      .Caption = "Button 1"
    End With
     
    With .Buttons("Button 3")
      .BringToFront
      .Height = 20
      .Width = 60
      .Caption = "Button 2"
    End With
     
    .Labels.Add 100, 50, 120, 100
    .Labels(1).Caption = "Push either Button 1 or Button 2"
    Application.ScreenUpdating = True
     
    If .Show = True Then
    MsgBox "Your ''Button 1'' code goes here", 64, "Button 1 was clicked"
    Else
    MsgBox "Your ''Button 2'' code goes here", 64, "Button 2 was clicked"
    End If
     
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
   
  End With
End Sub
 
Upvote 0
Dante i took a look at that but so much text i was confused so i left it.

Even when i then googled Dialog Sheets it seemed over the top & i also didnt understand that.
I know
Dialog Sheets were used before user forms but even if i right click the tab INSERT is greyed out.

I think all this for a msgbox of 1 or 2 option on it is not worth it.

Many thanks
 
Upvote 0
Dante i took a look at that but so much text i was confused so i left it.

Even when i then googled Dialog Sheets it seemed over the top & i also didnt understand that.
I know
Dialog Sheets were used before user forms but even if i right click the tab INSERT is greyed out.

I think all this for a msgbox of 1 or 2 option on it is not worth it.

Many thanks

Totally agree with you, it's not worth it. What you ask for seems simple, but as you can see it is not simple.
The easiest is to resort to the userform, it is more practical to use.
Let me know if you need help for the userform.

7495194fb788fc2561819f1c31afe1f1.jpg
 
Upvote 0
Hi,

If you could please that would be great.

I will supply the codes for buttons below.

This relates to Print 1 invoice or 2 invoices.

1 Invoice

Code:
Private Sub Print_One_Invoice_Click()

If Range("N18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "Payment Type Not Selected"
    Exit Sub
Else
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
     MsgBox "ONCE INVOICE HAS PRINTED CLICK OK" & vbNewLine & vbNewLine & "TO SAVE INVOICE & CLEAR INFO", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
End If
        
Dim strFileName As String


strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & Range("N4").Value & ".pdf"
If Dir(strFileName) <> vbNullString Then
    MsgBox "INVOICE " & Range("N4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
    Exit Sub
Else
    With ActiveSheet
        .PageSetup.PrintArea = "$G$3:$O$61"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        MsgBox "INVOICE " & Range("N4").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
        Range("G27:N36").ClearContents
        Range("G46:G48").ClearContents
        Range("G47:I51").ClearContents
        Range("N18").ClearContents
        Range("N4").Value = Range("N4").Value + 1
        Worksheets("INV2").Range("N4").Value = Range("N4").Value
        Range("G13").ClearContents
        Range("G13").Select
        ActiveWorkbook.Save
    End With
End If
End Sub

2 Invoices

Code:
Private Sub Print_Two_invoices_Click()    If Range("N18") = "" Then
        MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "Payment Type Not Selected"
    Else
ActiveWindow.SelectedSheets.PrintOut Copies:=2
     MsgBox "ONCE INVOICE HAS PRINTED CLICK OK" & vbNewLine & vbNewLine & "TO SAVE INVOICE & CLEAR INFO", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
End If
        
Dim strFileName As String


strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & Range("N4").Value & ".pdf"
If Dir(strFileName) <> vbNullString Then
    MsgBox "INVOICE " & Range("N4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
    Exit Sub
Else
    With ActiveSheet
        .PageSetup.PrintArea = "$G$3:$O$61"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        MsgBox "INVOICE " & Range("N4").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
        Range("G27:N36").ClearContents
        Range("G46:G48").ClearContents
        Range("G47:I51").ClearContents
        Range("N18").ClearContents
        Range("N4").Value = Range("N4").Value + 1
        Worksheets("INV2").Range("N4").Value = Range("N4").Value
        Range("G13").ClearContents
        Range("G13").Select
        ActiveWorkbook.Save
    End With
End If
End Sub
 
Upvote 0
Hi @ipbr21054,

Create the userform with 2 buttons.

Put the following code inside the userform.
Code:
Private Sub CommandButton1_Click()
  Call Print_Invoice(1)
End Sub
Private Sub CommandButton2_Click()
  Call Print_Invoice(2)
End Sub


Private Sub Print_Invoice(n As Long)
  Dim strFileName As String
  If Range("N18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "Payment Type Not Selected"
    Exit Sub
  End If
[COLOR=#0000ff]  ActiveSheet.PrintOut Copies:=n[/COLOR]
  MsgBox "ONCE INVOICE HAS PRINTED CLICK OK" & vbNewLine & vbNewLine & "TO SAVE INVOICE & CLEAR INFO", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & Range("N4").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    MsgBox "INVOICE " & Range("N4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
    Exit Sub
  End If
  With ActiveSheet
    .PageSetup.PrintArea = "$G$3:$O$61"
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "INVOICE " & Range("N4").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
    Range("G27:N36").ClearContents
    Range("G46:G48").ClearContents
    Range("G47:I51").ClearContents
    Range("N18").ClearContents
    Range("N4").Value = Range("N4").Value + 1
    Worksheets("INV2").Range("N4").Value = Range("N4").Value
    Range("G13").ClearContents
    Range("G13").Select
    ActiveWorkbook.Save
  End With
End Sub

To call the userform, put the following code in a module.

Code:
Sub OpenForm()
  UserForm1.Show
End Sub
 
Upvote 0
Hi,
Works great thanks.

Thanks

In your userform photo example 1 2
How did you get the top part in blue ?
 
Last edited:
Upvote 0
I mean did you create that userform to look like that or was it taken from internet etc to show me an example ?
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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