Global message for different message boxes

Question

I have the following simplified VBA code which shows a message box once the user clicks on a button in the Excel spreadsheet:

Message Box A:

Sub Message_Box_A ()
Answer = MsgBox("Do you want to continue?", vbYesNo)
If Answer = vbYes Then
Sheets("Sheet1").Range("A1").Value = 1
Else
End If
End Sub

Message Box B:

Sub Message_Box_B ()
Answer = MsgBox("Do you want to continue?", vbYesNo)
If Answer = vbYes Then
Sheets("Sheet2").Range("A1").Value = 1
Else
End If
End Sub

Is there a way how I can "globalize" the text of the message box "Do you want to continue?" so if I want to change it I do not have to go through each VBA code of each message box?

Pᴇʜ · Answer 1 · 2017-03-17T13:33:21.390

1

I suggest to use a constant. In a module insert …

Const msgContinue = "Do you want to continue?"

Sub Message_Box_A ()
    Answer = MsgBox(msgContinue, vbYesNo)
    If Answer = vbYes Then
        Sheets("Sheet1").Range("A1").Value = 1
    Else
    End If
End Sub

edited Mar 17 '17 at 13:33

answered Mar 17 '17 at 13:16

Pᴇʜ

56,719
10
49
73

score 0 · Accepted Answer · edited May 23 '17 at 12:32

0

Add a function like this:

Function Answer() As Long
 Answer = MsgBox("Do you want to continue?", vbYesNo)
End Function

then you can use it like this:

Sub Message_Box_A ()

If Answer = vbYes Then
Sheets("Sheet1").Range("A1").Value = 1
Else
End If
End Sub

Edit:

If we want autocomplete works like this answer as @Peh comment. we can use:

    Function Answer() As VbMsgBoxResult
     Answer = MsgBox("Do you want to continue?", vbYesNo)
    End Function

edited May 23 '17 at 12:32

Community

1
1

answered Mar 17 '17 at 13:20

Fadi

3,302
3
18
41

Shouldn't it be `Function Answer() As VbMsgBoxResult`? – Pᴇʜ Mar 17 '17 at 13:31
Thank you @Peh., [MsgBox Function](https://msdn.microsoft.com/en-us/library/aa445082(v=vs.60).aspx) returns an Integer and Long is better than Integer. – Fadi Mar 17 '17 at 13:36
1

Non the less it should be VbMsgBoxResult so autocomplete works – Pᴇʜ Mar 17 '17 at 13:37
@Peh, please add watch to the variable to see that its type is Long. – Fadi Mar 17 '17 at 13:48
1

@Peh, I thought you are talking about data type, yes you are right if we want autocomplete works like [this answer](http://stackoverflow.com/a/1770293/4039286), I will edit my answer, Thank you again. – Fadi Mar 17 '17 at 14:26

Winterknell · Answer 3 · 2017-03-17T13:39:15.413

Terse version:

Public Function PlsContinue() As Boolean
    PlsContinue = MsgBox("Do you want to continue?", vbYesNo) = vbYes
End Function

Sub Message_Box_A()
    If PlsContinue Then Sheets("Sheet1").Range("A1").Value = 1
End Sub

Sub Message_Box_B()
    If PlsContinue Then Sheets("Sheet2").Range("A1").Value = 1
End Sub

Edited to add - flexible version, use as many buttons as you like and feed in different values from each:

Public Function PlsContinue() As Boolean
    PlsContinue = MsgBox("Do you want to continue?", vbYesNo) = vbYes
End Function

Sub Message_Box(ByVal sht As String, ByVal cel As String, vlu As Long)
    If PlsContinue Then Sheets(sht).Range(cel).Value = vlu
End Sub

Sub Message_Box_A()
    Message_Box "Sheet1", "A1", 1
End Sub

Sub Message_Box_B()
    Message_Box "Sheet2", "A1", 1
End Sub

Sub Message_Box_C()
    Message_Box "Sheet1", "A2", 2
End Sub

CMArg · Answer 4 · 2017-03-17T14:36:04.240

I'm just guessing, but maybe following code might help you (in particular if you have too many Message_Box_X () Subs).

Private Sub CommandButton1_Click()
    WhichButton = "Sheet1"
    Message_Box (WhichButton)
End Sub

Private Sub CommandButton2_Click()
    WhichButton = "Sheet2"
    Message_Box (WhichButton)
End Sub



Sub Message_Box(WB As String)
    msgContinue = "Do you want to continue?"
    Answer = MsgBox(msgContinue, vbYesNo)
    If Answer = vbYes Then
        Sheets(WB).Range("A1").Value = 1
    Else
    End If
End Sub

Global message for different message boxes

4 Answers4