Add to msg box in excel vba

BORUCH

Well-known Member
Joined
Mar 1, 2016
Messages
528
Office Version
  1. 365
Platform
  1. Windows
Private Sub Workbook_Open()
Dim Issue As String
Dim RowNrNumeric As Integer
Dim RowNrString As String
Dim CloumnNameIssue As String
Dim CloumnNameDate As String
Dim CloumnNameRemStatus As String
Dim DueDate As Date
Dim RemStatus As String
Dim TextDay As String
Dim TextMonth As String
Dim TextYear As String


CloumnNameIssue = "A"
CloumnNameDate = "B"
CloumnNameRemStatus = "C"


RowNrNumeric = 2
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value


Do While Issue <> ""

If (RemStatus = "ON" And DateDiff("d", DueDate, Date) >= 0) Then
TextDay = Day(DueDate)
TextMonth = Month(DueDate)
TextYear = Year(DueDate)
MsgBox "Remider:" + " " + Issue + " DUE DATE is : " + TextMonth + "/" + TextDay + "/" + TextYear, vbYesNo, "REMINDER LIST"
Range(CloumnNameDate + RowNrString).Interior.ColorIndex = 3
End If




RowNrNumeric = RowNrNumeric + 1
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value
Loop


End Sub

HI

I found this code online which gives you reminders when you open excel what I would like is to have a button on the msg box "dismiss" or "keep"

if I press “dismiss” it changes that particular Reminder to off, it I press “keep” it keeps it to on
The default is on when I create the reminder on my reminder list sheet

MY REMINDER LIST SHEET LOOKS LIKE THIS
ISSUEDUE DATEREMINDER STATUS
TEST06/19/2017ON

<tbody>
</tbody>
 

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.
.
Code:
Option Explicit


Sub Test3()
Dim answer As Variant
  answer = MsgBox("Yes = run Test1; No = run Test2", vbYesNo + vbQuestion, "Choose macro to run")
  If answer = vbYes Then
      Call Test1
   Else
     Call Test2
End If
End Sub


Sub Test1()
  'Your macro here
End Sub


Sub Test2()
  'Your macro here
End Sub
 
Upvote 0
.
Code:
Option Explicit


Sub Test3()
Dim answer As Variant
  answer = MsgBox("Yes = run Test1; No = run Test2", vbYesNo + vbQuestion, "Choose macro to run")
  If answer = vbYes Then
      Call Test1
   Else
     Call Test2
End If
End Sub




Sub Test1()
  'Your macro here
End Sub


Sub Test2()
  'Your macro here
End Sub


where do i add this code to the code i posted ?

the code i posted is on workbook open
 
Upvote 0
.
Hopefully this will do what you wanted :

Code:
Option Explicit

Private Sub Workbook_Open()
Dim Issue As String
Dim RowNrNumeric As Integer
Dim RowNrString As String
Dim CloumnNameIssue As String
Dim CloumnNameDate As String
Dim CloumnNameRemStatus As String
Dim DueDate As Date
Dim RemStatus As String
Dim TextDay As String
Dim TextMonth As String
Dim TextYear As String

CloumnNameIssue = "A"
CloumnNameDate = "B"
CloumnNameRemStatus = "C"

RowNrNumeric = 2
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value

Do While Issue <> ""

Dim answer As Variant
  answer = MsgBox("Yes = KEEP  " & "  |  " & "  No = DISMISS", vbYesNo + vbQuestion, "Choose YES / NO")
    If answer = vbYes Then
        Range(CloumnNameDate + RowNrString).Interior.ColorIndex = 3
    Else
        Exit Sub
End If

RowNrNumeric = RowNrNumeric + 1
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value
Loop

End Sub

I am not aware of a means to change the wording on buttons in a Message Box. If that is truly required, it would have to be accomplished by creating a UserForm that looks like a Message Box.

ps: Did a quick search and found this if interested : https://www.codeproject.com/Articles/18399/Localizing-System-MessageBox
 
Last edited:
Upvote 0
.
Hopefully this will do what you wanted :

Code:
Option Explicit

Private Sub Workbook_Open()
Dim Issue As String
Dim RowNrNumeric As Integer
Dim RowNrString As String
Dim CloumnNameIssue As String
Dim CloumnNameDate As String
Dim CloumnNameRemStatus As String
Dim DueDate As Date
Dim RemStatus As String
Dim TextDay As String
Dim TextMonth As String
Dim TextYear As String

