Log the results

[joeny0706]

Hi All

My daughter has recently been diagnosed with diabetes. It is not an easy condition to maintain. There is alot of work involved and information I need to record and save.

I have an excel worksheet that calculates how much insulin I need to give her. It works good but I also need to keep a log of her daily test results and also how much insulin I give her. Currently I transfer the results into a different file. Sometimes I forget and would be great to have the logging part automated.

I have attached the excel file I use to calculate how much medicine I need to give her. I was thinking of finding a way to log all the information automatically. The attached file just has the formulas inserted into cells that are locked. I enter the test results into the unlocked cells and it cellulates how much to give her.

I enter her current level and fill in the amount of carbs she eats and it calculates how much insulin I need to give her. I need it to record the contents in the level cell and also contents the total cell. Having the date and time when it was done also listed would be perfect.

Can anyone help me so I can find a way to have the results saved to a different worksheet without me having to manual transfer it. So, if maybe having a button to click submit after I fill in all the required information and hit enter it could transfer the data to another worksheet or log workbook. This could show the level I entered, the amount of carbs she ate and also the results for how much insulin I need to give her "Contents in the total cell".

Sometimes the carbs cell will not need informartion to be entered but the level and total will alwyas

Or if anyone has any good ideas of a good way to log all the info into a different worksheet or workbook.

Thanks All Much.

[Logit]

.
Can you work with a different layout ?

	CARBS	RATIO	BOLUS	LEVEL	TARGET	AMOUNT CORRECT	FACTOR	CORRECTION	BOLUS	CORRECTION	TOTAL
Breakfast
Lunch
Dinner

[SamT]

Joeny, I got the formulas wrong, but the data logging is working.

[joeny0706]

.
Can you work with a different layout ?

	CARBS	RATIO	BOLUS	LEVEL	TARGET	AMOUNT CORRECT	FACTOR	CORRECTION	BOLUS	CORRECTION	TOTAL
Breakfast
Lunch
Dinner

yes anything that works. That was just a simple excel sheet I created
My wife is the one who mainly does it. So the easier the better.

[Paul_Hossler]

I did something similar

Formats a little different

Lots of choices, or combine the different pieces

Input sheet

Capture.JPG


Log sheet

Capture1.JPG

[joeny0706]

They are both great.


Sometimes We just need one for logging and it wont require a dosage.
I need to add 2 more after dinner

Paul I like the Update button so it does not log when she types mistakes.

I might try to create a new sheet from both with the features I mentioned.

I really appreciate all your help. This is a very important task that just recently entered our lives.

[Paul_Hossler]

It's easy enough to add two more input rows to the Input sheet (AfterDinner_1 and AfterDinner_2)

I originally went with one row / day on the Log sheet, but if that gets too wide with 5 sets of inputs, I'd suggest a format like SamT's with each 'event' being a separate line, thus making it narrower

Let us know if there's anything we can do to help

[joeny0706]

Thanks

i am not sure how to link it to transfer to the log. I will check out you setup and see if I can figure out.

again thanks. This is a very helpful tool for my wife.

[snb]

Another suggestion

[Paul_Hossler]

This might give you some ideas. Look at the other responses to see ideas from others

The Log sheets can be used as an input to a pivot table or a pivot graph

You can add conditional formatting to the macro to shade (for ex) Bolus > 1.5 yellow and Bolus > 3.0 red

Option Explicit


