Custom MsgBox. Buttons

[ipbr21054]

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

 

[DanteAmor]

Review this

https://www.mrexcel.com/forum/excel...on-captions-standard-msgbox-not-question.html

 

[Rick Rothstein]

Try Googling DialogSheets. Here is a example of its use...

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

 

[ipbr21054]

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

 

[DanteAmor]

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.

 

[ipbr21054]

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

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

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

 

[DanteAmor]

Hi @ipbr21054,

Create the userform with 2 buttons.

Put the following code inside the userform.

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.

Sub OpenForm()
  UserForm1.Show
End Sub

 

[ipbr21054]

Hi,
Works great thanks.

Thanks

In your userform photo example 1 2
How did you get the top part in blue ?

 

[DanteAmor]

Hi,
Works great thanks.
Thanks

Youre Welcome.

In your userform photo example 1 2
How did you get the top part in blue ?

It is the standard color of the Excel 2007 version.

 

[ipbr21054]

I mean did you create that userform to look like that or was it taken from internet etc to show me an example ?