Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    VBA to produce a Calculated Comment (XL03)

    I would appreciate help with the vba that would produce a "calculated comment", if this is even possible. I've attached a simplified version of a project where the end result is demonstrated...

    In a nutshell, Sales figures are listed in the C column, while hours allowed is listed in the D column. I need a comment in the D column that shows the SPMH (Sales Per man Hour). The formula would be C/D. The result within the comment would need to be formatted as $x.xx. Further, the comment would need to be dynamic (change itself if the underlying amounts were to change).

    <img src=/S/please.gif border=0 alt=please width=31 height=23>
    Attached Files Attached Files
    - Ricky

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to produce a Calculated Comment (XL03)

    This would be easy to do if you wanted the comment in a cell rather than in a comment. If you want it in a cell, then you could use a formula like:

    ="SPMH " & TEXT(C1/D1,"$###,##0.00")

    If you must have it in a comment, then you will need to use VBA code in the Worksheet_Change event routine.
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to produce a Calculated Comment (XL03)

    Thanks Legare - Yeah, I needed it in a comment
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

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

    Re: VBA to produce a Calculated Comment (XL03)

    See Legare's reply. If you really want comments, right-click the worksheet tab and select View Code from the popup menu. Copy/paste the following code into the worksheet module:
    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Const lngMin As Long = 7
    Const lngMax As Long = 100
    Dim lngRow As Long

    Application.EnableEvents = False

    For lngRow = lngMin To lngMax
    If Not Intersect(Range("C" & lngRow & ":E" & lngRow), Target) Is Nothing Then
    On Error Resume Next
    Range("D" & lngRow).Comment.Delete
    Range("E" & lngRow).Comment.Delete
    On Error GoTo ErrHandler
    If Not Range("C" & lngRow) = 0 Then
    If Not Range("D" & lngRow) = 0 Then
    Range("D" & lngRow).AddComment _
    Text:="SPMH " & Format(Range("C" & lngRow) / Range("D" & lngRow), "Currency")
    End If
    If Not Range("E" & lngRow) = 0 Then
    Range("E" & lngRow).AddComment _
    Text:="SPMH " & Format(Range("C" & lngRow) / Range("E" & lngRow), "Currency")
    End If
    End If
    End If
    Next lngRow

    ExitHandler:
    Application.EnableEvents = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    Change the constants 7 (first row used) and 100 (last row used) as needed.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to produce a Calculated Comment (XL03)

    Hans, Thanks for the code.

    Since this code is being placed "on the sheet" rather than a module, is there a quick way to make the code useful for many sheets in the workbook. Or, would I need to copy/paste for each workbook while in the VBE?
    - Ricky

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

    Re: VBA to produce a Calculated Comment (XL03)

    Do you want to apply the exact same code to *all* worksheets in the workbook or only to some of them?

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to produce a Calculated Comment (XL03)

    Yes - Every sheet in the workbook.
    - Ricky

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

    Re: VBA to produce a Calculated Comment (XL03)

    In that case, remove the code from the worksheet module(s), and put the following code in the ThisWorkbook module:
    <code>
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Const lngMin As Long = 7
    Const lngMax As Long = 100
    Dim lngRow As Long

    Application.EnableEvents = False

    For lngRow = lngMin To lngMax
    If Not Intersect(Sh.Range("C" & lngRow & ":E" & lngRow), Target) Is Nothing Then
    On Error Resume Next
    Sh.Range("D" & lngRow).Comment.Delete
    Sh.Range("E" & lngRow).Comment.Delete
    On Error GoTo ErrHandler
    If Not Sh.Range("C" & lngRow) = 0 Then
    If Not Sh.Range("D" & lngRow) = 0 Then
    Sh.Range("D" & lngRow).AddComment Text:="SPMH " & _
    Format(Sh.Range("C" & lngRow) / Sh.Range("D" & lngRow), "Currency")
    End If
    If Not Sh.Range("E" & lngRow) = 0 Then
    Sh.Range("E" & lngRow).AddComment Text:="SPMH " & _
    Format(Sh.Range("C" & lngRow) / Sh.Range("E" & lngRow), "Currency")
    End If
    End If
    End If
    Next lngRow

    ExitHandler:
    Application.EnableEvents = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    Again, change the constants 7 and 100 as needed. Note that the same values will be used for all sheets (and the same columns as well).

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thank you very much.

    Thanks. I'm going to install the code tomorrow. Need to make a few column changes in the code as my "real" workbook is not exactly like the example I posted. Shouldn't be a problem... I'll post again to let you know how it worked out (or if I flubbed it up) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    - Ricky

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to produce a Calculated Comment (XL03)

    In the line of code:
    <pre><font color=red> If Not Intersect(Sh.Range("C" & lngRow & ":E" & lngRow), Target) Is Nothing Then </font color=red> </pre>


    What is the significance of " <font color=red> :E </font color=red> "

    Am I correct that;
    <pre>For lngRow = lngMin To lngMax
    If Not Intersect(Sh.Range("C" & lngRow & ":E" & lngRow), Target) Is Nothing Then
    On Error Resume Next
    Sh.Range("D" & lngRow).Comment.Delete
    Sh.Range("E" & lngRow).Comment.Delete</pre>

    looks to verify that there's something in the "C" column and if not, deletes the comments in the corresponding D& E columns?
    - Ricky

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

    Re: VBA to produce a Calculated Comment (XL03)

    For example, if lngRow = 12, "C" & lngRow & ":E" & lngRow evaluates to C12:E12, i.e. the cells C12, D12 and E12.
    In other words, the code checks if any of the cells C12, D12 or E12 has changed. If so, the comments for both D12 and E12 are deleted, since they may not be valid any more.
    After that, the code determines which of the comments (if any) has to be created anew.

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

    Re: VBA to produce a Calculated Comment (XL03)

    > The calculations are derived as follows: E/K, L/K, N/H, and O/H with the comments being placed in E, L, N, and O respectively.

    If I look at the sample worksheet, I would guess it should be

    The calculations are derived as follows: E/K, E/L, H/N, and H/O with the comments being placed in K, L, N, and O respectively.

    Is that correct? If not, I don't understand your sample worksheet.

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to produce a Calculated Comment (XL03)

    You are correct. My mind is frazzled! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    - Ricky

  14. #14
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to produce a Calculated Comment (XL03)

    <P ID="edit" class=small>(Edited by Tricky on 28-Jan-08 19:16. Made corrections to the text: "The calculations are derived... " (Thanks Hans))</P>That makes sense and I appreciate the explanation.

    When I first asked for help on this project, I posted an over-simplified example. I thought if I received help with the example, I could work my own way through the "real" project. I thought wrong! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> And, I need some additional help (in more ways than I care to admit!)

    In the real workbook, four comments are needed (not two).
    The calculations are derived as follows: E/K, E/L, H/N, and H/O with the comments being placed in K, L, N, and O respectively.

    The code is for an entire workbook (every worksheet is set up identical (different dates and different numbers, but the formatting and locations are all the same) I've attached a page from the workbook filled with some dummy data so this time, it's a true representation of what I'm working with. If it's not too much to ask, I sure could use an additional push... <img src=/S/please.gif border=0 alt=please width=31 height=23>
    Attached Files Attached Files
    - Ricky

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

    Re: VBA to produce a Calculated Comment (XL03)

    Try the code from the attached text file. It should go into the ThisWorkbook module. If you wish, you can place the HandleComment procedure in a standard module.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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