Custom MsgBox. Buttons

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,804
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,496
Office Version
2010
Platform
Windows
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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,804
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,734
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,804
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,734
Office Version
2007
Platform
Windows
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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,804
Hi,
Works great thanks.

Thanks

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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,804
I mean did you create that userform to look like that or was it taken from internet etc to show me an example ?
 

Forum statistics

Threads
1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top