I have pop-up reminder that show row by row the due date within 15 days once workbook is opened. The pop-up reminder msgbox have "OK" button only and show Member ID and the Due Date.
There is a lots of column which contain data in the row and I wish to make more effective by using userform, so I wish this procedur can be perform in userform (that I already created) which the Member ID in textbox1, Due Date in textbox2 and few details in another textbox. The userform have 2 command button for 'Next' (goes to next reminder) & 'Cancel' (to exit form), how this can be done?
Below is a code that run pop-up reminder:
Private Sub Workbook_Open()
Dim Membership As String
Dim RowNrNumeric As Integer
Dim RowNrString As String
Dim CloumnNameMembership As String
Dim CloumnNameDate As String
Dim CloumnNameStatus As String
Dim DueDate As Date
Dim Status As String
Dim TextDay As String
Dim TextMonth As String
Dim TextYear As String
CloumnNameMembership = "F"
CloumnNameDate = "L"
CloumnNameStatus = "M"
RowNrNumeric = 2
RowNrString = RowNrNumeric
Membership = Range(CloumnNameMembership + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
Status = Range(CloumnNameStatus + RowNrString).Value
Do While Membership <> ""
Range(CloumnNameDate + RowNrString).Interior.ColorIndex = 6
If (Status = "ON" And DateDiff("d", DueDate, Date) >= 10) Then
TextDay = Day(DueDate)
TextMonth = Month(DueDate)
TextYear = Year(DueDate)
MsgBox "Membership: " + Membership + " DUE DATE is : " + TextMonth + "-" + TextDay + "-" + TextYear
Range(CloumnNameDate + RowNrString).Interior.ColorIndex = 8
End If
RowNrNumeric = RowNrNumeric + 1
RowNrString = RowNrNumeric
Membership = Range(CloumnNameMembership + RowNrString).Value
DueDate = Range(CloumnNameDate + RowNrString).Value
Status = Range(CloumnNameStatus + RowNrString).Value
Loop
End Sub
I really hope someone can help me. I've been looking for this solution in websites more than a month based on related title and tried the code but the result is not what I want. Thank you in advance.