CODEWITHSUNDEEP
vbaexcel
slomo
slomo

Reputation: 117

Macro in Opening a file If you were in a meeting, click "OK", if not, to mention again in excel

I want to make a notification of appointments from a table. If it was done click Okay, and write in the table in the column B. If not, redo the next time the file is developed.

That's what I know, I do not know how to place the Okay column B in the corresponding row, and make it reappear.

Private Sub Workbook_Open()
    For Each cell In Sheets("appointments").Range("A1:B500")
        '''If cell.value = "yes" Then '''By column B
        If cell.value = Date Then
            MsgBox "appointments" & cell.Offset(0, 4).value, vbExclamation + vbYesNo + vbQuestion, "appointments"
        End If
    Next cell
End Sub

I would appreciate any help, idea, concepts.

Edit: The correct answer was found last

Upvotes: 1

Views: 66

Answers (3)

slomo
slomo

Reputation: 117

With help from friends before, I was able to do the following code Column 1 this "date", column 2 this "meeting", column 3 this "status"

The first condition, if the status on the "No" The second condition, if the date on today's date

If you click Yes, you enter "Yes" If you click No, enter a status of "No"

Private Sub Workbook_Open()
    For Each cell In Sheets("Appointment").Range("A1:E500")
       If cell.Cells.Offset(0, 3) = "No" Then
            If cell.value = Date Then
                If MsgBox("Did you go to the meeting on " & Date & cell.Cells.Offset(0, 2), Buttons:=vbYesNo) = vbYes Then
                     cell.Cells.Offset(0, 3) = "Yes"
                     Else
                     cell.Cells.Offset(0, 3) = "No"
                 End If
             End If
        End If
    Next cell
End Sub

If you want to find old dates, use this <= Date

Upvotes: 0

urdearboy
urdearboy

Reputation: 14580

Here are a couple thoughts

  1. Do not use cell as a variable. It is too close to a pre-defined object (Cells). Instead, try MyCell (or anything else). You also need to declare this variable which leads me to #2
  2. Option Explicit. Always use this at header
  3. Set a dynamic range to loop through. I'm guessing you just want to loop through Column A from the 2nd row (header?) down to the last used row
  4. You will need to set your MsgBox input as a variable and then build your If Then statement around the result
  5. Consider using vbYesNoCancel. What if you want to open the book to check something real fast without answering a series of unavoidable pop ups? It is not necessary, but you could give the option to Cancel and Exit Sub

I took out the text in your MsgBox for readability here. Maybe you should change the prompt to something like Have you completed [this] appointment? which will yield the options of Yes, No, or Cancel. 

Option Explicit

Private Sub Workbook_Open()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Appointment")
Dim MyCell As Range, LRow As Long, Ans As String

LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

For Each MyCell In ws.Range("A2:A" & LRow) '<=== Dynamic Range
    If MyCell = Date Then
    Ans = MsgBox("Select One", vbYesNoCancel)
        If Ans = vbYes Then
            'Do something if YES
        ElseIf Ans = vbNo Then
            'Do something if NO
        ElseIf Ans = vbCancel Then
            'Do something if CANCEL (Exit Sub?)
        End If
    End If
Next MyCell

End Sub

Upvotes: 3

oxwilder
oxwilder

Reputation: 757

It seems like you're almost there. If I'm interpreting your question correctly, you want to ask a yes/no question and have a value placed in this row a few columns over.

You can achieve this with a nested if statement:

Private Sub Workbook_Open()
    For Each cell In Sheets("appointments").Range("A1:B500")
        '''If cell.value = "yes" Then '''By column B
        If cell.Value = Date Then
            If MsgBox("Did you go to the meeting on " & Date & "?", Buttons:=vbYesNo) = vbYes Then
            cell.Cells.Offset(0, 4) = "Yeah I went."
            End If
        End If
    Next cell
End Sub

I made a few assumptions about columns, but you can adjust the ranges and column numbers in the offset to suit you.

Upvotes: 2

Related Questions