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
159 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
Comment Utility
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
Comment Utility
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
Comment Utility
ok fine hv a good break !
gowlfow
0
 

Author Comment

by:Dave Kong
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No problem, enjoy using the file, however try not to enjoy eating tooo much !!! :)
gowflow
0
 

Author Comment

by:Dave Kong
Comment Utility
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
Comment Utility
ok will wait till you advise what is the problem.
gowlfow
0
 

Author Comment

by:Dave Kong
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ok great !
gowflow
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Dave Kong
Comment Utility
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
Comment Utility
I meant to attach the current file!  Here it is.
Food-Log-December-2014-V04.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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
Comment Utility
Gowflow:

Thanks!  I will try this out later today.

Dave
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
No problem.
gowflow
0
 

Author Comment

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

Dave
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Let me know then.
gowlfow
0
 

Author Comment

by:Dave Kong
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial is about how to put some of your C++ program's functionality into a standard DLL, and how to make working with the EXE and the DLL simple and seamless.   We'll be using Microsoft Visual Studio 2008 and we will cut out the noise; that i…
This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now