CloumnNameIssue = "A"
CloumnNameDate = "B"
CloumnNameRemStatus = "C"

RowNrNumeric = 2
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value

Do While Issue <> ""

Dim answer As Variant
  answer = MsgBox("Yes = KEEP  " & "  |  " & "  No = DISMISS", vbYesNo + vbQuestion, "Choose YES / NO")
    If answer = vbYes Then
        Range(CloumnNameDate + RowNrString).Interior.ColorIndex = 3
    Else
        Exit Sub
End If

RowNrNumeric = RowNrNumeric + 1
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value
Loop

End Sub

I am not aware of a means to change the wording on buttons in a Message Box. If that is truly required, it would have to be accomplished by creating a UserForm that looks like a Message Box.

ps: Did a quick search and found this if interested : https://www.codeproject.com/Articles/18399/Localizing-System-MessageBox

I think the code is missing the if statement to check if its due or not, can you add it nothing is working now .
 
Upvote 0
.
I was confused about that as well until I added this to the worksheet:

ISSUEDUE DATEREMINDER STATUS
TEST06/19/2017ON

<tbody>
</tbody>
 
Upvote 0
.
I added back some of your code from the original you posted. Was this what you meant :

Code:
Option Explicit

Private Sub Workbook_Open()
Dim Issue As String
Dim RowNrNumeric As Integer
Dim RowNrString As String
Dim CloumnNameIssue As String
Dim CloumnNameDate As String
Dim CloumnNameRemStatus As String
Dim DueDate As Date
Dim RemStatus As String
Dim TextDay As String
Dim TextMonth As String
Dim TextYear As String

CloumnNameIssue = "A"
CloumnNameDate = "B"
CloumnNameRemStatus = "C"

RowNrNumeric = 2
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value

Do While Issue <> ""
If (RemStatus = "ON" And DateDiff("d", DueDate, Date) >= 0) Then
TextDay = Day(DueDate)
TextMonth = Month(DueDate)
TextYear = Year(DueDate)
Dim answer As Variant
  answer = MsgBox("Yes = KEEP  " & "  |  " & "  No = DISMISS", vbYesNo + vbQuestion, "Choose YES / NO")
    If answer = vbYes Then
        Range(CloumnNameDate + RowNrString).Interior.ColorIndex = 3
    Else
        Exit Sub
    End If
End If

RowNrNumeric = RowNrNumeric + 1
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value
Loop

End Sub
 
Last edited:
Upvote 0
Ok so that seems to work but how can i add to my msg box the whole msg like the due date issue etc ...
 
Upvote 0
.
How about this ?

Code:
Option Explicit


Private Sub Workbook_Open()
Dim Issue As String
Dim RowNrNumeric As Integer
Dim RowNrString As String
Dim CloumnNameIssue As String
Dim CloumnNameDate As String
Dim CloumnNameRemStatus As String
Dim DueDate As Date
Dim RemStatus As String
Dim TextDay As String
Dim TextMonth As String
Dim TextYear As String


CloumnNameIssue = "A"
CloumnNameDate = "B"
CloumnNameRemStatus = "C"


RowNrNumeric = 2
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value


Do While Issue <> ""
    If (RemStatus = "ON" And DateDiff("d", DueDate, Date) >= 0) Then
        TextDay = Day(DueDate)
        TextMonth = Month(DueDate)
        TextYear = Year(DueDate)
        
        Dim answer As Variant
        Dim Msg As String, Title As String
        Dim Config As Integer, Ans As Integer
        
          Msg = "Reminder:" & " " & Issue & " DUE DATE is : " & TextMonth & "/" & TextDay & "/" & TextYear
          Msg = Msg & vbNewLine & vbNewLine
          Msg = Msg & "Yes = KEEP  " & "  |  " & "  No = DISMISS"
          Title = "Choose YES / NO"
          Config = vbYesNo + vbQuestion
          answer = MsgBox(Msg, Config, Title)
          
            If answer = vbYes Then
                Range(CloumnNameDate + RowNrString).Interior.ColorIndex = 3
            Else
                Exit Sub
            End If
    End If


RowNrNumeric = RowNrNumeric + 1
RowNrString = RowNrNumeric
Issue = Range(CloumnNameIssue + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
RemStatus = Range(CloumnNameRemStatus + RowNrString).Value
Loop


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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