Hi Matthew
How will the User execute the Print Entire Workbook? Will it be from a Button or from the Ribbon?
Hi Matthew
How will the User execute the Print Entire Workbook? Will it be from a Button or from the Ribbon?
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
John,
Right now it will be both but if I can some how prompt the following lines of code to work automatically after the form has been printed then 2010 users will be able to print the entire workbook and still have their buttons in place when they return to the workbook.
I placed the above code in a module and assigned the code to a button and it works to restore the buttons but it's a bit slow and I haven't found a way to prompt the code to run automatically after the form has been printed. If I can get this to work I will have the users print via the ribbon. Thanks again for your support on this project.Please Login or Register to view this content.
Matthew
Hi Matthew
Please upload your latest File...I feel I'm missing something...
John,
Here you go. I temporarily assigned the refresh buttons code to a button on the Demographics page (Sheet1). Thanks.
Hi Matthew
Did you notice, after one clicks Refresh Buttons the Hide Toolbar, Show Toolbar buttons no longer work?
John,
Yes, I noticed that. If you click the Esc button the Show and Hide buttons begin to work again. This was just a temporary placement of the code to see if it would do what I wanted it to do. I haven't been able to explore too much about where I might place it so it would do what I wanted while not messing with the other functionality.
Matthew
Hi Matthew
For the Buttons Issue, try adding this Code to the ThisWorkbook Moduleand modify Module9, Refresh_Buttons CodePlease Login or Register to view this content.
Please Login or Register to view this content.
Hi Matthew
For the Copy From Clipboard Issue, try this Code in the ThisWorkbook ModulePlease Login or Register to view this content.
John,
Been gone from home all day but saw earlier in the day that you had suggested some new lines of code. I just entered and/or modified our code in the form and best I can tell....PROBLEMS SOLVED!
Private Sub Workbook_BeforePrint(Cancel As Boolean) works so well that I've removed the "Refresh Button" from the form (for now) but left the code just in case I need it. I'm excited to get this latest version of the form into the hands of my "test pilots" to trial the stability of our buttons after print as well as the security of our formatting when pasting from the clipboard.
If the disappearing buttons after print "entire workbook" issues is truly resolved I am seriously removing the "Print to PDF" button. Can you see any reason to keep it?
As usual, thanks for putting your thoughts, talents and energy into the latest hiccups. With your blessing, I'd like to start a thread on this forum to share with others the solution you came up with for fixing the disappearing buttons issue in Excel 2010. Do you mind if I proceed? Thanks.
Matthew
Hi Matthew
Let me get back to you tomorrow...keep all in place for the moment...you can probably delete the "Refresh Button"...see no need for it.
Leave all else in place...don't do this as yet till I have a chance to respondI'm most interested in the field results of thisI'd like to start a thread on this forum to share with others the solution you came up with for fixing the disappearing buttons issue in Excel 2010the security of our formatting when pasting from the clipboard.
Hi Matthew
Regarding thisI have no issue with you starting a Thread describing your solution. However, calling it MY solution would not be honest nor fair. I wrote and added this CodeI'd like to start a thread on this forum to share with others the solution you came up with for fixing the disappearing buttons issue in Excel 2010.
But this Code is the essence of the solution and I did not write it...I added only one line to the Code.Please Login or Register to view this content.
So, should you decide to start a new Thread describing your solution, be certain to give proper attribution for the essence of the solution.Please Login or Register to view this content.
Now, how is the Copy From Clipboard Code working? Does it satisfy?Please Login or Register to view this content.
John,
The roll out is going well. Feedback is positive thus far. I provided the pilot group the new version of the form today so I hope to hear back throughout the week how the clipboard and print entire workbook fixes are going. I have tested and re-tested the fixes and have yet to break them so I'm cautiously optimistic that our solution is sound.
Regarding the other thread, I will most certainly give proper attribution (best I can recall) for the code used for the solution. I have great respect for the work you've done on this project and give you mountains of credit for the form's success so your contributions to the code (whether small or large) are huge to me.
I've been reviewing the State regulations that govern the delivery of the programs this form was created for and I may have uncovered an additional piece of data that we may be required to track. I'll be reviewing the rules and the form tonight to see if what we currently have in place is sufficient. I'll keep you posted. Thanks again.
Matthew
John,
Well........I was putting the form through some real life scenarios and I uncovered an issue. When I click on buttons to Add, Delete, etc. I get the following pop-up message, "Method of 'List' of object '_CommandBarComboBox' failed".
I did a little digging and came up with this:
http://answers.microsoft.com/en-us/o...7-75491708d919
This link above appears to speak to the same issue I've uncovered. I made an attempt to adjust the code but got over my head quickly. Thanks for taking a look.
Matthew
Hi Matthew
As I suppose you've probably surmised, the error was being thrown by the Copy/Paste Code newly introduced into the Workbook. I believe I've resolved the issue but you'll need to test thoroughly.
John,
I've run the form through it's paces and thus far it's looking great. The fix appears to be solid. I did run across an Run-time error 91 when attempting to delete Obj A under any of the STGs. I removed the following line of code and the Run-time error vanished:
The only thing this line of code did for us (I think) was to control where the cursor landed after an Obj is deleted. I'll keep you posted how things are going as the evening progresses.Please Login or Register to view this content.
Matthew
Last edited by moosetales; 04-09-2014 at 10:49 AM.
John,
Good morning. We've tested the form further and the "Method of 'List' of object '_CommandBarComboBox' failed" message persists. We tested the buttons but failed to test other aspects of the form and today one of our users uncovered the Method of......failed message when entering text and tabbing to the next field on Sheets1 and Sheets6. Thoughts? Thanks.
Hi Matthew
In ThisWorkbook Module, Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range), comment out the indicated Line of Code. Let me know...
Please Login or Register to view this content.
John,
That slight adjustment made all the difference in the world. If you don't mind, how did you figure that out? Is it something you just new was an issue or did you have to look line by line until you found the problem line? I tried but could not find a logical connection. Thanks.
While I was poking around I did however add a few lines of code to the Delete Obj button. Now, when the user attempts to delete Obj A they get a similar message to when they attempt toe delete STG 1. (see code below)
With this addition, I was able to add the following line back into the code with the above additon. (see below)Please Login or Register to view this content.
We've been playing host to a team of State reviewers this week and as a part of the review I introduced them to our new treatment plan format. They were pleased to see technology being put to use and very much so like the format and functionality of the form. One of the reviewers suggested I considered copying the STGs and Objs from Sheet2 to Sheet6 when the "Sync" button on Sheet 6 is clicked. I gulped and told him I'd take a look and see. His suggestion was aimed at ensuring that the user documented progress on the STGs and Objs. I've tried adjusting the code for the "Sync" button on Sheet6 but it's definitely an easy adjustment (by my estimation). My thought at this point is to leave it as is unless it's just a simple adjustment to the code.Please Login or Register to view this content.
I'm introducing the form to the last user in our test group and after that I'm expecting to launch the form to all 38 users (fingers crossed).
Thanks.
Matthew
Hi Matthew
I'll look at this ASAP...have some outdoor work to do as the weather changes...
Hi Matthew
As we both know, the Error Message was being raised by the Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Code and the Code was doing just as it was designed to do. I found the source of the issue by putting a Breakpoint in the Sub and then stepping through the balance of the Code.how did you figure that out?
In this instance, the Undo Stack was empty thereby raising an Error when it truly is NOT an error. The Code was simply displaying the Error Message...well, we don't care what the Error Message is...let's just get on with the getting on.
Regarding thisWhat is the "Sync" button on Sheet 6?One of the reviewers suggested I considered copying the STGs and Objs from Sheet2 to Sheet when the "Sync" button on Sheet 6 is clicked
John,
I've been living, breathing and sleeping this form and I forget that not everyone does the same
Button 11 on Sheet6 use to be called "Sync with ITP Goals...." so I still refer to is as such. Here's what it's called now.
BTW, thanks for the explanation on how you uncovered the error message. I'll brush up on the method.
Matthew
Last edited by moosetales; 04-11-2014 at 10:26 AM.
Hi Matthew
Regarding thisI'd like you to do two things:copying the STGs and Objs from Sheet2 to Sheet6 when the "Sync" button on Sheet 6 is clicked.
- Start a new Thread for this issue
- Mock up a Sheet2 and Sheet6 that demonstrates how this will look.
I'd expect this would not be a significant modification but I won't know until I see it.
The reason I'd like you to start a new Thread is because this one has been marked Solved for some time now and others are no longer looking at it. That's to your detriment...you're not getting the benefit of the wealth of knowledge available on this Forum.
Please PM me a Link to your new Thread and I'll look at it.
If you've found this thread to be of interest please continue to follow along and chime in here:
http://www.excelforum.com/excel-prog...ml#post3659340
Matthew
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks