Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Add to msg box in excel vba

This is a discussion on Add to msg box in excel vba within the Excel Questions forums, part of the Question Forums category; ...

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    189

    Default Add to msg box in excel vba

    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
    ISSUE DUE DATE REMINDER STATUS
    TEST 06/19/2017 ON

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    753

    Default Re: Add to msg box in excel vba

    .
    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

  3. #3
    Board Regular
    Join Date
    Mar 2016
    Posts
    189

    Default Re: Add to msg box in excel vba

    Quote Originally Posted by Logit View Post
    .
    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

  4. #4
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    753

    Default Re: Add to msg box in excel vba

    .
    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...tem-MessageBox
    Last edited by Logit; Jun 19th, 2017 at 05:02 PM.

  5. #5
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    753

    Default Re: Add to msg box in excel vba

    .
    Well, I'm glad you inquired about changing the button text. Here is a strong approach just located :

    VBA Msgbox Custom Button Text -

    With download.

  6. #6
    Board Regular
    Join Date
    Mar 2016
    Posts
    189

    Default Re: Add to msg box in excel vba

    Quote Originally Posted by Logit View Post
    .
    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...tem-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 .

  7. #7
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    753

    Default Re: Add to msg box in excel vba

    .
    I was confused about that as well until I added this to the worksheet:

    ISSUE DUE DATE REMINDER STATUS
    TEST 06/19/2017 ON

  8. #8
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    753

    Default Re: Add to msg box in excel vba

    .
    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 by Logit; Jun 19th, 2017 at 05:39 PM.

  9. #9
    Board Regular
    Join Date
    Mar 2016
    Posts
    189

    Default Re: Add to msg box in excel vba

    Ok so that seems to work but how can i add to my msg box the whole msg like the due date issue etc ...

  10. #10
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    753

    Default Re: Add to msg box in excel vba

    .
    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com