Solved

Excel 2010:  How do I modify my base worksheet to perform like the model sheet and automate copies of the base sheet? (follow up to #28595451)

Posted on 2015-01-18
29
163 Views
Last Modified: 2015-01-31
In the resolution of case #28595451, we now have the 12-1 worksheet as a model of how we want the sheets to perform:   the actual portion size is entered into column A; the corresponding food item is selected from a dropdown list in column C, which linked to the table on the Food List worksheet.  Column C is then set for the selected item and the adjacent cells fill in with the corresponding macronutrient values, automatically comparing the portion size on the food list to the actual size and recalculating the values.

Fantastic work!  Now, how do we modify the base daily sheet to perform like the 12-1 model, and how can we automate the creation of new daily sheets?
Food-Log-December-2014-V03.xlsm
0
Comment
Question by:Dave Kong
  • 15
  • 14
29 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40556709
ok here it is:

Here is how it works. When you load the file and enable macros you will notice now that your sheet Base New Sheet has disappeared and it is now hidden. You can now have the possibility to create a new day by doing the following:

On the Top Menu you can see a menu called Add-in just click on it and you will see a new item called Add Day. If you click on this Menu you will see 2 sub menues:

1) Create Blank Day
2) Create Copy Day

if you want a blank copy that you will fill from A to Z simply select the blank menu and follow the instructions. However it may happen that you feel that you can use data that you already had done in a certain day like maybe the lunch is similar or breakfast and dinner or whatever so just before selecting the menu to create the day goto that worksheet and simply select it and then activate the Create Day and Choose Create Copy Day and follow the instructions and it will create the new day as a copy of the day you selected that of course you can modify to your needs by selecting the cells and changing values.

Any Day that is created is created after the last day already existing and not ranked !!! if this is needed then it can be arranged but this will require a sorting procedure that can be arranged separately in an other question.

Let me know your comments.
gowflow
Food-Log-December-2014-V04.xlsm
0
 

Author Comment

by:Dave Kong
ID: 40559075
Gowflow:

You are brilliant, and a mind reader!  Sometimes I do eat similar things more than one day in a row, so I often copy a previous sheet to create the following day.  Great thinking!

Today was my day off.  No skiing but I had fun.  I will test the file tomorrow.

Thank you!

Dave
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40559436
ok fine hv a good break !
gowlfow
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:Dave Kong
ID: 40560636
Gowflow:

Something is not right.  When I first open the first open the file, I get a small Microsoft Excel window that says, "Will delete menu" with an OK button.  I click past that.  I see the existing sheets have the dropdown functioning.  I click on the Create Day add-in item and see it defaults to today's date (nice!).  However, once the sheet is created, the C column is not showing the dropdown.

On my second try, after creating the new sheet, I got some message about the specified item not being part of the collection, but I accidentally closed it before recording the specific error, and then could not get it to appear on subsequent attempts.

Creating a copy day, however, is working fine.  I am entering today's meals, and it is a joy to use!

Dave
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40561396
Your are right there are several issues:

1) the msgbox saying delete menu is normal and is there as it reset the new menu created each and every time you start the workbook !!! If and when you don't see this message then you should get worried as this means either macros are not running or something is missing in your file.

2) As for the combobox not showing in blank day your are correct I did not test it but after looking carefully to the routine I had to change several things so I will show you how to implement the new code as you already started working and don't want to messup your already existing sheets.

Here is how to integrate the new solution.

1) You should first Disbale macros from running and this you open a blank excel workbook and goto Excel options (File/Options if Excel 2010 - or Office Icon in top left corner of screen and choose Excel Options for 2007) then choose Trust Center / Trust Center Settings / Macro Settings and here choose Disable All macros with notification.
Close the blank excel.

2) Open your latest production workbook. You should now not see this Delete Menu and close to the address bar you should see a warning that macros are Disabled with a button Options. This is what we want.

3) Press ALT F11 and it should open VBA

4) Doubleclick on Module1 in the left pane and you will see in the right pane the existing Subs.

5) In the bottom left corner of the right pane where there is code you see 2 small icons. Click on the leftmost icon it should display each sub separately. By default it displays all Subs. We want to isolate each sub as we need to replace them.

6) Now please locate the following Sub in the top right combo CreateBlankNewDay  and delete all the code that you have there.

7) locate the following Sub in the top right combo CreateCopyNewDay and delete all the code that you have there.

8) Paste the below new code after Any End Sub that you see in the right pane.

Sub CreateBlankNewDay()
Dim sName As String, sDate As String
Dim WS As Worksheet
Dim WSActive As Worksheet

Set WSActive = ActiveSheet

