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

[jrklein]

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

[HansV]

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.

[jrklein]

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

[HansV]

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.

[jrklein]

Hans
Many many many thanks, Have a great weekend
Jerome

[jrklein]

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

[sdckapr]

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

[HansV]

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.

[jrklein]

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

[HansV]

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.

[jrklein]

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

[jrklein]

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

[HansV]

See attached version.

[jrklein]

Hans
Many many thanks helps tremendously
Jerome

[jrklein]

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