Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Structure long daily chart of meds and test result (Excel Office 2003)

    Hans:
    THe holiday week off for Xeloda does not show correctly shows 0 instead of 1 week. And shows 4 weeks of taking instead of 3 weeks of treatment.
    Had been working fine not certain why now, except added more treatments during last/ current week
    Jerome

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

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    I'm sorry about that, I hadn't tested thoroughly enough. Change the loop

    Do
    lngRow = lngRow + 1
    If wsh.Range("A" & lngRow) <= Date Then
    dblW1 = dblW1 + Val(wsh.Range("D" & lngRow)) + _
    Val(wsh.Range("E" & lngRow)) + Val(wsh.Range("F" & lngRow))
    dblW2 = dblW2 + Val(wsh.Range("J" & lngRow))
    dblW3 = dblW3 + Val(wsh.Range("I" & lngRow))
    dblW4 = dblW4 + Val(wsh.Range("H" & lngRow))
    End If
    Loop Until wsh.Range("A" & lngRow) = "" Or _
    wsh.Range("A" & lngRow) >= datDate + 7

    to

    Do
    If wsh.Range("A" & lngRow) <= Date Then
    dblW1 = dblW1 + Val(wsh.Range("D" & lngRow)) + _
    Val(wsh.Range("E" & lngRow)) + Val(wsh.Range("F" & lngRow))
    dblW2 = dblW2 + Val(wsh.Range("J" & lngRow))
    dblW3 = dblW3 + Val(wsh.Range("I" & lngRow))
    dblW4 = dblW4 + Val(wsh.Range("H" & lngRow))
    End If
    lngRow = lngRow + 1
    Loop Until wsh.Range("A" & lngRow) = "" Or _
    wsh.Range("A" & lngRow) >= datDate + 7

    The code is the same, but the line that increases the row number has been moved down.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    Hans
    Got it works fine, thanks.
    I forgot how to add a macro to the main toolbar after the help menu. I would assign the divide weeks macro to a new toolbar but don't have the desktop toolbar space and have a lot of available toolbar space after the main help menu.
    Thanks again for all
    Jerome

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

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    The menu bar is a toolbar, so you can use the same method to add an item (button) to the menu bar you would use to add a button to another toolbar:
    - Select Tools | Customize...
    - Activate the Commands tab.
    - Select Macros in the list of Categories on the left hand side.
    - Drag either Custom Menu Item or Custom Button to the menu bar and drop it there.
    - Right-click the new item.
    - Select Assign Macro from the popup menu.
    - You can further customize the new item by right-clicking it and selecting items from the popup menu.
    - Close the Customize dialog when you're done.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    Hans
    Many many many thanks, Have a great weekend
    Jerome

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    Hans
    Thanks again. Is it pssible to add the shortcut button to the main toolbar but it only shows up with this spreadsheet and only when it is opened??
    Thanks
    Jerome

  7. #7
    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: Structure long daily chart of meds and test result (Excel Office 2003)

    You can create the custom toolbar programmatically.

    Jan Karel <!profile=Pieterse>Pieterse<!/profile> has an article on addins with a chapter on creating Toolbars in this manner.

    In addition to the workbook open and close events the could be run from the workbook window activate/deactivate event to also add/remove it when the workbook is not active...

    Steve

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

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    You can add code to various workbook events in the ThisWorkbook module to create, show, hide and delete the button as appropriate.

    Don't know what you mean by "only when it is opened".

    See attached workbook. Note: you'll probably have to delete the button you already have.

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    Hans Thanks works great, what i meant "only when it is opened" is that the button only shows when the workbook is open and is removed when the workbook closes just as you have it working.
    How does one specifiy a button image to put with the name divide weeks as in photo attached.
    Thanks
    Jerome

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

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    If you want an icon and a caption, set the style to msoButtonIconAndCaption; if you want only an icon, set the style to msoButtonIcon.
    You can then assign a value to the FaceId property, for example:

    With cbb
    .Caption = "Divide Weeks"
    .Style = msoButtonIconAndCaption
    .FaceId = 481
    .OnAction = "DivideWeeks"
    .Visible = True
    End With

    You'll have to experiment yourself which FaceId you like.

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    Hans and Steve, but esp Hans
    Many many thanks I wish the rest of life had such phenomenal and stress free resolutions to all my questions and needs as you provide here.
    You provide a phenomenal service
    Thank you thank you
    Jerome

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    Hans:
    On using the macro, I find that I need to know the exact days in addition to weeks. Weeks is perfect as it is easier to discuss with patients. But it is important to know that it is 23 days and really 3 weeks 2 days not 4 weeks quite yet So is it possible to add the exact days next to weeks as per graphic. or say 3 weeks 1 day vs 4 week, whichever is easiest??
    I looked at the macro and was not certain how to add the math
    Thanks
    Jerome

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

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    See attached version.

  14. #14
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    Hans
    Many many thanks helps tremendously
    Jerome

  15. #15
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Excel Office 2003)

    Is there a way, or is it asking too much, to get the sequence of treatment and holiday weeks off the formatted or data sheet, and put it on the extra sheet
    e.g.
    Xeloda 2 Tx weeks, 1 holdiay week ,3 treatment weeks , 2 holiday weeks etc??
    Jerome

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
  •