Formulas In Cell Comments

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

Greetings,

Is it possible to run formulas or vba code inside of a cell comment
box?

If so, how?

-Minitman
 
Not sure about VBA code but something like this might -

Sub test()
Dim sFml As String
Dim cm As Comment
sFml = "A1*4+A2"

Set cm = Range("A1").Comment
If cm Is Nothing Then
Set cm = Range("A1").AddComment
End If

cm.Text sFml

Range("A1").Value = 20
Range("A2").Value = 10

Range("C3").Formula = "=foo()"
Application.CalculateFull

End Sub

Function foo()
On Error GoTo errH

foo = Evaluate(Range("A1").Comment.Text)
Exit Function
errH:
foo = CVErr(xlErrValue)
End Function

Might need to press Ctrl-Alt-F9 after manually editing the comment

Regards,
Peter T
 
Hey Peter,

Thanks for the reply

I'm not sure how to convert your code from an absolute reference to a
relative reference. I need to insert this code into an entire column.

How do I do this?

-Minitman
 
I don't understand, describe your overall objective, if necessary with
example. Otherwise it's just a guessing game.

Regards,
Peter T
 
I have diabetes and my doctor wants to see what foods I am eating, as
well as how much and how often. I built a spreadsheet to record all
of this information.

In a data sheet called "FoodList", I entered the 12 categories of
nutrition that the FDA requires all prepackaged foods sold in the US.

On the log sheets, each of these categories is split into 3 columns
(1-Category values of the food in the description column I call an
"ITEM", 2-Add all "ITEM" values listed in the same date-time I call a
"MEAL" and finally 3-Add all of the "ITEM" values that have the same
date I call this "DAILY TOTAL")

I used VLookUps to get the data for the category "ITEM" into the log
sheet. The VLookUps values are adjusted by a ratio of the log sheets
actual size divided by the data sheets standard size.

I have formulas to do all of this.

I had to delete all of them (the formula cells not the sheets - and I
still have the back-ups)!

After filling up a few months of log sheets, the cycle time for each
entry was approaching 30 min. I did everything I could think of to
clean up and speedup my code. Finally I had to admit, my whole
approach was flawed, so I have gone back to the drawing board.
I was thinking of using the cell comment to display the 12 USDA facts
in an array appearing format (not an ARRAY as defined in Excel)

I still have a data sheet called "FoodList", which has about 8000 rows
with 13 columns (it is still growing). I have a lot of monthly log
sheets labeled as dates in the format of "Mmm-yy" starting with
"Nov-06" up to present.

Without actually naming each FDA nutritional item, this is what I am
aiming to see in a comment box:
_______________________________
FDA | ITEM | MEAL | DAILY |
ITEMS | VALUE | TOTAL | TOTAL |
Item 01 |________|________|_______|
Item 02 |________|________|_______|
Item 03 |________|________|_______|
Item 04 |________|________|_______|
Item 05 |________|________|_______|
Item 06 |________|________|_______|
Item 07 |________|________|_______|
Item 08 |________|________|_______|
Item 09 |________|________|_______|
Item 10 |________|________|_______|
Item 11 |________|________|_______|
Item 12 |________|________|_______|

Is this possible?

Thanks for your interest and help.

-Minitman
 
You could certainly write a table able the lines of "what you are aiming to
see" in a cell comment. Thereafter, in theory at least, extract data from
the comment table for use in some other code. Personally I wouldn't do it
that way. Better to keep all your data in cells, normal Lookups and/or VBA
code to process etc., which if I gather correctly is the way you did it
before. No problem to have a bit of code to populate comments the way you
want them purely for visual purposes

Is your data stored in table form per food item (along the lines you propose
for your comments. If it is, I'd suggest arranging it as one row of data per
food item, ie food-name and 36 columns of data. You will probably want a
data entry form or better still a userform with say 3x12 text boxes in which
to enter new food data, and/or read existing food data and update the food
data if/as necessary.

It would be easy enough to have 3x12 VLookups to display the row of 36 data
items in a 3x12 matrix. However you might not even need that (other than for
display). It should be possible for you to have a very simple (as far as
user is concerned) data entry system something along these lines

Enter Food-name (say from dropdown list), qty, date/time.
(data could be entered cells or a userform)
Press a button

Code to update date sheet with food name, qty, the 12 items associated with
the food name, in turn totalled for the day.

Though this might not answer your question directly, maybe it is food for
thought!

Regards,
Peter T
 
Hey Peter,

Your right, it can't be done without appending the data and formulas
to each item.

But I am still at a loss as to how to speed up these 30 min cycle
times.

Any Ideas?

-Minitman
 
Minitman said:
Hey Peter,

Your right, it can't be done without appending the data and formulas
to each item.
Click to expand...

Not sure I follow, you might need one set of 36 Lookups all with the same
food-item lookup (rearranged into say a 3x12 matrix), but not a seperate set
for each food item.
But I am still at a loss as to how to speed up these 30 min cycle
times.
Click to expand...

I'm not sure what you mean, what's a cycle time, excercise?

Regards,
Peter T
 
Thanks Peter for your help. You made me take a long hard look at what
I was asking for and realize my basic approach was flawed.

I have to store the VLookUp information (only 12 columns needed) as
values on the log sheet (like what you suggested). But rather then
using VLookUp as paste down formulas (as in my original approach), I
need to modify the posting sub in my UserForm (which already has the
VLookUp information on one of the MultiPage pages, just not the log
entry page, but it is there). My original attempt was to make the
entire row a kind of display (not very usable). This approach should
eliminate a lot of the calculation problems and time delays I've been
experiencing.

The rewriting of the User Form code to make these changes is fairly
straight forward, if not a little time consuming.

I then will be ready to attempt to set-up the comment box as my
display method.

Do you have an example of how to make a comment box with vba showing
the value of the column L in the row just loaded from the UserForm, in
the first column of the comment box.

For the second column, I need a formula something like this where
column K is the result of combing the day value in column A with the
time value in column B (I could not see how to do the SUMIF without
the helper column. Perhaps you see a better way?):

=SUMIF(K:K,K{current row})

For the third column, something like this:

=SUMIF(A:A,A{current row})

Current row refers to the row of the cell just triggered by the mouse
over event.

Any help is greatly appreciated.

-Minitman

P.S. - I can send you a sample of my workbook if you would like. If
so, please send me an email (my email address is not hidden).
 
OK I'll have a look at your workbook off-line though I'm a little concerned
I'll end up needing to remake it from scratch.

One thing though please, and don't take this the wrong way, before sending
any email read it back and ask yourself if someone who doesn't know what you
know, will be able to understand your explanation / question. For example in
your message below you asked several questions. I'm sure they are all fairly
straightforward but I really haven't got a clue about the details of any of
them and hence how to answer them. It's been rather like that with some of
your previous questions.

My address is in the reply-to field, lightly disguised.

Regards,
Peter T
 
Back
Top