CODEWITHSUNDEEP
vbaexcel
Michi
Michi

Reputation: 5471

Global message for different message boxes

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?

Upvotes: 1

Views: 138

Answers (4)

CMArg
CMArg

Reputation: 1567

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

Upvotes: 0

Fadi
Fadi

Reputation: 3322

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

Upvotes: 0

Winterknell
Winterknell

Reputation: 585

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

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 1

Related Questions