Hi, I just finished building my menus around the sheets we were working on and I find that there are some problems with the buttons after I made a single change by Adding ONE row. Please ask for the file for your reference- For the Print Buttons, the last line - Desserts: Crumb cake seem to come up on every page even though its value is zero- The Drivers button doesn't work at all - All the checklist buttons do not workThe only change I did, was added a new menu item in Row 35 - Pulled BBQ Chicken. I would hope that all the buttons would still work if I inserted a Row to add a new menu item (or deleted a row for that matter)Please adviseThank you
got it
Ok let us set up an hour session tomorrow at around 8 pm pacific. Let know when you are online at that time so we can start
Sounds good. Ill confirm the time tomorrow.
ok, till then
Hi, so sorry, it's too late by me. Can we do it earlier tomorrow
How about from 3:00 to 3:30 pm pacific tomorrow
Can we di 2 pm pacific
No I have an engagement until after 2:30
Ok, so 3PM it is
Ok good
I am on line now. Are you there?
No I am leaving in a short while
You have missed two time slots that I had asigned for you. Not sure what else I can do
I am rally sorry I misse this time. I was really in a bind and couldn't make it. Please understand that I live in NY and work during normal business hours - 9 to 5 om eastern standard. Today it just didn't work out and I am sorry. YEsterday you wanted 11 pm - which is after I go to sleep. Hopefully we can compromise on this. I don't mean to be difficult but you are the only person who can fix this and I have been pleading with you for over 1.5 weeks! Please, I beg you to just look at my sheet and fix the buttons so they work. I told you the problem already with as much detail as I know. PLEASE, I AM BEGGING!!!!!
Half an hour tomorrow at 3:00 pm ok?
Tell me ahead of time if you change your mind so I can make use of my time
3pm is 6 pm my time. It will work. thx
Ok
I'm ready...
I am here
Ok great!
Please close all excel files, then open only the last file that I sent you which is:
"PackingSlips12a COMBINED (original).xlsm"
ok done
Ok now which button and on which sheet are you having an issue with?
If you click on the Mon tab. Insert a row in row 35. I will name the new cell A35 - "Wrap:BBQ Chicken". Now all the buttons have problems
give me a speciifc button so I can check the code
Please try to hurry up I have another engagement in 10 mins
If you click INVOICE button, the last row of the printed sheet is DESSERT:Crumb Cake - but it is empty and should not appear at all . The same problem is happening for PACK FOR C, PACK FOR I, PLATTERED, NO PACK
I don't know what that button does. I need to review the code...
Ok. Also the Drivers button does not work properly and all the buttons you created in the ChecklistMon, ChecklistTue, ChecklistWed, ChecklistThur, ChecklistFri will no longer work
Ok looking at that code it is clear that the macro was written for the data structure that is currently on that page (both columns and rows). You must maintain that data structure for the code to work. Any deletion/insertion of rows and columns will affect the macro.
Sorry it is not something that can be fixed easily (especially when there are that many macros involved) nor was it a requirement that rows or columns can change
Will have to continue at another time as I will be something else soon
Ok. Please note that when you originally designed the code for the buttons we did discuss these issues on April 18th and you said at that time you will work it into the code if I need to add or delete a row in the menu section. Please see our correspondence belowWednesday, April 18, 2012 10:01 AM EST If I add or delete rows or Columns will it effect the printing? For example I amy want to Delete rows 5 through 9 and I may want to add or subtract menu items between rows 22 and 118. I may also want to add a column when I get a new account (or delete a column when I lose an account). Will all this be possible without effecting the print macro? thx Your Expert needs more informationFrom John DWednesday, April 18, 2012 10:51 AM ESTThe current macro processes the data in rows 21 thru 117. If you want this range to be variable we can have the macro identify the first row of data (say by looking for the word "BOX #" in column A), and the last row of data (say by looking for the word "TOTAL" in column A). That way it will not matter where the data is or how many rows it covers. However, the values in rows 2 and 13 must be in a fixed or predetermined rows As for the columns we can make the macro automatically determine the last column of data by looking for the last entry in row 1 Hope this clarifies these points
Here is my response on April 18th you your offer to change the code to enable addition or deletion of menu item rows. You repliedWednesday, April 18, 2012 11:02 AM ESTOk, so please make it possible to add menu items
I just posted a long reply and it appears that it did not get posted....
Have to go now. Will post it again later this evening
ok. thx
It got over ridden (erased) by your repeated postings
I just wanted to tell you that I do not recall the details of these discussions and had you brought this up at the time I am sure we could have done something about it. But coming back two months later asking for these changes does not sound reasonable
SO THE MAIN ISSUE HERE IS THE LATE REQUEST
That said, it is clear that the structure of the sheet you privided is close-ended, and hence the macro was created for that structure (i.e. you have data and buttons at the bottom of the list of data which gives the data range limitation)
At this point in time to change all the macros to accommodate this request is not something that can be easily done. Due to your late request I have already spent a few hours since you came back with this request and I am sorry I cannot spend any more time like this.
In order to bring this extended discussion to a conclusion you can make me an offer to make these changes and I will let you know if I can do it and when it can be finished
Thank you
Even though you assured me at the time you wrote the original code that my current issue will not be one, I am at your mercy. I have already offered $60 for you to do something that I technically should have paid for already. I will offer you a $60 bonus to fix this. Is that fair?
Ok thanks.
Let me make sure I understand it 100% correctly. You want the 6 top buttons on the five weekly sheets to process any number of rows of data on these weekly sheets starting always from row 16 and ending with any row that has the word "TOTAL" in column A (wherever that row is). Correct?
Is there anything else needed that I may have missed?
It's not just those buttons, it's also the checklist sheets buttons. It should also work if I add or subtract columns a well
I would like to try and help if i may? I see John D has opted out. If you would upload your workbook to me with a set of instructions as to what you want it to do, I will see if i can do it. Cheers
So that means all the buttons on all the 5 weekday sheets and the 5 checklist sheets
Will start on it in the morning and will let you know the progress
ok thx
I am done with one set of codes. Will try to have it ready tonight or by tomorrow morning
ok
Getting close but not quite done yet
One question. Does the menu list on the Drivers sheet have to be 100% identical to the main menu lists on the daily sheets (Mon, Tue, etc), i.e. when you delete or add new rows on the daily sheets, will you be making the corresponding changes on the Drivers sheet so they remain identical?
It does have to be identical. I can add or delete rows in that page if need be
OK thanks
Ok done. However I can do with some more testing. So please let me know when you are ready so I can send it to you
I will be online all day tomorrow. Let menu know when it works for you Thanks
Ok will send it out today
Here you go
Please go ahead and test all the macro and let me know if any part needs any adjustments
http://www.filesharesite.com/files/201206/1341005988PackingSlips12a_COMBINED_revised.xlsm.html
Hope this is ok
Everything on the Mon tab works.
In the Tue and Thu, Fri tab, when I insert the BBQ chicken in Row 35, and click DRIVERS, dozens of rows that should be hidden appears. Sometimes if I press the DRIVERS button, the drivers sheet appears ok with no extra rows, but if I press the button again, tons of extraneous rows appear
Also in THU the NO PACK (I think it is misspelled it should be No Pack and NOT No Rack) is printing the wrong sheets. It should be printing All sheets where Row 2 >0 and Row 11 does NOT say Pack. Instead it printing where Row 11 says Pack.
In TUE, WED, FRI the NO PACK Button does not work at all
Are you adding the BBQ Chicken row in BOTH the TUE and DRIVERS sheets and in the same sequential location as we have discussed earlier? Remember the menus should be 100% identical in both sheets before you can run the Drivers macro
As for the speed, new routines have been added to the code so the speed should be affected but not that much. You can test the speeds by timing the original version with this new version. Anyhow I don't think anything can be done in this regard
Yes, I added in both sheets it so that it is identical.
Please send me that file so I can check it
http://www.filesharesite.com/files/201207/1341245362PackingSlips12a_COMBINED_revised-1.xlsm.html
I ran DRIVERS from Tue and I did not notice any issues. Here is a screenshot of the output
If it woks on the Mon tab it must work on the others because the CODE IS THE SAME FOR ALL TABS. The issue must be in your data. Try to copy the data from the sheet that works to the sheet that does not work and you will see the code does work on the copied sheet
Diagnosing issues like that could take a whole day (and there are some 36 procedures in this program) and I am sorry I do not have the time for that at the moment. Have already spent over four hours on the new code in addition to several hours on these 30+ messages. If you are not happy with the code I don't think there isn't much more we can do. Unless you want me to work on time basis
Ok, how about the NO PACK buttons?
Will check them if I can see at least an interim compensation for my time
BTW I am assuming that the new work is substantially completed. If you don't think so let me know
Yes. Thx
Just letting you know that nothing came through.(in this new JA system you need to click the rating button instead of the usual accept button)
Thanks
I try clicking the rating button, but nothing works
It could be site related issue.
Could you try one of the buttons that show up in this message
Experience: Bachelor of Science - Engineering Consultant
Got it. Thanks
thx. Looking forward to getting the NO PACK buttons fixed
Will start on it as soon as I finish the job on hand
Please check the NO PACK buttons on all 5 sheets and let me know if there are still any issues with that macro (or with anything else)
http://www.filesharesite.com/files/201207/1341255263PackingSlips12a_COMBINED_revised_a.xlsm.html
I checked, and the NO PACK buttons on tue, wed, thu & Fri do not work still
Again this is a DATA issue. If you copy your Mon sheet to Tue or to Wed etc you will see that the NO PACK button does work on that data.
Remember the NO PACK macro prints only the columns that have:
- a number greater than 0 in row 2
AND
- blank in row 11
So you need to check your data in Tue, Wed to make sure these criteria exist on these sheets
you are 100% right! I got it now, thx
Great!
Let me know if there is anything else that needs fixing so we can be bring this to a successful conclusion
I just plugged your code into my spreadsheet (the one I sent you originally) and everything works except the drivers button. I get the following error
Could you send me that file please, and tell me sheet is the one in question
http://www.filesharesite.com/files/201207/1341265804FreskoMaster_JOHN.xlsm.html
IF you click on MON and click on the Drivers button, you will see the error
Again that is a DATA issue
The program adds the number of meals for each menu. So it expects that the table associated with the menus has numbers that can be added. However in cell AG40 on 'Mon' you have entered text data (i.e. "NO CURRY!") in that cell so obviously the sum formula will not work
Remove any text from your quantity table and you should be good to go
Hope this helps. Please do not forget to use complete the balance payment as agreed last week
Ok. Thanks! The only issue is that the print buttons take so long to print. When I click INVOICE it takes 7 minutes to print 30 pages. Are you sure there is nothing you or I can do to speed it up?
Yes but I asked you to check the time it takes to print the same data using the original version as compared with this version
Hi, I am testing the printing in my office and it takes absolutely forever to print this. Can I pay you to do something to make it print faster??
Ok I will look into I will see I can find an enhacement to speed up printing, provided iof course it is not something that is related to your system reources and/or printer. Cannot promise but I will try
In the meantime please don't forget to settle the balance of last project so we can conclude that part
Let me know what you intend to do
How do I get you the balance , the feedback buttons don't work. I definitely want you to make this thing faster
Ok could you scroll up to the message that you have rated/accepted yesterday, you should see an Add Bonus button there. Let me know if you don't
Got it, thanks.
Which button(s) on the 5 weekday sheets is printing too slow?
All of them.
Ok will see what I can do
I have been trying and testing various approaches to achieve optimal running time and I think I am coming up with a combination involving Filters (instead of hiding rows) and Manual Calculation which looks quite promising. Need to test it more throughly and modify the rest of the code to work with the new approach
I don't think I will able to do much work on it tomorrow but I should be able to send you something on Thursday
K. Thx
Ok please check the output to make sure the filtering and the manual calculation are not affecting the results
http://www.filesharesite.com/files/201207/1341515509PackingSlips12a_COMBINED_revised-enhanced_a.xlsm.html
Hope the speed is ok now
It would help to mention which button, which sheet, which line of code gets highlighted in yellow when you click the Debug button
All the print buttons on Mon, Tue, Wed, Thu, Fri
hmm.. which line of code gets highlighted in yellow when you click the Debug button
I just sent you a screen shot
Tested it now on two systems and it's working fine on both. I hope this is not another Mac related issues?
Please test it on a PC and I hope it will work for you
It requires around two hour time slot. I can start on it Monday afternoon hoping I can have it ready by Monday evening.
Will keep you posted
I just noticed that some messages got accidentally posted in the wrong page.
I apologize for the mistake. Please ignore the last two messages
Ok I modified the INVOICE code hoping it will run on your system without any errors(can't really tell on my end as I was not getting any errors)
http://www.filesharesite.com/files/201207/1341530982PackingSlips12a_COMBINED_revised-enhanced_b.xlsm.html]
Let me know how this one goes
Still not working. I am including a screen shot - I am getting the exact same error,
That is not the same new macro that I just sent you. You seem to be running the old file or you have more than one file open at the same time
Close all excel files, then download and save this file to your desktop.
http://www.filesharesite.com/files/201207/1341536091PackingSlips12a_COMBINED_revised-enhanced_c.xlsm.html
Then go to the desktop and open this file only, and run INVOICE
It still doesn't work. All other excel files were closed when I pressed HEre is the screen shot
No problem. If you cannot make use of the print enhancement modifications just ignore that file. You do not have to pay for that part
Have a great week
Why, can you not fix it? I really need to get this working faster. I will pay!
Sorry the issue is not your intention to pay. I worked on this for what amounted to a full days work. Now you come back 4 days later wanting me to diagnose issues that are related to YOUR system (ALL PRINT ENHANCEMT MACROS WORK 100% CORECTLY ON MY SYSTEMS)
I do not really recall at this point what changes I made 4 days ago and I do not really have time to start all over on it or even go through a series of diagnostics. That's why I decided to give up the time and cut the losses.
Thanks for your understanding
Please, I was away from my computer on Friday and Saturday. I will pay you another $100 to fix this. My whole system is worthless without the printing capabilities
Ok can you stay with me in an online session until we have resolved the issue so we do not have to come back to it again 2 days or a week later?
Ok. Please tell me what time and Ill be on
How about tomorrow any time between 3:30 pm and 9:30 pm pacific (excluding 6:30 thru 8:00). Would that be too late for you
Let me know which hour within that slot you prefer. Also you need to be on a PC so we can test it on PC first
Ok, 6:30 pm it is.
lol.. but I said excluding 6:30 thru 8:00 pm
Waiting for you to post an alternative time between
3:30 thru 6:30
OR
8:00 thru 9:30
3:30
k
I'm ready when you are...
Im on
Ok please close all Excel files and applications and open version b of the file that I sent you which is this one:
PackingSlips12a COMBINED (revised-enhanced) b.xlsm
If that file has been altered please stop there so I can send you the correct file
You sent me enhanced B and Enhanced C. Which one should I open?
b-1?
I have been pasting the correct and full file name. i.e.
If you cannot find that file scroll up on this page and look for it in one of the download links then download it
Ok, I opened it
- Ok which operating system are you running on this pc
- Which version of Excel
- Are all applications and excel files closed except this file
So we are doing the PC, before the MAC, ok.
Good
Ok go to the Mon tab and click the INVOICE button and wait until you get a message (or an error)
It worked on the PC
Whew! Been trying to tell you that it is a MAC issue but you kept ignoring my remarks
Ok the printing in this version should take less than a minute for what it used to take about 7 mins. Correct?
It said 302 seconds total
hmm.. the original used to take 318 sec. and I got it reduced to 44 secs
Interesting. On my machine it took 302 seconds. Any thoughts?
Vista is known to have issues. Also it could be your printer
Ok could you try this
Go to the INVOICE code and delete this line
ActiveSheet.PrintOut
then run the macro again and report the number of pages and time
43 pages in 53 seconds. Much better
Ok please download this file and save it to your desktop, then close all Excel files then go to the desktop and open the file. Then go to the Mon tab an click the INVOICE button
http://www.filesharesite.com/files/201207/1341877223PackingSlips12a_COMBINED_revised_a1.xlsm.html
Let me know what the message says
There is no message - to is just doing something - and stalling. Nothing is happening and nothing is printing
Because it's not done yet. And yes I deleted the printing line from that file
You need to let it finish until it shows the Done! message and give control back to the user
Vista is not the best OS to do this kind of testing on
Its still not done - 9 minutes already
I thought so.
Ok that last program is the SAME combined file that I sent you before the enhancement, except that I removed the print command and added a timer.
Conclusion;
The new enhanced program reduced the execution time when tested on the same machine from 9 mins (which is 540 secs) to 53 secs. Which is an enhancement of about 10 folds
So I think I should get some credit for my work on the enhancement which has taken over 10 hours of my time so far
I don't understand. Why when I erased the line did it work fine, but when you sent me the revised file it doesn't work at all??
I do not know about your system but if you read my last conclusion where we are comparing two programs with no printing in both (apples to apples) the speed got enhanced by 1000%
Now if you question that conclusion let me know. Again I need to know if I am going to get credit for my work up to this point or not as I was reluctant put more hours into this and wanted to cut losses before you encouraged me to which resulted in adding 2 more hours this afternoon
You will definitely get credit - as I always have. Just please send a finished file that works. If you need to see some money before you send the file, then fine. Tell me how much and it will r done. This process is as frustrating for me as for you - believe me.
Frustrating? How about also taking 10 hours of your time..
In short I was able to demonstrate yesterday that the program does work on the PC and it has reduced the running time by 10 fold. Run it on a Windows 7 machine and you will see that speed is more than satisfactory
Any further assistance will have to be on time basis after of course settling the previous work
Ok. If I erase the print line, they will all work, yes?
It does work both with the print line AND without the print line, as I have shown you on the Vista systemThe problem is that due to some issues on your Vista it is running the code with the print at a very slow speed as compared to running it without the print code, which suggests that there is an issue with the system, profile, and/or printer setup on that vista computerThe enhanced code on a good computer should run almost the same speed with printing or without printingIf you like you can check it on a decent computer (win 7, 4+ MB Ram) and you will so it should workHope that explains it
Ok. I will check on a different computer. What about my MAC?