Do
    Do
        On Error Resume Next
        sDate = InputBox("Please enter sheet date in the form of mm/dd/yy", "Sheet Date", Format(Now, "mm/dd/yy"))
        If sDate = "" Then Exit Sub
    Loop Until IsDate(sDate)
    On Error GoTo 0
    
    sName = Format(sDate, "mm-d")
    On Error Resume Next
    Set WS = Sheets(sName)
    If Err <> 0 Then
        
        '---> Disable Events
        Application.EnableEvents = False
        
        '---> Create the sheet
        Sheets("BDS").Copy after:=Sheets(Sheets.Count)
        Set WS = Sheets(Sheets.Count)
        WS.Name = sName
        WS.Range("A8") = sDate
        WS.Visible = xlSheetVisible
        
        
        '---> Enable Events
        Application.EnableEvents = True
        WSActive.Activate
        WS.Activate
    Else
        MsgBox "Sheet: " & sName & " already exist !. Please choose an other name.", vbCritical, "Create Sheet"
        Err = 1
    End If
Loop Until IsDate(sDate) And Err = 0
On Error GoTo 0

End Sub


Sub CreateCopyNewDay()
Dim sName As String, sDate As String
Dim WS As Worksheet
Dim WSActive As Worksheet

Set WSActive = ActiveSheet

Do
    Do
        On Error Resume Next
        sDate = InputBox("Please enter sheet date in the form of mm/dd/yy", "Sheet Date", Format(Now, "mm/dd/yy"))
        If sDate = "" Then Exit Sub
    Loop Until IsDate(sDate)
    On Error GoTo 0
    
    sName = Format(sDate, "mm-d")
    On Error Resume Next
    Set WS = Sheets(sName)
    If Err <> 0 Then
        
        '---> Disable Events
        Application.EnableEvents = False
        
        '---> Create the sheet
        WSActive.Copy after:=Sheets(Sheets.Count)
        Set WS = Sheets(Sheets.Count)
        WS.Name = sName
        WS.Range("A8") = sDate
        WS.Visible = xlSheetVisible
        
        '---> Enable Events
        Application.EnableEvents = True
        WSActive.Activate
        WS.Activate
    Else
        MsgBox "Sheet: " & sName & " already exist !. Please choose an other name.", vbCritical, "Create Sheet"
        Err = 1
    End If
Loop Until IsDate(sDate) And Err = 0
On Error GoTo 0

End Sub

Open in new window


9) SAVE and Exit the workbook.
10) Activate your macros (if the original setting was not the one you found in trust center then open a blank excel and get the setting you had originally)
11) Load your production workbook and give it a try creating a blank new day and a copy and whatever...

LEt me know
gowflow
0
 

Author Comment

by:Dave Kong
ID: 40562961
I can do that!  I will have to wait until the day's crises are past, but will try it out tonight.  Thanks!

Dave
0
 

Author Comment

by:Dave Kong
ID: 40563689
I made the changes.  The file is working.  I have some questions, but let me use it for a couple of meals tomorrow in case I have more before I post.

It is fantastic!  Thank you!

Dave
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40563706
No problem, enjoy using the file, however try not to enjoy eating tooo much !!! :)
gowflow
0
 

Author Comment

by:Dave Kong
ID: 40566758
Very funny.  You are right, it is a good excuse to eat!

I used the file yesterday creating a brand new sheet and that worked well.  I was hoping to copy that day for today, but went into a strange loop when I tried to create the copy sheet.  

I have a training this morning.  As soon as I can, I will see if I can recreate the problem and send you the errors.

Dave
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40567902
ok will wait till you advise what is the problem.
gowlfow
0
 

Author Comment

by:Dave Kong
ID: 40568305
OK.  Back to it.  Work is getting incredibly busy, which is exactly why I needed to automate this process!

Attached is a Word 2010 file showing the message windows that appear when I attempt to copy an existing daily sheet.  It looks like it is cycling through everything on the Food List.

I have attached the message windows and the file currently in use.  I have been using the file, and it's real value is becoming clear:  I can plug in the values for the foods I have on hand and design the perfect meal before I even start in the kitchen!  It's a HUGE timesaver!

Dave
Copy-Date-Message-Windows.docx
Food-Log-December-2014-V04.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40568341
ok got your problem.

open the workbook
choose from the Formulas menu Name Manager
You will see in the list that for some reason you have some names that were created with #REF error.
Just click on the column Values or Refers to which ever shows the #REF error so that they will all be grouped.
Select all the #REF lines and highlight them and press on Delete
Close the window save and exit the workbook.

Open it and try all kind new blank on a day copy on an other etc ...

Let me know if it solves it.
gowlfow
0
 

Author Comment

by:Dave Kong
ID: 40569511
That worked!  Fantastic!

Thank you!  Let me use the file today and I'll get back with any questions.  This is great!

Dave
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40569521
ok great !
gowflow
0
 

Author Comment

by:Dave Kong
ID: 40574241
Gowflow:

I have used the file for the past two days and am loving it!  Your work has really sped up the process.  It is going to be a real time-saver.

Questions:

1.  How are the calculations done?  I use Irish butter a lot.  If I specify 1 tablespoon, the table shows 11 grams of fat and 100 calories, which is correct (as shown in the Food List table).  If I enter .5 tablespoon, the table shows 5.5 grams of fat and 50 calories, which is correct.  However, if I input .25 tablespoon, the table shows 2.2 and 20, which is not correct.

2.  How do I enter a food that is not on the list?  I thought once I had an entry, I just put the cursor in Column C and press F2.  That allows me to enter a non-list item, but when I move off the cell it reverts back to the original entry.

3.  What is the best way to add new foods to the Food List?  If I add an item to the list, the macros stop working.  I've found I need to add a food to the list and then exit Excel and reopen in order to reactivate the macros?

Dave
0
 

Author Comment

by:Dave Kong
ID: 40574243
I meant to attach the current file!  Here it is.
Food-Log-December-2014-V04.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40574769
ok will look at that during the course of the day. Meantime when you say:
However, if I input .25 tablespoon, the table shows 2.2 and 20, which is not correct.

What is then the correct figure ? I will look again to see how it calculate. But for me to understand when you say if I put 1 tbs it gives and 0.5 it gives and 0.25 it gives ... you are sitting on the same item and changing the values or your moving to an other item then back to this one and trying a different setting ?

gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40575123
ok here it is you are correct it was rounding to total unit so I removed the rounding and noticed that if you edit the record like if you are in C column and have an item like egg white and press F2 so it goes in edit when you press enter it would change the item below it. This has been fixed. If you change quantities to update the figures just point to the item in Col C and press F2 and then enter it will recalculate.

Now as far as a new Item now simply type it there and it will show when you move the cursor from it in C. Now if you are back to it for sure if it is not in the table you will temporarily see blank but if you move away you will see it back.

When the item is know and you want it for good insert it in the table (I made a sample and inserted a dummy Zimbo at the end of your table in red and with its corresponding values) without changing anything to the code it now appears in the list !!!

Let me know your comments.
gowflow
Food-Log-December-2014-V06.xlsm
0
 

Author Comment

by:Dave Kong
ID: 40576030
Gowflow:

Thanks!  I will try this out later today.

Dave
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40576922
No problem.
gowflow
0
 

Author Comment

by:Dave Kong
ID: 40577533
I just started testing.  So far, so good.  Will test throughout the day.  Thanks!

Dave
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40577792
Let me know then.
gowlfow
0
 

Author Comment

by:Dave Kong
ID: 40579117
OK.  This is a thing of beauty!  Everything is working quite well.   The calculations are now correct.  Pressing F2 to recalculate when I change values is working (nice!).  Inserting new items in the table is working fine.

I didn't at first understand entering non-list items, but i see now that that only works if column C is not already filled in from the table.

It is really great!  THANK YOU!

One final question, can I delete existing sheets without messing up the code?

Dave
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40579522
Absolutely you can delete any sheet with no problem this is how I was testing it !!! don't delete Food List though :)
gowflow
0
 

Author Comment

by:Dave Kong
ID: 40580751
Fantastic!  I am set.  I will close this case out today and give you the highest marks possible.

You've really done an amazing job with this project!  It is far better than I had imagined.  Really great work!

Dave
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40581263
Glad I could help,
n Yes I think this question is due for closure as have been well loaded !!!!
Do not hesitate in letting me know if you need more help by posting a link in here (for sure after closing this one) as I will keep this question monitored.

Gowflow
0
 

Author Comment

by:Dave Kong
ID: 40581792
I will do that.

Again, thank you!  You've done an incredible job.  The file SINGS, and it is beautiful!

The food log is part of my physical training program.  The trainers have always stressed living well by setting goals both in and out of the gym.  My new goal is to learn Excel programming, so I can be cool like you.

Dave
0
 

Author Closing Comment

by:Dave Kong
ID: 40581797
Unfortunately, this rating system doesn't allow for an A++, because that is the job Gowflow has done!

If anyone has followed the question thread, what had started as me asking about automatically inputting values from a table, Gowflow has figured out how do that and more.  He created a fully functioning food log that automatically adjusts values based on the actual amount eaten vs. the values on the food table.  You can create blank new daily sheets or copy an existing sheet for similar days.

If you've ever tried to keep a food log, this is not only an incredible time saver, but it allows you to quickly program the perfect meal based on your personal needs.

Bravo, Gowflow!  Bravo!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40581925
WOW Tks for the very nice compliments and I am very happy to have been able to help you and indeed very appreciative to all your nice comments which is the fuel for us here in EE !!!

Pls do not hesitate to post a link in here for any other question you may need help with.
gowflow
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I will show how to use the Ribbon IDs Tool Window to assign the built-in Office icons to a ribbon button.  This tool will help us to find the OfficeImageId that corresponds to our desired built-in Office icon. The tool is part of…
zlib is a free compression library (a DLL) on which the popular gzip utility is built.  In this article, we'll see how to use the zlib functions to compress and decompress data in memory; that is, without needing to use a temporary file.  We'll be c…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question