Sub UpdateLog()
    Dim rowDate As Long, rowMeal As Long
    
    Application.ScreenUpdating = False
    Application.Calculate
    
    'delete today if already there
    On Error Resume Next
    rowDate = 0
    rowDate = Application.WorksheetFunction.Match(Int(CDbl(Now)), Log.Columns(1), 0)
    
    Do While rowDate > 0
        Log.Rows(rowDate).Delete
        rowDate = 0
        rowDate = Application.WorksheetFunction.Match(Int(CDbl(Now)), Log.Columns(1), 0)
    Loop
        
    On Error GoTo 0
        
    'move data
    With Log
        rowDate = .Cells(Log.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
        For rowMeal = 3 To 7
            .Cells(rowDate, 1).Offset(rowMeal - 3, 0).Value = Int(Now)
            .Cells(rowDate, 2).Offset(rowMeal - 3, 0).Value = Daily.Cells(rowMeal, 2).Value
            Daily.Cells(rowMeal, 4).Resize(1, 9).Copy
            .Cells(rowDate, 3).Offset(rowMeal - 3, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Next rowMeal
        
        .Columns(10).NumberFormat = "0.00"
        .Columns(11).NumberFormat = "0.00"
    End With


    Application.ScreenUpdating = True


End Sub

[joeny0706]

Hi snb

My wife likes this one. The interface is very friendly. Very nice.
I just have a couple questions.

Can we make it so it auto populates the values when she selects if it is breakfast, lunch, dinner. The only information she enters each time is carbs and level
If you have time can you make it populate the ratio, target and factor with static values. The values are linked to what time of the day it is.

Breakfast: Ratio 20, Target 120, Factor 90
Lunch : Ratio 30, Target 120, Factor 90
Dinner: Ratio 25, Target 120, Factor 90
Evening1,2: Ratio 60, Target 150, Factor 90


Also after you hit save how do you get the windows to appear again. Is it possible to make the box open again after it is saved.

Thanks

[snb]

@joeny

Your new information makes it a lot simpler.
See the attachment.

[joeny0706]

snb




Thanks
She really likes the format.

It does not seem to be calculating the formulas correct. It is giving me the same results if I pick dinner, evening or breakfast etc.
The ratio is different for all 4 and still giving 4.91 for the dosage. I looked but not sure how to fix that.
Also the target does not change for evening. I may not have explained it correctly. Depending on the time of day the values are different.

12/18/20	Breakfast	20	20	240	120	90	2.33
12/18/20	Lunch	75	25	130	120	90	3.11
12/18/20	Dinner	62	30	400	120	90	5.18
12/20/20	Breakfast	50	20	300	120	90	4.00
12/20/20	Dinner	45	25	400	120	90	4.91
12/20/20	Breakfast	45	20	400	120	90	4.91
12/20/20	Breakfast	45	20	400	120	90	4.91
12/20/20	Dinner	45	25	400	120	90	4.91
12/20/20	Evening_2	45	60	400	120	90	4.91
12/20/20	Evening_2	45	60	400	120	90	4.91
12/20/20	Evening_1	45	60	400	120	90	4.91

Breakfast: Ratio 20, Target 120, Factor 90
Lunch : Ratio 30, Target 120, Factor 90
Dinner: Ratio 25, Target 120, Factor 90
Evening1,2: Ratio 60, Target 150, Factor 90

Again Thanks much to everyone for Helping me.

[Paul_Hossler]

BTW, you have 12/20/2020 Breakfast in 3 times and Evening_2 in twice

[joeny0706]

Paul


Thanks Very nice. I am giving my wife many different options and she does like you setup. She has been using your file right now.

I do have a question.
When I open this file on my computer it slows it down alot. Seems like it has other processes running with it. In the task manager it runs 17% cpu on my computer.
I have a HP Zbook with an I9 and 32gb ram. It slows it down so much there is a lag moving between cells and typing within excel.
On hers it uses 30-35% of the cpu.
Once I close the file it all goes back to normal with both laptops.
No other excel file cause anything like that.

Any idea why an excel file would do that?

[joeny0706]

BTW, you have 12/20/2020 Breakfast in 3 times and Evening_2 in twice

With snb file I choose the meal then enter the information then hit save. Doing only one meal at a time.
I even tried to close the file

Those were just for testing. I keep choosing meals to see the results.

[Paul_Hossler]

1. snb's programming style and mine are very different. It's very hard for me to follow the code. I'll look anyways, but snb would probably have more insight

Edit -- the xlsb file from post #12 doesn't cause any lag on my PC; Task manger shows0% to maybe 1% Excel usage

What version of Excel are you using, but that still shouldn't make any difrerence


2. Re: Duplicates -- I was thinking that if a Date+Meal were already in the data, the user friendly thing would be to populate the user form with the existing data, and revise it

[joeny0706]

I am using office pro plus 2016 and the other laptop has pro plus 2019.

I am not sure what could be the cause. Just strange it is happening on both laptops.
I have restarted both.
Also her office I just installed tonight. She had open office and that wont work with macros so hers has a fresh install of 2019.
I agree the version should not matter but what version did you use to create it? Just curious.


I just opened the file and minimized it. 17%

I will do some research see if I figure anything out. She does like your setup. Now I just need to figure out what is causing the CPU usage.


This only happens when I have the dailyInputSheet open. If I have the log sheet open the cpu drops down to 1%.

[Paul_Hossler]

I'll look, but seems strange


Here's a prettier version

Doesn't log anything but I wanted to see if there was interest in doing it like this, or just leaving it simpler

Capture.JPG

Added a 'Defaults' sheet that cvan be maintained off the form, and not 100% sure about the calculations

[Paul_Hossler]

Yes, it is strange

Capture.JPG

With row 2 solid back, CPU usage is very high




Capture1.JPG
Just deleting that row, then CPU usage is normal

There's no formulas or anything else in that row, so it's very^3 strange