Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Message to alert user to insert comment (2003)

    Edited by HansV to reduce picture in size - please don't post pictures larger than 640 x 480 pixels.

    I would like to produce a formula so that the user has to insert a comment (in comments field) if the date field is 5 months from todays date.
    See screenshot for details
    Attached Images Attached Images
    • File Type: jpg y.jpg (16.9 KB, 0 views)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Message to alert user to insert comment (2003)

    When should this be triggered: when the user's enters a date in the date column, when the workbook is opened (check for all the dates), both times, or something else?

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message to alert user to insert comment (2003)

    When the work book is opened all worksheets should be checked and a comment would be required by the user

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Message to alert user to insert comment (2003)

    What column or columns should be checked for dates? Which column has the comments?

    Is the same column to be checked for each sheet? Which Column?
    If they are not all the same, will the columns be labeled the same (the same item is in row 1)? What is it?
    If they are not labeled the same, could you attach a list of worksheet names and columns to check?
    [Or is there some other way in each worksheet to tell which columns to check?

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message to alert user to insert comment (2003)

    See attachment for answers to your questions.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Message to alert user to insert comment (2003)

    So every worksheet in the workbook will have dates entered in Col C. Required comments will be in I.

    Do you want to check vs "Today's date" (the date the macro is run) or do you want to use the date in Col J? What if there is no date in Col J? Should the macro check for a date in J and if not, enter the formula in it?

    Steve

  7. #7
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message to alert user to insert comment (2003)

    So every worksheet in the workbook will have dates entered in Col C (yes) .

    Required comments will be in I (yes)

    Do you want to check vs "Today's date" (the date the macro is run) or do you want to use the date in Col J? What if there is no date in Col J? Should the macro check for a date in J and if not, enter the formula in it?

    I need to check for a date in column J which is 5 months from the date in column c

    Yes, the macro should check for a date in column J (this will be todays date)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Message to alert user to insert comment (2003)

    How about this?
    Add it to the thisWorkbook Object. It will check ALL WORKSHEETS (per your request) even the hidden ones. The message box displays the description so the user knows what he is supposed to comment on [You could add other info if desired} I did not use the cell in col J, I just used the Computer date (most of the sheets in your workbook do not have the date in Col J ,so I didn't want to depend on it being there).

    Steve

    <pre>Option Explicit
    Private Sub Workbook_Open()
    Dim wks As Worksheet
    Dim lRow As Long
    Dim lRows As Long
    Dim iColDate As Integer
    Dim iColDes As Integer
    Dim iColCmt As Integer
    Dim sComment As String
    Dim rCell As Range

    iColDate = 3 'Col C
    iColDes = 4 'Col D
    iColCmt = 9 'Col I

    For Each wks In ThisWorkbook.Worksheets
    With wks
    lRows = .Cells(.Cells.Rows.Count, iColDate).End(xlUp).Row
    For lRow = 2 To lRows
    Set rCell = .Cells(lRow, iColDate)
    If IsNumeric(rCell) And rCell > 0 Then
    If DateDiff("m", .Cells(lRow, iColDate), Date) > 5 Then
    sComment = ""
    Do While sComment = ""
    sComment = Application.InputBox _
    (prompt:="Enter Mandatory Comment for " & vbCrLf & _
    "[" & .Cells(lRow, iColDes).Value & "]", _
    Title:="Comment is Mandatory", _
    Type:=2)
    Loop
    .Cells(lRow, iColCmt) = sComment
    End If
    End If
    Next
    End With
    Next
    Set rCell = Nothing
    Set wks = Nothing
    End Sub</pre>


  9. #9
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message to alert user to insert comment (2003)

    Thatís great thanks

Posting Permissions

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