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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

LuisV805

New Member
Joined
Jan 9, 2014
Messages
23
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,301
Office Version
  1. 365
Platform
  1. Windows
Whilst it's possible to customise the buttons, it's not that easy.
You would be better off creating a userform instead.
 

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,301
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

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
Top