Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas In Cell Comments
Greetings,
Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas In Cell Comments
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 "Minitman" wrote in message ... Greetings, Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas In Cell Comments
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 On Fri, 23 Apr 2010 10:38:47 +0100, "Peter T" <peter_t@discussions wrote: 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 "Minitman" wrote in message .. . Greetings, Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas In Cell Comments
I don't understand, describe your overall objective, if necessary with
example. Otherwise it's just a guessing game. Regards, Peter T "Minitman" wrote in message ... 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 On Fri, 23 Apr 2010 10:38:47 +0100, "Peter T" <peter_t@discussions wrote: 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 "Minitman" wrote in message . .. Greetings, Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas In Cell Comments
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 On Fri, 23 Apr 2010 19:13:12 +0100, "Peter T" <peter_t@discussions wrote: I don't understand, describe your overall objective, if necessary with example. Otherwise it's just a guessing game. Regards, Peter T "Minitman" wrote in message .. . 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 On Fri, 23 Apr 2010 10:38:47 +0100, "Peter T" <peter_t@discussions wrote: 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 "Minitman" wrote in message ... Greetings, Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas In Cell Comments
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 "Minitman" wrote in message ... 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 On Fri, 23 Apr 2010 19:13:12 +0100, "Peter T" <peter_t@discussions wrote: I don't understand, describe your overall objective, if necessary with example. Otherwise it's just a guessing game. Regards, Peter T "Minitman" wrote in message . .. 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 On Fri, 23 Apr 2010 10:38:47 +0100, "Peter T" <peter_t@discussions wrote: 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 "Minitman" wrote in message m... Greetings, Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comments within formulas | Excel Discussion (Misc queries) | |||
Formulas as comments | Excel Discussion (Misc queries) | |||
Having comments copied when using formulas | Excel Worksheet Functions | |||
formulas in comments in excel | Excel Discussion (Misc queries) | |||
UDF code to find specific text in cell comments, then average cell values | Excel Programming |