Have MsgBox show other button than Yes No Cancel

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
So currently my code has vbQuestion & vbYesNoCancel. I am trying to figure out if I can replace Yes No Cancel with my own options. Preferably (not necessarily) without editing code too much.
Code:
    If Dir(FPath & "\" & FName & FExt) <> "" Then
        wAns = MsgBox("The file already exists. Do You want to replace it", vbQuestion & vbYesNoCancel, "Save Copy")
        Select Case wAns
            Case vbYes
                Application.DisplayAlerts = False
                ThisWorkbook.Save
                Application.DisplayAlerts = True
            Case vbNo
                Application.DisplayAlerts = False
                ThisWorkbook.SaveAs Filename:=FPath & FName & "_2" & FExt
                Application.DisplayAlerts = True
            Case vbCancel
                MsgBox "Process canceled"
        End Select
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
The only way to have customized buttons is to create a form. Here's one way to make it work with minimal changes...

Code:
[FONT=Verdana]  Application.DisplayAlerts = False[/FONT]
[FONT=Verdana]  If Dir(FPath & "" & FName & FExt) <> "" Then[/FONT]

[FONT=Verdana]    If MsgBox("The file already exists. Do You want to replace it", vbYesNo) = vbYes Then[/FONT]
[FONT=Verdana]      ThisWorkbook.Save[/FONT]
[FONT=Verdana]    ElseIf MsgBox("Would you like to save a copy?", vbYesNo) = vbYes Then[/FONT]
[FONT=Verdana]      ThisWorkbook.SaveAs FileName:=FPath & "" & FName & "_2" & FExt[/FONT]
[FONT=Verdana]    Else[/FONT]
[FONT=Verdana]      MsgBox ("Process canceled")[/FONT]
[FONT=Verdana]    End If
[/FONT]
[FONT=Verdana]  End If[/FONT]
[FONT=Verdana]  Application.DisplayAlerts = True[/FONT]
 
Last edited:
Upvote 0
Whilst it's possible to customise the buttons, it's not that easy.
You would be better off creating a userform instead.
 
Upvote 0
It is something I have never done before.well.. Only did it few times in Access very long time ago.
I will try to research on it.
So if I create this userform, will I still keep my code ?
 
Last edited:
Upvote 0
You could use your code like
Code:
   If Dir(fPath & "\" & Fname & FExt) <> "" Then
      UserForm3.Show
      Select Case UserForm3.Tag
         Case 1
            Application.DisplayAlerts = False
            ThisWorkbook.Save
            Application.DisplayAlerts = True
         Case 2
            Application.DisplayAlerts = False
            ThisWorkbook.SaveAs FileName:=fPath & Fname & "_2" & FExt
            Application.DisplayAlerts = True
         Case 3
            MsgBox "Process canceled"
      End Select
      Unload UserForm3
   End If
And on the userform, you would need code like
Code:
Private Sub CommandButton1_Click()
Me.Tag = 1
Me.Hide
End Sub

Private Sub CommandButton2_Click()
Me.Tag = 2
Me.Hide
End Sub

Private Sub CommandButton3_Click()
Me.Tag = 3
Me.Hide
End Sub
It may just be simpler to accept the limitations of the standard Msgbox
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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