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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas In Cell Comments
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 On Sat, 24 Apr 2010 11:14:54 +0100, "Peter T" <peter_t@discussions wrote: 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 om... Greetings, Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas In Cell Comments
"Minitman" wrote in message Hey Peter, Your right, it can't be done without appending the data and formulas to each item. 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. I'm not sure what you mean, what's a cycle time, excercise? Regards, Peter T Any Ideas? -Minitman On Sat, 24 Apr 2010 11:14:54 +0100, "Peter T" <peter_t@discussions wrote: 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 m... 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 news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax. com... Greetings, Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas In Cell Comments
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). On Sun, 25 Apr 2010 11:44:40 +0100, "Peter T" <peter_t@discussions wrote: "Minitman" wrote in message Hey Peter, Your right, it can't be done without appending the data and formulas to each item. 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. I'm not sure what you mean, what's a cycle time, excercise? Regards, Peter T Any Ideas? -Minitman On Sat, 24 Apr 2010 11:14:54 +0100, "Peter T" <peter_t@discussions wrote: 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 om... 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 news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax .com... Greetings, Is it possible to run formulas or vba code inside of a cell comment box? If so, how? -Minitman |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas In Cell Comments
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 "Minitman" wrote in message ... 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). On Sun, 25 Apr 2010 11:44:40 +0100, "Peter T" <peter_t@discussions wrote: "Minitman" wrote in message Hey Peter, Your right, it can't be done without appending the data and formulas to each item. 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. I'm not sure what you mean, what's a cycle time, excercise? Regards, Peter T Any Ideas? -Minitman On Sat, 24 Apr 2010 11:14:54 +0100, "Peter T" <peter_t@discussions wrote: 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 m... 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 news:p223t5pu8mdq8cpo10t722cm2bb8u7vlps@4ax. com... 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 news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4a x.com... 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 |