Log the results

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.

.
Can you work with a different layout ?

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

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

Quote:

---

Originally Posted by Logit

.
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.

I did something similar

Formats a little different

Lots of choices, or combine the different pieces

Input sheet

Attachment 27593


Log sheet

Attachment 27594

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.

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

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.

Another suggestion

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

Code:

---

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

---

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

@joeny

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

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

http://www.vbaexpress.com/forum/imag...BJRU5ErkJggg==

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.

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

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?

Quote:

---

Originally Posted by Paul_Hossler

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.

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

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.


http://www.vbaexpress.com/forum/imag...AASUVORK5CYII=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%.

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

Attachment 27601

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

Yes, it is strange :banghead:

Attachment 27602

With row 2 solid back, CPU usage is very high




Attachment 27603
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

As you may have noticed: the program itself chooses breakfast/ lunch, etc. depending of the time the userform is opened. It's all aimed at reducing the user's task/burden.
To me it seemed unlikely the userform would be opened at 8 AM to enter the data for dinner or even for evening.
You are tsting now for unlikely events (what the userform isn't meant for).
Anyway I adapted the userform for improbable events.

Paul


That is great. The default sheet is also very handy. They do change. That was going to be one of my questions before how to adjust for changes.

Everything seems perfect. Can we get that to log?
The cpu is normal with the latest one also.

snb



Looks great. One question is the values do change at times when the dr says to. Also the target for evening 1 and 2 is 150. How do I change the values for target, ratio and factor when needed?

snb


She sometimes has strange schedules. We are still working on getting her sleep schedule better. I think it is very cool to chose by the time of day "I did not notice that" but there are times where her lunch is at 5pm.
So allowing for improbable events is very helpful.

You help is very much appreciated.

So I see you did end up having the CPU usage do the same.


I did some research but unable to find a reason why this could be.

Quote:

---

Originally Posted by Paul_Hossler

Yes, it is strange :banghead:

Attachment 27602

With row 2 solid back, CPU usage is very high




Attachment 27603
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

---

Going out on a limb here, but I think I can see what causes the high CPU usage, but not why it does it

The way I set Freeze Pane originally (don't remember how) some how caused the usage to be very high with the rows hidden

Removing the Freeze Pane and making D3 the new Freeze Pane, seems to solve that -- very weird


Attachment 27613

I'm guessing that setting the Freeze Pane to a hidden might be the issue

Quote:

---

Originally Posted by joeny0706

Paul


That is great. The default sheet is also very handy. They do change. That was going to be one of my questions before how to adjust for changes.

Everything seems perfect. Can we get that to log?
The cpu is normal with the latest one also.

---

Working on it

How do you want the Log sheet sorted?

a. Add at bottom so newest is always in Last Row

b. Insert at top so newest is always in Row 2, next newest is in row 3, etc.

Play with this version

It logs inputs, and writes to next line in log unless you're updating an existing entry, then it just replaces the old data

Paul

i deleted row 2. I then inserted a new row turned it black and locked it. That also solved the issue. I don’t know as much as you. Like what a “freeze pane is “I will look it up” or why that is better to use. But I did find a very easy way to make the cpu usage go away.

I will test that out tomorrow once I get back on my computer. From you explanation it seems to be all I need. I just need the log to look through the history. Just adding to a list is fine. So any features are just a bonus but not needed.

Thanks

In Range("J1:N6") you can adapt any value (as long as you do not change the structure).

Quote:

---

Like what a “freeze pane is “I will look it up” or why that is better to use

---

Just a way to keep (for ex) row 1 or col 1 or both always on the screen. Can be any row and/or column, but this is typical


1. Selecting B2 and Freeze Panes

Attachment 27623


2. sort of 'locks' row 1 and column A on the screen. This way you can always see the header row(s) and maybe the 'main' column(s)

Attachment 27624