+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 201 to 225 of 225

Adding additional goals and objectives in a treatment plan using a form control button

  1. #201
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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.

  2. #202
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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.

    Please Login or Register  to view this content.
    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.

    Matthew

  3. #203
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    Hi Matthew

    Please upload your latest File...I feel I'm missing something...

  4. #204
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    John,

    Here you go. I temporarily assigned the refresh buttons code to a button on the Demographics page (Sheet1). Thanks.
    Attached Files Attached Files

  5. #205
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    Hi Matthew

    Did you notice, after one clicks Refresh Buttons the Hide Toolbar, Show Toolbar buttons no longer work?

  6. #206
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    Quote Originally Posted by jaslake View Post
    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

  7. #207
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    Hi Matthew

    For the Buttons Issue, try adding this Code to the ThisWorkbook Module
    Please Login or Register  to view this content.
    and modify Module9, Refresh_Buttons Code
    Please Login or Register  to view this content.

  8. #208
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    Hi Matthew

    For the Copy From Clipboard Issue, try this Code in the ThisWorkbook Module
    Please Login or Register  to view this content.

  9. #209
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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
    Attached Files Attached Files

  10. #210
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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 respond
    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
    I'm most interested in the field results of this
    the security of our formatting when pasting from the clipboard.

  11. #211
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    Hi Matthew

    Regarding this
    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.
    I 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 Code
    Please Login or Register  to view this content.
    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.

    Now, how is the Copy From Clipboard Code working? Does it satisfy?
    Please Login or Register  to view this content.

  12. #212
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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

  13. #213
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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
    Attached Files Attached Files

  14. #214
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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.
    Attached Files Attached Files

  15. #215
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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:

    Please Login or Register  to view this content.
    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.

    Matthew
    Attached Files Attached Files
    Last edited by moosetales; 04-09-2014 at 10:49 AM.

  16. #216
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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.

  17. #217
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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.

  18. #218
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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)

    Please Login or Register  to view this content.
    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.

    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
    Attached Files Attached Files

  19. #219
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    Hi Matthew

    I'll look at this ASAP...have some outdoor work to do as the weather changes...

  20. #220
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    Quote Originally Posted by jaslake View Post
    Hi Matthew

    I'll look at this ASAP...have some outdoor work to do as the weather changes...
    Thanks. Our snow is finally gone so outdoor work is calling my name too. It'll be good to get outside once again.

    Matthew
    Last edited by moosetales; 04-11-2014 at 08:37 AM.

  21. #221
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    Hi Matthew

    how did you figure that out?
    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.

    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 this
    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
    What is the "Sync" button on Sheet 6?

  22. #222
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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
    Attached Images Attached Images
    Last edited by moosetales; 04-11-2014 at 10:26 AM.

  23. #223
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    Hi Matthew

    Regarding this
    copying the STGs and Objs from Sheet2 to Sheet6 when the "Sync" button on Sheet 6 is clicked.
    I'd like you to do two things:
    1. Start a new Thread for this issue
    2. 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.

  24. #224
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Adding additional goals and objectives in a treatment plan using a form control button

    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

  25. #225
    Registered User
    Join Date
    06-06-2015
    Location
    Pakistan
    MS-Off Ver
    2013 Professional
    Posts
    13

    Re: Adding additional goals and objectives in a treatment plan using a form control button


+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hide a picture button placed in all sheets using a option button (form control)
    By lagaranch in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-26-2012, 10:33 AM
  2. ActveX Control Button Not Working But Form Control Button is
    By MikeTruth in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-31-2012, 05:04 AM
  3. Yearly Treatment Calendar based on treatment dates
    By ajay_psingh25 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-02-2011, 09:33 PM
  4. Button Form Control
    By qwertyas in forum Excel General
    Replies: 5
    Last Post: 08-26-2010, 08:41 AM
  5. Control button to add additional worksheets
    By cycle_simon in forum Excel General
    Replies: 1
    Last Post: 06-18-2007, 10:31 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1