Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add comment (excel200)

    Greetings,

    The goal is to walk/run either 3 miles per day or 40 miles bi-weekly (20 miles per week).
    I’ve attached the sample worksheet I created and would like to modify it as follows:

    1.When I enter the # of miles each day and it is <3 miles, let’s say 2 miles, then I would like to display a text saying “…almost you’re short by a mile to meet your goal..”

    2. The next day if # of miles = 3, then I would like a message saying “great, you meet your goal today…”
    3. If # of mile run >3, let’s say 5 miles, then the txt message would read “ excellent, you’ve exceeded your daily goal by 2 miles)

    The same message for bi-weekly (bi-weekly #1 a mile short, and bi-weekly #2 exceeded the 40 miles goal by 5 miles)

    Thank you in advance,
    OCM
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: add comment (excel200)

    Displaying a message or a comment each time I enter a value would make me rather nervous, and it doesn't work for the bi-weekly values because those are calculated, not entered.
    Moreover, it would require macro code.

    The attached version demonstrates two alternatives that don't use VBA:

    - Formulas to display a text in the cell to the right of the number of miles.
    - Conditional formatting to color the cells with the number of miles.
    Attached Files Attached Files

  3. #3
    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: add comment (excel200)

    You could add something like this to the worksheet object in code.
    Any time to change a cell in Col B, E, H etc it will popup a message and then check the value in row 13 and popup a message about how the biweekly number is going.

    Steve

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim dValue As Double
    Dim rCell As Range
    For Each rCell In Target
    If (rCell.Column - 2) Mod 3 = 0 Then
    dValue = rCell.Value
    Select Case dValue
    Case Is < 3
    MsgBox "...almost you’re short by " & _
    3 - dValue & " mile(s) to meet your goal..."
    Case Is = 3
    MsgBox "great, you met your goal today..."
    Case Is > 3
    MsgBox "excellent, you’ve exceeded your daily goal by " & _
    dValue - 3 & " mile(s)"
    End Select
    dValue = Me.Cells(13, rCell.Column) + _
    Me.Cells(13, rCell.Column + 3)
    Select Case dValue
    Case Is < 40
    MsgBox "...almost you’re short by " & _
    40 - dValue & " mile(s) to meet your biweekly goal..."
    Case Is = 40
    MsgBox "great, you met your biweekly goal today..."
    Case Is > 40
    MsgBox "excellent, you’ve exceeded your biweekly goal by " & _
    dValue - 40 & " mile(s)"
    End Select
    End If
    Next
    Set rCell = Nothing
    End Sub</pre>


  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add comment (excel200)

    Steve,
    Thank you for your reply post. Just want to make sure the following:
    1. I just need to copy & paste your code into the worksheet (right click worksheet tab & view code & past it there, or alt + F11 & paste it there correct?
    2. how do i hide this code so that no one can modify it?

    Thanks,
    OCM

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add comment (excel200)

    Hans,
    Thank you very much it works beautifuly!

    OCM

  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: add comment (excel200)

    Yes the view code in the sheet gets you there directly.

    While in VB you can protect the project:
    Right-click the sheet object in "Project Explorer" pane
    Select "VBAProject Properties..."
    Select "Protection" tab
    Check the box: "Lock project for viewing"
    Enter and confirm a password in the boxes.
    [OK] to confirm

    [Note: remember this password or you will not be able to access the code later]

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add comment (excel200)

    Thanks Steve. The program is working fine as I would like it to work.


    OCM

Posting Permissions

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