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

**moosetales** · 02-25-2014, 01:16 AM

Good evening. I'm a week and a half into a project for work that I'm hoping will assist in warding off some decisions that would jeopardize the flow of necessary clinical information to front line staff. I am familiar with Excel but as of a week ago I had not tapped into the potential of Excel. Since then I've learned just how little I do know about Excel as well as the AWESOME functionality that the program offers. I am at the infant stage when it comes to coding (VBA) and beg your patience with me as I launch my first couple of questions regarding my current dilemmas with the forms I've created.

My unsolved issues thus far are as follows:

ISSUE

Instead of having the goals and objectives already in the form I would like to have buttons that would allow my clinicians to add as many goals and objectives as they'd like to the attached treatment plan. I'd like them to be able to click a delete button as well when they review the plan and need to delete a goal that's been met. When my team clicks into the second worksheet they would see rows 10-16 (below). They would then type a STG 1 and an Obj A. They then would click the Add Obj button and add as many objectives that they needed. Then when they clicked the add goal button a blank copy of rows 10-16 (including the Add Obj button) would pasted one row down and would update to read STG 2. Neither of these buttons would appear when the document printed.

Here is a screen scrape of what I'd like the attached form to look like before goals and objectives are added.

add goal.jpg

The original form is saved in an .xltm format but I saved it in an .xlsm format so I could upload it here.

HCT ITP (2-25-14) Abbr.xlsm

I found a thread on this forum that came close to answering my question but my attempts to pull out the coding that I needed was unsuccessful. Here's a link to the thread:

http://www.excelforum.com/excel-prog...matting-4.html

Given that this is my first post I thank you for your patience as I figure things out about the forum and how it all works. Thanks.

Matthew

**jaslake** · 02-26-2014, 01:58 PM

Hi Matthew

Welcome to the Forum!!!

I'd encourage you to include in your Workbook, Worksheets that display what you want the Output to look like. For me, looking at your Worksheets, I can't vision what it is you expect to happen.

**moosetales** · 02-26-2014, 07:57 PM

Originally Posted by jaslake

Hi Matthew

Welcome to the Forum!!!

I'd encourage you to include in your Workbook, Worksheets that display what you want the Output to look like. For me, looking at your Worksheets, I can't vision what it is you expect to happen.

Okay, here you go. I've attached the two documents that I'm attempting to add some functionality to. These have buttons included that I'll explain a little more about below. The Password for the sheets is "j".

RCS ITP (2-26-14) Abbr.xlsm

HCT ITP (2-26-14) Abbr.xlsm

RCS ITP (2-26-14) Abrr.xlsm

I've got 8 buttons on the second worksheet that I'm trying to assign macros to. They all will have the same basic functionality except they will be assigned different rows to copy and paste.

Add Parent Priority Button--copy rows 4-15 (including buttons that hover above those rows) and paste them on row 15 while maintaining a 1 row buffer between what was pasted and the next line of text (i.e. "How will parents be...."). When rows 4-15 are pasted into their new rows the Parent Priority should change from #1 to #2.

Delete Parent Priority Button--delete all rows between "Parent Priority" and the row following "Methods & Strategies to address..."

Add STG Button--copy rows 11-15 and paste them in the same column with one row between the row that the new STG is on and the row where "Methods & Strategies...." are entered. When rows 11-15 are copied and pasted the STG should change from 1 to 2.

Delete STG Button--delete the row that contains an STG and the corresponding rows that contain the Objs and "Methods & Strategies..."

Add Obj Button--copy row 12 and paste it on row 13. When the new line appears in row 13 (or whatever line it's pasting to) the Obj should change from A to B.

Delete Obj Button--if referring to Obj B that was just pasted in row 13 above the delete button would remove Obj B.

Add Objective Indicator Button--copy row 23 and paste it into row 24.

Delete Objective Indicator Button--delete what was just pasted into row 24 or whatever row an objective indicator was pasted to.

Summary--All rows that need to be copied and pasted need to paste blank with the exception of the drop down menus that are next to "from" and "to". Because I have people at work still using Excel 2007 I need to reference (data validation) data that's on the same worksheet (see O6:O13 and P6:P13). I will at some point be adding a drop down menu to cells in the "Pre-set Objective Indicator" column, I just haven't gotten that far yet. Now, you'll notice that the fourth worksheet (i.e.CSW Copy of Goals & Obj.) is locked. The cells on this sheet are to be filled from their corresponding cells on the second sheet (i.e. RCS ITP Goals & Objectives). The Client ID cell is already linked to the Client ID cell in the first worksheet.

HCT ITP (2-26-14) Abrr.xlsm

The buttons in this form are intended to do the same things as on the RCS form just with different row assignments.

I hope I haven't confused the heck out of you and if there is a more simplistic way of doing what I'm trying to do please let me know. I realize I am shooting for the moon here and appreciate you guys even being willing to take a look. I'm a social worker who loves technology and although I lack the training and knowledge, I am excited to learn. Thanks again.

Matthew

**moosetales** · 02-28-2014, 05:09 AM

I've been working on the following coding since yesterday evening at around 9:30 p.m. It's now 4:00 a.m. (

) I'm making some headway on my project but progress is SLOW. Through a healthy dose of trial and error I've finally been able to make two more buttons in my sheet work (sorta). The buttons in the form below that are PINK work to one degree or another.

HCT ITP (2-28-14) Active Buttons.xlsm As before, all worksheets have the same password of "j".[/B]

The "Spell Checker" buttons work perfectly with the exception that it still spell checks the "locked" cells even though the code is supposed to only spell check the "unlocked" cells; that's only a minor inconvenience though. Here's the code I'm using for the spell checker:

Please Login or Register  to view this content.

Now the "Add STG" and "Add Objective Indicator" buttons are functional and copy and paste just fine but I've run into some problems once I've entered text into the blank cells. Let me show the code I'm using for both buttons first.

Code for the "Add STG" Button:

Please Login or Register  to view this content.

Would someone be so kind as to give me a brief explanation of how the numbers in the code below work; i.e. what cell do they start from, are they referring to up 7 over 1, number of rows/columns copied, etc.? I know there's a pattern but I just haven't been able to figure it out. I got where I am by trial and error but I'm not confident enough of the concept to start the coding on my additional buttons just yet. Thanks.

Please Login or Register  to view this content.

Code for the "Add Objective Indicator" Button:

Please Login or Register  to view this content.

As I said, both "Add..." buttons copy and paste the rows I'm needing to duplicate. The problems that I'm having are as follows:

(1) When I add text into the unlocked cells and then click either of the "Add...." buttons the rows that are pasted are carbon copies of whatever I've already entered in the previous rows. I need the newly pasted cells to "reset" to a default state so I don't have to erase the text that's already been entered in the cells I'm copying from. One method I've thought of is to reference somehow in my code a carbon copy of this worksheet that has been copied to a separate "validation" sheet; one that would be hidden and not printable.

(2) When I copy and paste a new STG using the "Add STG" button, the new STG is a carbon copy of the original therefore the second STG still reads "STG 1". I need the code to add 1 to each goal (as well as subtract one once I get my "Delete STG" button up and running).

(3)The cell adjacent to "Due Date" auto-populates from a corresponding cell in the first worksheet (see formula below):

Please Login or Register  to view this content.

When I click the "Add STG" button the formula in the cell adjacent to the "Due Date" changes to the following formula which means the cell remains blank or will have inaccurate data:

Please Login or Register  to view this content.

How do I keep the formula with a cell when I'm using a macro to copy and paste?

(4) I'm having trouble finding coding language that will allow specific data from the second worksheet (HCT ITP Goals & Objectives) to auto-populate corresponding cells in the fourth worksheet (BHP Copy of ITP Goals & Obj.) The fourth worksheet is intended to be locked so only certain information gets transferred to that worksheet. Is there a simplistic way of coding this?

(5) And finally, I need the "Add Obj" and "Delete Obj" buttons to copy and paste along with the specified rows as indicated above. Should I link the buttons to the cells they are over or is there a specific line of coding that ties them to the copy and paste function?

I really appreciate any feedback that is available to help help me out.

Matthew

**jaslake** · 02-28-2014, 12:13 PM

Hi Matthew

Please add some Data to your Worksheets so we can visualize what the finished product will look like...

**moosetales** · 02-28-2014, 01:37 PM

Here you go. I've attached a mock treatment plan for you to get a better sense of what I'll be using it for.

Any where there is a button, I'll need to be able to add and delete fields so that the plans can be individualized. Thanks.

HCT ITP (2-28-14) Mock Treatment Plan.xlsm

Matthew

**moosetales** · 02-28-2014, 10:40 PM

I met with our entire team today and everyone is on board with shifting to this new way of approaching our data collection. I'm excited and a bit intimidated now that I've jumped this hurdle. I been asked if I might be able to roll these out for use across our 5 offices within two weeks.....gulp.....I said yes. So, the learning curve shall now continue.

**moosetales** · 03-02-2014, 12:58 AM

I've made some more headway in assigning code to some more of my buttons. Funny thing is, I'm finding different ways to do the same (or similar) thing. Here is the coding for three buttons on the second worksheet (Button 10, Button 11 and Button 14).

Button 10 (adds a new STG)

Please Login or Register  to view this content.

Button 11 (adds a new Obj)

Please Login or Register  to view this content.

Button 14 (adds a new line for a new Objective Indicator)

Please Login or Register  to view this content.

Here are my two questions:

1) Is one of the above ways of coding this function better than the other?
2) What lines of code do I need (and where do I place them) to get STG 1 to change to STG 2 or Obj A to change to Obj B when I click Button 10 or Button 11 to add new rows?

Here's the most recent version of the form I'm creating. Thanks for any feedback you might be able to provide.

HCT ITP (3-1-14) Active Buttons.xlsm

**jaslake** · 03-02-2014, 12:04 PM

Hi Matthew

Here's an example of Code to use for Buttons 10 and 15...it's in the attached.

Please Login or Register  to view this content.

You don't need to Unprotect and Protect Sheets...place this Code in the WorkBook Open Event...Save, Close and Reopen the Workbook.

Please Login or Register  to view this content.

**moosetales** · 03-02-2014, 03:18 PM

John,

Thanks so much. You definitely made some great headway. I've run through the Mock Treatment Plan attached and am able to follow the adjustments fairly well. Things are looking up for this document.

Regarding glitches, how about we tackle items that need tweaking 2 at a time. If that doesn't work for you please let me know.

ISSUE #1

When Button 10 is clicked a new STG is added the STG # increases by one (as desired) but the Obj does not paste as desired; pastes as Obj B or Obj C….instead of Obj A.

ISSUE #2

When Button 10 is clicked the drop down menus in the cells next to “from” and “to” do not copy and paste with the new STG.

I've attached version 2 of the Mock Treatment Plan with STGs added. Thanks.

HCT ITP (2-28-14) Mock Treatment Plan v2.xlsm

**jaslake** · 03-02-2014, 04:09 PM

Hi Matthew

To fix this

the Obj does not paste as desired; pastes as Obj B or Obj C….instead of Obj A.

Please Login or Register  to view this content.

I see no Dropdowns in the the cells next to “from” and “to” in the File you posted...

**moosetales** · 03-02-2014, 04:48 PM

John,

Oops. Try this one for the drop down lists. Thanks.

HCT ITP (2-28-14) Mock Treatment Plan v2.xlsm

**jaslake** · 03-02-2014, 05:06 PM

Hi Matthew

Nope, still no Drop Downs...

**moosetales** · 03-02-2014, 05:15 PM

Bummer. I've been at my son's cross country ski meet all day and must not have posted the correct version of the file before we packed up. I will re-post when home. For now, the lists are on the last worksheet of the workbook.

**jaslake** · 03-02-2014, 05:28 PM

Hi Matthew

I know what the List is...I see it...please apply it and I'll try to help. After you've applied it we'll make it a Dynamic Named Range such that, if there are any changes to the List, they'll be applied automatically.

**moosetales** · 03-02-2014, 05:30 PM

Thanks. Will be home in an hour and a half and will re-post then.

**moosetales** · 03-02-2014, 08:46 PM

John,

Here you go. Hope this works. I just noticed that when messing with this version of the form the drop down lists copied and pasted with the next line, at least I think they did. Let's see if it does it for you. Thanks.

HCT ITP (2-28-14) Mock Treatment Plan v3.xlsm

**jaslake** · 03-03-2014, 09:26 AM

Hi Matthew

I still see no Dropdowns (Data Validation) in your most recent attachment. However, if you put them in, they'll be copied.

**moosetales** · 03-03-2014, 09:40 AM

John,

Wow, I'm so confused about the lack of drop downs when you open it. I've attached a screen shot from the file when I open it and click on the cell next to "from" or "to".

drop down.jpg

While I figure out what's happening with that here's another glitch I've run into. When Button 15 is clicked and the specified STG is deleted the STG # does not recalculate which results in the plan having STG 1, STG 2, STG 4...instead of STG 1, STG 2, STG 3. I'll run into this when I begin work on the "Add Obj." and "Delete Obj." buttons so if you could specify the line of code that you adjust here I'll try my hand at it with the other buttons. Thanks.

Matthew

**jaslake** · 03-03-2014, 11:31 AM

Hi Matthew

Regarding this

When Button 15 is clicked and the specified STG is deleted the STG # does not recalculate

Replace your Button 15 Code with this

Please Login or Register  to view this content.

**moosetales** · 03-03-2014, 11:54 AM

John,

The new code for Button 15 worked perfectly (see below).

HCT ITP (2-28-14) Mock Treatment Plan v4.xlsm

My next issues is finding a way for the "Add Obj" and "Delete Obj" buttons to copy and paste when the button 10 ("Add STG") is clicked.

Thanks.

**jaslake** · 03-03-2014, 12:35 PM

Hi Matthew

If you need help figuring that out, let me know. Gotta tell you, don't know what this means

finding a way for the "Add Obj" and "Delete Obj" buttons to copy and paste when the button 10 ("Add STG") is clicked.

You'll need to show me...

**moosetales** · 03-03-2014, 12:49 PM

John,

I hope to get better as I go at communicating what I need. Thanks for your patience. Here are two screen shots of what I'm talking about.

This is a screen shot of what the form looks like now. It has three STGs added. On the far right of the form, just under the "from" and "to" cells are two buttons; "Add Obj" and "Delete Obj". These buttons need to be duplicated each time a new STG is added.

Current view (buttons not added).jpg

Here's a screen shot of what I'm hoping for.

Buttons Added.jpg

The "Add Obj" and "Delete Obj" buttons will allow me to add or delete new Obj under each STG.

Thanks.

**jaslake** · 03-03-2014, 01:16 PM

Hi Matthew

The communication issue is mostly me...I'm a Tactile/Kinesthetic learner...I learn by "seeing" and "doing".

Place in your Form the Objective Added just as if you Clicked the Add Obj Button...this is what I mean by "show me".

**moosetales** · 03-03-2014, 01:26 PM

John,

Here's a screen shot of what I'm hoping to accomplish as well as an updated version of the form with the Obj added.

Objs Added.jpg

HCT ITP (2-28-14) Mock Treatment Plan v5.xlsm

Thanks.

**jaslake** · 03-03-2014, 02:54 PM

Hi Matthew

Adding the additional Objectives blows the Code for Command Buttons 10 and 15 out of the water. I'll need to look at it...

**moosetales** · 03-03-2014, 03:53 PM

John,

If I'm understanding you correctly, to do what I'm wanting would require the current Code for Command Buttons 10 and 15 to be TOTALLY reconfigured? If so, let me take a look and see if there is an alternative that would involve less effort. I value your input and am sensitive to the fact this is a forum to address relatively small coding and other related issues. Thanks.

Matthew

**jaslake** · 03-03-2014, 04:33 PM

Hi Matthew

I think I have the Command Button 10, 15 issue sorted...not to worry. I'm currently tied in knots in getting your Add Obj, Delete Obj Buttons added in the CORRECT place...this is important as any Code will be driven based on their location.

I'll attach a File with the modified Code. The File will include a Named Range called mySTG located (for the moment) in

Formula:

Please Login or Register  to view this content.

**moosetales** · 03-04-2014, 07:35 AM

Originally Posted by jaslake

Hi Matthew

I think I have the Command Button 10, 15 issue sorted...not to worry. I'm currently tied in knots in getting your Add Obj, Delete Obj Buttons added in the CORRECT place...this is important as any Code will be driven based on their location.

I'll attach a File with the modified Code. The File will include a Named Range called mySTG located (for the moment) in

Formula:

Please Login or Register  to view this content.

John,

I could not get the v5.2 to open and function as expected. It kept attempting to reference code in an earlier version of my form and had a STG 2 off to the right of the screen. I think I've found a fairly straight forward workaround...see next post.

**moosetales** · 03-04-2014, 07:52 AM

John,

Concept #1

My thought in regards to the Button 11 and Button 16 is to treat them just like Button 10 and Button 15; all four buttons would be in a row and would drop down together each time lines were added above them. (see image below) Button 11 would have an Input Box where the following message would pop up when Button 11 was clicked, "Please Click on Column D "Please Click on Column D Of The Obj to be Added." or something like that. I attempted in the attached version (v7) of my form to code Button 11 to add a new Obj but no cigar.

add obj concept.jpg

Concept #2

Remove Button 11 and Button 16 altogether and just have Button 10 coded to copy and paste 4 Obj each time a new STG is added. This is the less preferred method at this time. I have coded Button 10 in the attached version of my form (v7) to copy and paste 4 Obj with each new STG.

Let me know what you think of if I've confused the heck out of you. Thanks.

**jaslake** · 03-04-2014, 09:48 AM

Hi Matthew

Four Buttons in a Row is workable. The Code now need not Find where to add the Obj Buttons and it's attendant Code.

Deleting an entire STG gets a bit wacky when additional Objs have been added. The Code needs to FIND the END of the STG as the end has changed because of the additional Objs.

The Offsets and Resizing no longer work as they've been hard Coded. However, the Code CAN find the end.

Let me look at it a bit...

**moosetales** · 03-04-2014, 11:00 AM

Originally Posted by jaslake

Hi Matthew

Four Buttons in a Row is workable. The Code now need not Find where to add the Obj Buttons and it's attendant Code.

Deleting an entire STG gets a bit wacky when additional Objs have been added. The Code needs to FIND the END of the STG as the end has changed because of the additional Objs.

The Offsets and Resizing no longer work as they've been hard Coded. However, the Code CAN find the end.

Let me look at it a bit...

Thanks, John. I'm quickly becoming more and more aware of the interconnectedness of Codes within worksheet. With that said, I wanted to throw an additional function out there that will ABSOLUTELY be a MUST in this form. We don't need to work on it at this time (unless it impacts what we're doing now) but I wanted to bring it to light.

The 4th worksheet (BHP Copy of ITP Goals & Obj.) is intended to be locked (both the worksheet and cells). By design the content on the 4th worksheet comes selectively from (linked) to specific cells on the 1st worksheet (HCT ITP Demographics) and the 2nd worksheet (HCT ITP Goals & Objectives). In practice, a clinician will enter data on the 1st and 2nd worksheets and then will print all 4 worksheets. The 4th worksheet will then be given to the front-line staff to use in the homes they work. By design, the 4th worksheet will NOT contain in Personal Health Information (PHI) therefore should the document go missing it would not cause a breach.

I'm attaching another version (v8) of my form that shows you what I need the 4th worksheet to look like; the 4th worksheet pulls info from the 1st and 2nd worksheets.

Thanks.

**jaslake** · 03-04-2014, 03:01 PM

Hi Matthew

Try the four Buttons in the attached. Play with the Four Buttons...try to break them...see if they do as you require. We get your Buttons working to your satisfaction then we'll deal with this...I have some ideas...

I wanted to throw an additional function out there that will ABSOLUTELY be a MUST in this form

**moosetales** · 03-04-2014, 04:04 PM

John,

All four buttons work! The only glitch I ran into occurred when I attempted to delete an obj (Button 16). I pasted what it does; deletes an obj. but does two separate things--1) leaves "Obj c" for instance to the left of a blank cell and you'll notice that two of the Obj. changed to numbers after I deleted an Obj.

It's looking great. Thanks.

Button 16 Glitch.jpg

**jaslake** · 03-04-2014, 05:28 PM

Hi Matthew

Alright, I'll get back to you...have Grandson homework and dinner to tend to.

**moosetales** · 03-04-2014, 06:25 PM

No problem. I've got four kiddos of my own so believe me, I know what you're talking about. I'm super excited about the progress thus far. Thanks.

**jaslake** · 03-05-2014, 12:48 PM

Hi Matthew

See if you can break this one...

**moosetales** · 03-05-2014, 05:04 PM

John,

I can't seem to break this one

! All four are working great...it's terrific. I just had a co-worker stop in and she couldn't break it as well. The pop up buttons seem to be fairly intuitive for people as well.

I've been working on Buttons 14 and 17 but for some reason when I click 14 it copies and pastes but no lined paste with it. What am I doing wrong? Thanks.

Matthew

**jaslake** · 03-06-2014, 09:59 AM

Hi Matthew

The attached has Code for Buttons 14 and 17. As before, I have no idea what you wish these Buttons to do except Add and Delete rows. You'll need to "show" me.

**moosetales** · 03-06-2014, 02:06 PM

John,

The following screen scrape shows what I need Button 14 to do when adding new objective indicators. Also, in the screen scrape, the row where column D is highlighted in blue is where the new objective indicator was pasted when I clicked Button 14 to add a new objective indicator. I need the space for the new objective indicator to paste at the end not in the middle of the rows of objective indicators.

Button 14 and 17.jpg

Button 17 works when deleting the last row however, when you attempt to delete a row somewhere above the last row I get a dialogue box that pops up...see below.

Button 17.jpg

I've attached the form below with the code adjustments I made to Button 14. Thanks.

**moosetales** · 03-06-2014, 05:21 PM

John,

Hi. I just figured out a pattern with Button 17....when deleting rows using Button 17 sometime you have to click column D and sometimes column C to get the row to delete. Hope this makes sense. Thanks.

Matthew

**jaslake** · 03-06-2014, 05:27 PM

Hi Matthew

Somehow Buttons 14 and 17 got moved up one Row (could have been the Code) and was causing this and it's been fixed (please note...ALL the Add Code runs off the location of the Buttons...if they're moved, the Code needs modified)

the row where column D is highlighted in blue is where the new objective indicator was pasted when I clicked Button 14 to add a new objective indicator

This was by design as I had no clue what was going to be entered in the Cells...it's also been fixed

when you attempt to delete a row somewhere above the last row I get a dialogue box that pops up

I assume the User will be entering the Data in both Columns C and D of the Objective Indicators??? Please tell me this is so...I don't know of any Code that can complete those cells.

**jaslake** · 03-06-2014, 05:32 PM

Hi Matthew

Regarding this

when deleting rows using Button 17 sometime you have to click column D and sometimes column C to get the row to delete

Absolutely NOT. The way the Code is written, the User MUST click on Column D. I'll make the necessary revision such that the User can click on either Column. I had no idea which Cells were locked so didn't program for that. Good catch.

**moosetales** · 03-06-2014, 05:41 PM

Yes, the user will be entering data in both columns c and b. I'll check the revisions out when I get home in a bit. Thanks so much. I may have some how moved the buttons and screwed it up on my end. Sorry if it was my error.

**moosetales** · 03-06-2014, 06:26 PM

I meant to say C and D. Sorry.

**moosetales** · 03-06-2014, 07:11 PM

John,

I'm home and ALL the buttons on sheet 2 are working GREAT. I'm so excited. I can't tell if I'm more excited that the form is coming to life or because I'm learning a little bit more about Excel and VBA every time you respond....quite likely it's both.

I discovered a minor glitch that may be connected to the fact I have drop downs in column D in the Objective Indicator section of the form. Here's a screen shot to go along with the description of the glitch...

glitch.jpg

Try this...

1) single click into one of the rows in column D
2) click Button 17
3) after the "Delete Row" notification box pops up click the same row in column D that you clicked on in step 1 (it's the one with the drop down arrow to the far right)
4) when you click on the row in column D in step 3 nothing appears in the notification box field; typically something like $D$29 appears in this field.
5) click into a row in column D that's above or below the row you clicked into in step 1
6) when you click on the new row in step 5 the field in the notification box shows the corresponding column and row and you are allowed to delete that row

This is not a big deal at all unless someone accidentally clicks the row in column D before trying to delete it and then gets frustrated because they can't figure out why it won't delete. If it can't be fixed I can live with it, I'll just make sure everyone is educated to the process of deleting the row.

Thanks again. BTW, I've been doing as you suggest in your profile and it sure looks like fishing is a HUGE past time in the area you live. We sure do love our fishing (and hunting) up here in Maine.

**jaslake** · 03-06-2014, 08:28 PM

Hi Matthew

I don't pretend to know why but I don't see these on the File you posted

it's the one with the drop down arrow to the far right

This has been fairly consistent that I've not been able to see your Data Validation Dropdowns and I don't know why. I've been able to set them myself and feed them back but your's seem not to come through.

So, what is the Data Validation Range we're talking about for these cells?

**moosetales** · 03-06-2014, 08:48 PM

Hmmmmm. That's strange. The only time it hasn't worked for others here at work is when they are on Excel 2007. Apparently prior to Excel 2010 you could not reference data stored on a different worksheet.....is that correct?

Here's where my data is stored....Sheet 1.

Data Validation (sheet 1).jpg

**jaslake** · 03-06-2014, 09:24 PM

Hi Matthew

No, this is not the case

prior to Excel 2010 you could not reference data stored on a different worksheet

Show me your Data Validation Rule...I don't really have a clue why the Worksheet is losing it...

**moosetales** · 03-06-2014, 09:46 PM

Here you go.

data validation rule.jpg

**moosetales** · 03-07-2014, 02:24 AM

John,

Okay...so I adjusted the way I did my Data Validation...see below.

Data Validation (Sheet 1).jpg

I saw that you had linked cells with Named Ranges so I followed suit. It seems to be working great.

I originally had planned to create two separate treatment plans for two different clinical programs BUT with all the efforts that went into creating just one I've decided to make one plan that is customizable for each program...see attached.

My next BIG hurdle is to get worksheet #5 to auto populate from other fields in other worksheets (e.g. client ID would come from the first worksheet, STGs & Objs would come from worksheet #3, etc.) I've attached an updated version of my form and I've mocked up the treatment plan so that worksheet #5 appears to have been auto populated as I'd like it to do. If you need me to be more specific just let me know. Thanks.

HCT-RCS ITP V1.1.xlsm

**jaslake** · 03-07-2014, 02:31 PM

Hi Matthew

See if the Code in the attached works for this

My next BIG hurdle

**moosetales** · 03-07-2014, 03:14 PM

John,

I opened the file you attached and it's not doing what I need it to do. I tried adding a STG in worksheet #3 (using button 10) and hoped it would have automatically appeared in worksheet #5 but it did not. I also noticed that worksheet 5 was protected but certain fields were unlocked. I need worksheet #5 to be locked AND all fields locked so the only information that appears on worksheet #5 is the information that's been typed in worksheets #1 and #3.

I sure hope this makes sense....I truly hate wasting your time

with red-herrings or wild goose chases. Thanks again.

**jaslake** · 03-07-2014, 04:47 PM

Hi Matthew

Locking the Cells is a detail at the moment.

I've added a Button(18) to Sheet3 Add your STG's and Obj's to your hearts content...click Button(18)...the Data is pulled in.

Try it...

**moosetales** · 03-07-2014, 09:21 PM

Originally Posted by jaslake

Hi Matthew

Locking the Cells is a detail at the moment.

I've added a Button(18) to Sheet3 Add your STG's and Obj's to your hearts content...click Button(18)...the Data is pulled in.

Try it...

Just before leaving work I gave it a try and based on my quick try it works! I'll play with it a bit and run it through its paces. BTW, I'm working on my coding for the buttons on the second sheet and I have the add button working but the outline of the border won't paste. Why is that? I'm excited to be trying my hand at adapting the codes you've shown me for other uses. Thanks.

**jaslake** · 03-07-2014, 10:37 PM

Hi Matthew

You have me doing a tap dance. Please see this with regard to how to refer to Sheets...you're referring to Sheet6 I presume...?

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

**moosetales** · 03-08-2014, 09:09 AM

John,

Yes, you are exactly correct....I'm referring to Sheet6. I'm learning new things every time I turn on my computer. Sorry for the confusion.

I just finished reading the attachment (and a few other articles I found on the subject) and have gone back through your code to see how you used the Sheet names. Pretty cool.

Couple questions for you:

1) What prevents the border around a cell not to copy and paste when lets say I press Button 117 on Sheet6?
2) When adding a function or formula (see screen shot from Sheet7 below) can you use the Sheet name instead ** what's on the tab to reference the worksheet? This would be great in case I ever need to change the tab name.

fx.jpg

Thanks

**jaslake** · 03-08-2014, 11:17 AM

Hi Matthew

Try this for the Button117 Click

Please Login or Register  to view this content.

**jaslake** · 03-08-2014, 11:35 AM

Hi Matthew

I don't believe this is possible in a Worksheet Formula. However, I'm not a Worksheet Formula expert. That would be an interesting question for a Thread devoted specifically to that question.

When adding a function or formula (see screen shot from Sheet7 below) can you use the Sheet name instead ** what's on the tab to reference the worksheet? This would be great in case I ever need to change the tab name.

**moosetales** · 03-08-2014, 11:40 AM

John,

I replaced the code for Button 117 with what you provided above and here's what I got:

syntax error.jpg

Also, I need each row that's added, by clicking Button 117, to increase by one number (e.g. 1, 2, 3, 4, 5, etc.).

**moosetales** · 03-08-2014, 11:41 AM

John,

I will get a thread started. Thanks.

**jaslake** · 03-08-2014, 11:45 AM

Hi Matthew

That should be

Please Login or Register  to view this content.

Don't know why it pasted with the asterics...

I'll look at this

each row that's added, by clicking Button 117, to increase by one number

**moosetales** · 03-08-2014, 11:57 AM

John,

Here's a screen scrape, ** your most recent post, as it appears when I view your post on my computer.

of.jpg

I just started the other thread we spoke about and I noticed that when "**" is used by itself or in a word "**" is replaced with "**". I'm going to shut my system down and reboot to see if maybe it's on my end. I'll get back to you in a few minutes.

Matthew

**jaslake** · 03-08-2014, 12:08 PM

Hi Matthew

I think the asterisk thing is a Forum Issue...I'll do some inquiring...

**jaslake** · 03-08-2014, 12:13 PM

Hi Matthew

This issue has already been reported...

http://www.excelforum.com/newreply.p...reply&t=995025

**jaslake** · 03-08-2014, 01:25 PM

Hi Matthew

This attachment has Code for Buttons 117 and 118 and all Cells have been Locked in Sheet3...

**moosetales** · 03-08-2014, 01:35 PM

John,

I'm **f to watch a play that one ** my kiddos is in...I'll check out the new coding and get back to you once I'm back in touch with the internet. Thanks again.

Matthew

**moosetales** · 03-09-2014, 01:23 AM

John,

I'm home and have had some time to take the form for a spin. I made some adjustments to the code, here and there, to address a few things that came up. We have accomplished all but one major hurdle but before the addition ** the last Button (Button 8 on Sheet6) I've run into a few glitches and obstacles:

(1) I can't seem to get the coding correct so the form will open on Sheet1 every time it's opened. I pulled some coding from this forum but when/where I've placed it I can't get it to work. Here's the two pieces ** code I've tried...

Please Login or Register  to view this content.

(2) When Button 118 is clicked you can click on anything in column B and the cell you clicked into will delete...even if the cell is below Button 118 and NOT one ** the Presenting Problems.

(3) When you click Button 118 and then hit cancel without clicking one ** the cells in column B you get the following:

Click Cancel.jpg

Then when you click Button 118 and click OK without clicking into a cell in column B this is what you get:

Click OK.jpg

Button 15 seems to work great, without the pop up boxes as with Button 118.

(4) I've tried various versions ** code for my spell checker buttons but have yet to code them correctly so ONLY unlocked cells are checked. What am I missing?

Thanks once again for helping out. You'll see in the latest version ** the form I've cleaned it up and made it look "pretty" I believe we are VERY close to getting the form into the hands ** the front line staff for a trial run.

**jaslake** · 03-09-2014, 02:59 PM

Hi Matthew

This Code seems to work for this issue

the form will open on Sheet1 every time it's opened

Please Login or Register  to view this content.

This Code seems to work for this issue

ONLY unlocked cells are checked

Please Login or Register  to view this content.

Issues 2 and 3 are due to your Formating Change...you've removed the # from your numbering scheme. If you look at the Code you'll see the Code is using the # sigh as a locator...I'll need to look at this. Please get this in Final Form. Tweeks such as the # sign removal, if tied to the Code, will cause the Code to not work.

**jaslake** · 03-09-2014, 03:37 PM

Hi Matthew

I've added No (Number) to the Numbering Scheme...try it please...

**moosetales** · 03-09-2014, 05:28 PM

John,

Fantastic. I've attached what I believe is getting VERY close to being a final draft. I'm currently working with the form, using real data, to work out the bugs. Thanks again. I'll be in touch once I do my best to "break" the code in the form

. My biggest concern going forward will be to make sure the form is as STABLE as possible due to the fact the end user will NOT be readily willing or able to problem solve glitches.

**jaslake** · 03-09-2014, 05:37 PM

Hi Matthew

I'm going to suggest a different approach to your Buttons 19 and 20 on Sheet 6. Have the Code mostly surrounded. Get back to you in a bit...

**jaslake** · 03-09-2014, 06:20 PM

Hi Matthew

Try Button 8 in Sheet 6...see if it does as required...

**moosetales** · 03-09-2014, 09:24 PM

John,

The Button 8 works as envisioned. That turned out splendid.

On another note.....I think I've succeeded in overwhelming the form. I entered a large number of mock objectives into the ITP Goals and Obj sheet. When I clicked Button 10 to add a second STG the text filled in behind the buttons and now I can't straighten things out. Ideas?

Example of glitch.jpg

Also, when I open the form the Spell Check buttons keep shifting to the left on Sheet1.

Spell Check Button.jpg

Thanks.

Matthew

**jaslake** · 03-09-2014, 11:23 PM

Hi Matthew

Far as I can tell, your posted File doesn't demonstrate the issue...

**moosetales** · 03-09-2014, 11:43 PM

John,

Okay, I can't get it to do the thing it did where it pasted behind the buttons, as in the screen scrape above, but you can see in the file attached the code is messing up somewhere since it's copy and pasting some of the content from previous Obj and the "methods" and "how will parents" headings stop copy and pasting when adding new goals. Is it something I'm doing incorrectly? Thanks.

**moosetales** · 03-10-2014, 01:29 AM

John,

I'd like to use coding that would allow the users of my form to enter text into merged cells and have the merged cells expand in line with the number of characters entered. Sheet1 has at least four rows (i.e. 15, 17, 19, 21) that I'd like to use text wrap and auto row height with. I found the following code and have attempted to apply it but to no avail.

Please Login or Register  to view this content.

The code worked...kinda. The rows I mentioned above expanded to fit the amount of text that I entered but then the field was locked and I could not get back into the field unless I unprotected the worksheet and changed the properties of the merged cell range.

Any thoughts? Thanks

**jaslake** · 03-10-2014, 10:12 AM

Hi Matthew

I found the cause of this issue and it appears to be corrected in the attached. You'll notice I've added a Sheet to the Workbook called STGTemplate. This Sheet can be hidden.

the code is messing up somewhere

I fail to understand the purpose of the Code in Post 77...what is it you're trying to accomplish?

Please Login or Register  to view this content.

**moosetales** · 03-10-2014, 11:40 AM

John,

On Sheet1 I have several rows where I've merged cells (15, 17, 19, 21). Because the height of the row will no longer expand with the text I had to set the row height to a set height. If the user enters too many characters the full text does now show. I found the above macro on line which claims that it allows rows with merged cells to automatically increase or decrease to accommodate more or less text.

In a nut shell, when it comes to auto fit row height and text wrapping, I would like for the rows that have merged cells to function as much like the non merged cells as possible. Hope I'm coming through clearer than mud

.

Matthew

**jaslake** · 03-10-2014, 12:35 PM

Hi Matthew

Do you still have the Link to where you got the Code?

**moosetales** · 03-10-2014, 12:37 PM

Originally Posted by jaslake

Hi Matthew

Do you still have the Link to where you got the Code?

Here you go.

http://www.mrexcel.com/forum/excel-q...ged-cells.html

Thanks.

**jaslake** · 03-10-2014, 12:50 PM

Hi Matthew

Add the indicated line of Code...

Please Login or Register  to view this content.

**moosetales** · 03-10-2014, 02:45 PM

John,

It worked PERFECTLY! I'm getting great feedback from my crew. I'm compiling a list of a few glitches that people are running into and I'll do my best to address them on my own but may reach out for a little more help. I never would have imagined that we'd get this far with this form. I realize just how much I have to learn about code AND I'm super excited about how much I've learned from watching you work. The credit is ALL yours. I'll be in touch.

Matthe

**jaslake** · 03-10-2014, 03:51 PM

Hi Matthew

It's been an interesting project...hope your Crew gets along well with it. In my working career one of the things I enjoyed most was providing tools such that MY job and those of MY Crew were easier, more productive and therefore more enjoyable. My wish for you is that you and your Crew have a similar payback for our efforts.

Remember, when you tweak you need to be mind-full of the impact on the Code of those tweaks. A large segment of the Code is built on finding "things". Once found, the Code is referencing off of the Cell Address of those "things" with Offsets.

For example, if you insert a new Row and it impacts the Code, the Offset will need to be changed...if you change #1 to 1 the Code can no longer find the # sign.

Best of luck to you and your Crew...need my help...holler.

**moosetales** · 03-10-2014, 08:10 PM

John,

Thanks for the words of encouragement. You must have seen the future when you typed...

Originally Posted by jaslake

Remember, when you tweak you need to be mind-full of the impact on the Code of those tweaks. A large segment of the Code is built on finding "things". Once found, the Code is referencing off of the Cell Address of those "things" with Offsets.

For example, if you insert a new Row and it impacts the Code, the Offset will need to be changed...if you change #1 to 1 the Code can no longer find the # sign.

I made a few tweaks to the form and all of the sudden everything went haywire. I stopped and thought and your words came to me....I reviewed the changes I'd made and bingo, I'd deleted two lines in one of the worksheets. Replaced the lines and everything was copacetic.

I've run into a hiccup (minor but it catches the eye) on Sheet3. Here's a screen shot.

Glitch on Sheet3.jpg

In Sheet3 the data in H25 is aligned to the right of the cell instead of the left. Also, the font defaults to Arial and not Century Gothic. I've been over the code again and again and can't find where that is getting tripped up. Ideas?

Thanks.

**moosetales** · 03-11-2014, 01:21 AM

John,

Yup, I ran into another issue that I for see could cause some of my crew problems. It does not have to do with the coding but rather Excel itself...I think.

My crew will likely want to copy and paste or cut and paste from another form into this form. Before applying the code that I'll paste for you below the users were able to click once into a cell, click paste and the data being pasted would delete the formatting and everything else that was in the cell. I applied the code below to the Workbook and it seems to prevent the user from doing what I just described while still allowing the user to double click into the cell and then paste their data without the data overriding any of the formatting.

Please Login or Register  to view this content.

Here's a link to the site where I found this code:

http://www.mrexcel.com/forum/excel-q...al-values.html

Questions

1) Do you see any potential conflicts between the above code and the coding you've used in our form?
2) Is there a better solution to my problem than using the above code?

Thanks again.

Matthew

**jaslake** · 03-11-2014, 08:43 AM

Hi Matthew

For this

In Sheet3 the data in H25 is aligned to the right of the cell instead of the left. Also, the font defaults to Arial and not Century Gothic

In Button1818_Click add the indicated lines of Code

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

**jaslake** · 03-11-2014, 08:46 AM

Hi Matthew

Questions
1) Do you see any potential conflicts between the above code and the coding you've used in our form?
2) Is there a better solution to my problem than using the above code?

I have no clue...try it...if it works, it works...if not then it dosen't...

**moosetales** · 03-11-2014, 11:35 PM

John,

I added the new lines of code and everything is lined up as expected. Thanks.

I'm a bit discouraged....we ran into a Run-time error today and I'm not sure if it's a glitch that's been lurking in the shadows or it's something I've done.....I hope it's the former. The error is "Run-time error 424". I've isolated what triggers the error.

Step 1) Click on Button 17
Step 2) Click on the Cancel button or the red X at the top right of the dialogue box

There error dialogue box appears and the user has the option the "End" or "Debug". If the click End, all is good but if they click Debug the user is routed to the VBA code and the skies the limit as to what they can change.

Thoughts? I'm hoping I didn't cause this since I know it means time and energy on your end. Thanks.

**moosetales** · 03-12-2014, 08:55 AM

Originally Posted by moosetales

John,

I added the new lines of code and everything is lined up as expected. Thanks.

I'm a bit discouraged....we ran into a Run-time error today and I'm not sure if it's a glitch that's been lurking in the shadows or it's something I've done.....I hope it's the former. The error is "Run-time error 424". I've isolated what triggers the error.

Step 1) Click on Button 17
Step 2) Click on the Cancel button or the red X at the top right of the dialogue box

There error dialogue box appears and the user has the option the "End" or "Debug". If the click End, all is good but if they click Debug the user is routed to the VBA code and the skies the limit as to what they can change.

Thoughts? I'm hoping I didn't cause this since I know it means time and energy on your end. Thanks.

John,

I did some assessing of the Run-time Error issue and it appears to have been an issue that was lying dormant, at least back to V1.8. Thanks.

Matthew

**jaslake** · 03-12-2014, 09:07 AM

Hi Matthew

I know what the issue is...rookie mistake...two or three lines of Code...get back to you in a bit..

**jaslake** · 03-12-2014, 09:12 AM

Hi Matthew

Change Button17_Click as follows

Please Login or Register  to view this content.

**moosetales** · 03-12-2014, 05:14 PM

John,

Can't tell you how glad I am it's not something I did. Button 17 works like a charm.

I learned something interesting today...when using the form in Excel 2007, the Button labeled "Button 8" on Sheet6 is shifted to the left of it's original position and is transposed on top of the words in the same row. This is NOT something we need to fix since most (if not all) of my crew will be using Excel 2010 but I thought it was interesting. BTW, although Button 8 is shifted it retains it's full function.

The excitement here at the office is building as more and more people are hearing about the "new" form that's due to come out very soon. As usual, thanks so much.

**jaslake** · 03-12-2014, 05:46 PM

Hi Matthew

Actually, I've used Excel 2007 through this whole process

**moosetales** · 03-12-2014, 08:19 PM

Well, I guess this means it's a local machine issue then. I've pasted two screen scrapes of two glitches that appear when the form is opened on one of our user's machines.

Originally Posted by jaslake

Hi Matthew

Actually, I've used Excel 2007 through this whole process

#1 This screen scrape shows the Sync Goal button shifted to the left.

Syng Goals Button.jpg

#2 This one is subtle but the "P" in the ITP at the very top (first radio button) is missing. It appears that the second radio button is covering the "P".

P missing in ITP.jpg

I'm ONLY attaching this info in the event others on the forum open the form and experience the same thing. It appears this is likely just an issue on the one machine. Thanks.

**jaslake** · 03-13-2014, 05:12 PM

Hi Matthew

If this resolves your issue, please mark your Thread as solved. Select Thread Tools from the menu link above and mark this thread as SOLVED.

A few Style Points will be appreciated (Click the Star Button at the lower left of ANY of my posts

**moosetales** · 03-13-2014, 06:45 PM

John,

I just ran into an issue that I may or may not be able to do anything about. Try this with the attached version of the form and see what happens:

Step 1-on Sheet1 click once into E18 (next to Unmet Needs).
Step 2-Click ctrl X
Step 3-Click once into E24 (next to Discharge Criteria) on Sheet1
Step 4-Click ctrl V

If your result is the same as mine after Step 4 the cells E18, F18, G18, H18, and I18 are now empty and all formatting for those cells has been lost. Would this be considered a limitation of the program or an issue to be addressed via coding?

I'm working on an instruction guide that will go out to my crew when this form is launched so I just need to figure out whether or not I need to steer them clear of CUT and PASTE when moving data from one cell to the next within the form. Thanks.

**moosetales** · 03-13-2014, 06:58 PM

John,

I just ran into an issue that I may or may not be able to do anything about. Try this with the attached version of the form and see what happens:

Step 1-on Sheet1 click once into E18 (next to Unmet Needs).
Step 2-Click ctrl X
Step 3-Click once into E24 (next to Discharge Criteria) on Sheet1
Step 4-Click ctrl V

If your result is the same as mine after Step 4 the cells E18, F18, G18, H18, and I18 are now empty and all formatting for those cells has been lost. Would this be considered a limitation of the program or an issue to be addressed via coding?

I'm working on an instruction guide that will go out to my crew when this form is launched so I just need to figure out whether or not I need to steer them clear of CUT and PASTE when moving data from one cell to the next within the form. Thanks.

**jaslake** · 03-13-2014, 08:39 PM

Hi Matthew

And Ctrl + X runs what Code?

**moosetales** · 03-13-2014, 08:42 PM

Originally Posted by jaslake

Hi Matthew

And Ctrl + X runs what Code?

It doesn't run a code. It simply cuts the content of the cell and that when you paste it in a different cell all the formatting in the cell that you just cut from is gone

**jaslake** · 03-13-2014, 08:52 PM

Hi Matthew

Please explain what it is you're trying to do...and why...

**moosetales** · 03-13-2014, 09:14 PM

I need to block users from being able to cut, copy or paste UNLESS they have double clicked into a cell, otherwise the user will be able to override or remove the formatting of the cell (remove shading, unmerge cells, etc.).

The following screen shot is of Sheet1. The only thing I had to do to mess it up this bad was click once into cells, hit cut, click into another cell and hit paste. Then when I went back to the cells where the shading had been removed I was able to type text well outside the margins of the cells even though the worksheet was still protected.

oops.jpg

Maybe I just need to tell the user NOT to copy, cut or paste? Thanks.

P.S. I'm beginning to feel as though I've overstayed my welcome. Please PM me if that's the case. I just hope others are also benefiting from the troubleshooting you are doing on this project. Thanks.

Matthew

**jaslake** · 03-13-2014, 09:29 PM

Hi Matthew

In what circumstances SHOULD the User be able to Cut, or Copy or Paste? And WHY?

**moosetales** · 03-13-2014, 10:45 PM

Hey John,

There are two primary scenarios when my crew might cut, copy and paste.

#1

We currently complete this form using a Word template. When this Excel form goes live, my crew will need to cut or copy and paste their client's goals from the Word template into this new Excel form.

#2

Once my crew begins using the Excel form there will be times that they enter information into the incorrect cell or something and will need to copy or cut from one cell and paste into another. They might need to copy or cut and paste a part of the cell or the entire content of a cell.

Hope this makes sense.

Matthew

**moosetales** · 03-14-2014, 12:48 AM

John,

Here are two different codes that others have used in an attempt to address the cut, copy, paste issue. They are currently not in the version (V1.19) of the form I've enclosed in this post.

Here's a link to the site where I found the fist grouping of code:http://www.mrexcel.com/forum/excel-q...al-values.html

Please Login or Register  to view this content.

The second grouping of code below can be found here: http://www.ozgrid.com/VBA/disable-cut-copy.htm

Please Login or Register  to view this content.

I attempted to work both sets of code into the form but was unsuccessful so I removed them altogether.

So the question now is....does this help or confuse matters. If it confuses matters please disregard. Thanks.

Matthew

**jaslake** · 03-14-2014, 09:32 AM

Hi Matthew

I think if you add this Code to ThisWorkbook Module, Save and Close the Workbook...then Reopen. The Code allows the User to use Copy/Paste but not Cut/Paste. It gives them a Message "Please DO NOT Cut and Paste. Use Copy and Paste; then delete the source." I believe that addresses your issue...

Please Login or Register  to view this content.

**moosetales** · 03-14-2014, 12:44 PM

John,

The additional code above gets us EVER so close to a fix; I've added an updated version of the file to this post. In trying it out I discovered two glitches that still persist.

#1

Following the steps below will demonstrate better than I can explain what produces the glitch. Try the following:

While on the Demographic Sheet....
Step 1-Type "I love fish" into E6
Step 2-Single click into E9
Step 3-Single click back into E6
Step 4-Click copy
Step 5-Single click into E9
Step 6-Click paste
At this point an warning message will appear...click OK...now cells E9:I9 are highlighted
Step 7-Click copy
Step 8-Single click into E18
Step 9-Click paste
After clicking paste "Parent's/guardian's Name" is pasted into G18 and the cells that had been merged (E18:I18) are no longer merged.

I need the user to be blocked from being able to COPY a range of non-merged cells (some locked and some unlocked) and then accidentally paste it into a cell that is comprised of merged cells.

#2

When copying data from a source outside the Excel workbook and then single clicking into a destination cell on any given worksheet and then clicking paste the user is able to remove all the formatting that was in the cell prior to pasting. Also, once the user pastes content into the destination cell that cell is locked and no additional data may be entered.

I need the user to be blocked from pasting data from an outside source into a cell UNLESS they've double clicked into the destination cell.

Thanks.

BTW.....since I can't add anymore to your "Reputation" for the time being I wanted the thank you for your patience and continued tenacity on this project.

**jaslake** · 03-14-2014, 04:13 PM

Hi Matthew

Try the Code in the attached for Issue #1

For Issue #2...please post an example...I choose not to recreate your working environment.

Hold on for a moment...have a hitch in the giddyup...

**moosetales** · 03-14-2014, 05:11 PM

John,

Well, here you go and thanks for asking me to do this because I found a pattern. I copied and pasted "Hoping for Spring!" from a Word document into the Excel form (using only a single click on the cell in Excel) BUT found that I could only do this for select cells (see below):

RESULTS of copy paste from outside source.jpg

When I attempted to repeat the same copy/paste action in any of the green shaded cells in the screen scrape above I got the following pop up boxes in the order they appear below:

1st box.jpg

2nd box.jpg

3rd box.jpg

With this new information, I'm thinking that maybe there's something I've done to these particular cells that is allowing the copy/paste issue to happen. Hmmmmmm.

**moosetales** · 03-14-2014, 05:18 PM

John,

Just looked back at the form and realized that the difference between the cells I could paste into (and remove the formatting) and the cells I could not paste into is merged vs. non-merged cells. I was able to replace (with paste) the content and formatting in the individual cells that had not been merged with another cell (E9:E14). Not sure if this realization helps in any way but it's a pattern. Thanks.

**jaslake** · 03-14-2014, 05:44 PM

Hi Matthew

I'll work on this later tonight and tomorrow. What I was asking for was not pictures but the File you're copy/pasting from. Give me an example File(s).

**moosetales** · 03-14-2014, 06:15 PM

Oops. Once I'm home I will post a sample of what I'm copying from. Thanks.

**moosetales** · 03-14-2014, 07:47 PM

John,

Here you go. Thanks.

**moosetales** · 03-15-2014, 02:21 PM

John,

Good afternoon. I found a thread that you started a while back that hypothetically speaking might address our issue with the cut, copy paste....maybe? Here's the thread:

http://www.excelforum.com/excel-prog...into-cell.html

I may in fact be ALL wet on this one but I was thinking that if we required the user to double click into the cell before they could cut, copy or paste that would eliminate the issue of users being able to cut or copy/paste the cell content (including formatting). If this would work, would it work for cells that had drop down lists as well? Thanks.

Matthew

**jaslake** · 03-15-2014, 03:00 PM

Hi Matthew

I'm still working on Issues 1 and 2 from Post #107. Thought I had Issue 1 resolved but it hiccups on me...still looking/researching.

Regarding the Link to one of my old posts...I don't think it's applicable...that had to do with bringing up a Calendar Control...

I'll get back to you, but, is Issue 2 a short term File Conversion Issue? If so, there is a way to do this quite easily, manually, without Code. Please explain...

**moosetales** · 03-15-2014, 03:08 PM

John,

Not sure what you mean by Short term file conversion? I'm not up to speed with my terminology

Originally Posted by jaslake

is Issue 2 a short term File Conversion Issue? If so, there is a way to do this quite easily, manually, without Code. Please explain...

**jaslake** · 03-15-2014, 03:27 PM

Hi Matthew

I take it you're converting from Word to Excel for these Treatment Plans. In doing so you'd need to transfer existing Word Treatment Plan Data to the Excel Format.

Is that what all the Copy/Paste from Word is about...converting the existing Word Treatment Plans to Excel?

**moosetales** · 03-15-2014, 03:32 PM

You are correct. Thanks.

**jaslake** · 03-15-2014, 03:41 PM

Will there be one person doing this Conversion?

**moosetales** · 03-15-2014, 04:45 PM

John,

We have approximately 20 individuals that will be converting over from Word to Excel. These individuals will be copying and pasting only certain data NOT the entire treatment plan.

Originally Posted by jaslake

Will there be one person doing this Conversion?

**moosetales** · 03-15-2014, 05:09 PM

John,

As long as the user double left clicks into the Excel field and THEN pastes there is NO problem bringing data into the Excel form from another source. Is there a way to force the user to HAVE to double click into a field before they can paste? Thanks.

Matthew

**jaslake** · 03-15-2014, 06:37 PM

Don't know...

**moosetales** · 03-16-2014, 01:51 PM

John,

I'm jumping up and down with JOY! I think I've got the form fixed as I'd hoped. Give it a try and let me know if it works for you too. I hope I haven't counted my chickens before they've hatched.

Matthew

**moosetales** · 03-16-2014, 02:13 PM

John,

Disregard the above version (V1.20 with Calendar). I found a few hiccups that I've fixed in this version.

Matthew

**jaslake** · 03-16-2014, 02:42 PM

Hi Matthew

I get an error here in trying to open the Calendar

Please Login or Register  to view this content.

Your using a Calendar Control with which I'm not familiar. I don't use this Calendar Control because you need to make certain the Reference to Microsoft Windows Common Controls is available on each Computer

**moosetales** · 03-16-2014, 09:01 PM

John,

Here's where I found the code for the calendar:

http://www.fontstuff.com/vba/vbatut07.htm

[QUOTEYour using a Calendar Control with which I'm not familiar. I don't use this Calendar Control because you need to make certain the Reference to Microsoft Windows Common Controls is available on each Computer[/QUOTE]

It's working on my machine and at least one other in my crew BUT that must be because the Reference you referred to above is on each of the computers we've tired it on thus far. I'm open to suggestions if there is a better solution. What do you use?

BTW, Button 8 (Sheet6) is giving me fits. If I remove a STG from Sheet2 and then hit Button 8 on Sheet6 I lose the blue shading in the cells that are removed. I've been over the code and I can't see how you solved this problem for Button 118, Button 15, Button 16, and Button 17.

I'm going to run a full scale trial of the form tonight and as long as the cut, copy, paste solution holds up I'm hoping to go live tomorrow with a select number of my crew ahead of rolling it out to the masses. If the calendar is problematic and I can't figure out the shading I'll remove the calendar and the shading and call it good.

Thanks again.

Matthew

**moosetales** · 03-16-2014, 09:06 PM

John,

Here's the code (and source) I used to address the cut, copy, and paste issue:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

Please Login or Register  to view this content.

And here's how/where I inserted the code:

How to use:

Copy above code for the standard module.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Copy the code for the ThisWorkbook module.
In the project explorer, locate the ThisWorkbook object.
Double click the ThisWorkbook object.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.
Close and reopen the workbook.

**jaslake** · 03-16-2014, 09:44 PM

Hi Matthew

You do this...let me know how things go

I'm going to run a full scale trial of the form tonight and as long as the cut, copy, paste solution holds up I'm hoping to go live tomorrow with a select number of my crew ahead of rolling it out to the masses. If the calendar is problematic and I can't figure out the shading I'll remove the calendar and the shading and call it good.

You get things settled down. I don't need to be satisfied with the Code...you, and particularly your Users, need to be satisfied.

I'll help you with the Calendar Control once basic issues are settled...not before.

Let me know how I can help.

**moosetales** · 03-17-2014, 12:57 AM

John,

Just completed the full-scale trial run and I ran into a couple glitches.

#1--Button 8 on Sheet6 copies more from Sheet3 than is necessary. I've highlighted a section on Sheet6 that should NOT have copied and pasted from Sheet3. I picked up on this because I had never entered data into the Objective Indicators on Sheet3 and when I did I realized Button 8 was copying more than was necessary.

#2--When spell checking, the misspelled word does not highlight or anything therefore it's difficult to find where the misspelled word is so you can read it in context. Any chance of forcing the code to highlight the misspelled word?

That's all I found thus far that I could not fix. The COPY, CUT and PASTE macro is working like a champ. Thanks

Matthew

**jaslake** · 03-17-2014, 02:32 PM

Hi Matthew

Regarding this

#1--Button 8 on Sheet6 copies more from Sheet3 than is necessary

In Module 2 replace the Find_STG Code with this

Please Login or Register  to view this content.

Regarding this

#2--When spell checking, the misspelled word does not highlight

I have no ideas for this...can't find anything.

**moosetales** · 03-17-2014, 02:43 PM

John,

Great. I've entered the new code and based on my quick trial everything works. I'll put it through its paces in a bit and report back. Here's the newest version of the form with the new code added. Thanks.

Matthew

**moosetales** · 03-17-2014, 03:06 PM

John,

Just went back into the form and found that if I add STGs to the ITP and hit Button 8 everything works perfectly then when I remove STGs and click Button 8 the STGs are gone (as expected) but the cells where that were unlocked so Progress could be entered are still unlocked (see the most current version of the form, Sheet6, rows 11, 14, 17). These rows/cells are blank but unlocked. I've printed off the codes for Button 8 and the other "delete" buttons in hopes of being able to fix this glitch myself but alas I cannot see the common thread. What am I missing that is required to fix this hiccup? Thanks.

Originally Posted by moosetales

John,

Great. I've entered the new code and based on my quick trial everything works. I'll put it through its paces in a bit and report back. Here's the newest version of the form with the new code added. Thanks.

Matthew

**moosetales** · 03-17-2014, 03:44 PM

John,

In case this helps, I just completed a quick review of Button 8 in previous versions (back to V 1.9) and the glitch reported above existed back then too. Thanks.

**jaslake** · 03-17-2014, 04:38 PM

Hi Matthew

In Sheet 6, unprotect the Worksheet and Lock the offending Cells. Protect the Worksheet.

Add the indicated Line of Code to Sub Button8_Click()

Please Login or Register  to view this content.

Try this exercise again

Just went back into the form and found that if I add STGs to the ITP and hit Button 8 everything works perfectly then when I remove STGs and click Button 8 the STGs are gone (as expected) but the cells where that were unlocked so Progress could be entered are still unlocked (see the most current version of the form, Sheet6, rows 11, 14, 17). These rows/cells are blank but unlocked

**moosetales** · 03-17-2014, 05:06 PM

John,

That fixed it. I'll try another dry run and if all goes smoothly I'll let you know and we can talk about the calendar. I've learned through this process NOT to set a firm date but tomorrow's looking like a really nice day to call this project SOLVED and roll this project off the assembly line. Once again, THANKS!

**jaslake** · 03-17-2014, 05:56 PM

Hi Matthew

Have you invited some (or many...or ALL) of the Users to test the Code on THEIR Computers?

Recall what I previously mentioned about the Calendar Control. If yours works in your environment then you're good to go...we don't need to talk about the Calendar.

Best of luck on the Roll Out...keep me posted.

**moosetales** · 03-18-2014, 06:57 AM

John,

Good morning. I ran the form through it's paces last night and it performed perfectly with one minor hiccup. I'm close enough that I will still be rolling the form out today to select members of my crew to begin using in the field so I'm super excited.

I found one issue with Button 118 on Sheet6. It allows me to delete the "heading" row in column B; the one that has a drop down in it where you can choose either Presenting Problem or Parent Priority. Couldn't figure out how to block that other than to disallow the user to delete row 1. Also, when I click Button 118 and then click lets say B8 on Sheet6 I get a blank message box that pops up in the middle of the page. What might be causing this?

Please Login or Register  to view this content.

BTW, I added a message box to Button 8 to give a bit more instruction to the user ahead of them syncing with goals.

Thanks again for your input.

Matthew

**jaslake** · 03-18-2014, 08:53 AM

Hi Matthew

Regarding this

It allows me to delete the "heading" row in column B

Add these lines of Code

Please Login or Register  to view this content.

Regarding this

when I click Button 118 and then click lets say B8 on Sheet6 I get a blank message box that pops up in the middle of the page. What might be causing this?

It's caused by these lines of Code...place a Message in the Message Box

Please Login or Register  to view this content.

**moosetales** · 03-18-2014, 09:32 AM

John,

PERFECT! I updated the code and I'm a couple clicks away from the form being placed on our network so my crew may begin trialing it in the field. I'll keep you updated as the feedback comes in. I will mark this thread as SOLVED at this time.

It's been a FANTASTIC learning experience for me and my expectations of what I could have possibly produced have long been exceeded. I've enjoyed the process very much. We've been at this for just under a month and you've compiled an impressive amount of code along the way.

I hope our public dialogue has helped more members and guests on this site than just me. Now that I've "caught the bug" I'll be hanging around to learn and explore more regarding the possibilities of Excel. Thanks again for all your patience and support.

Matthew

**jaslake** · 03-18-2014, 09:55 AM

You're welcome...glad I could help. Thanks for the Rep.

Good luck on the roll out...hope it goes well.

**moosetales** · 03-18-2014, 10:50 AM

John,

In the process of putting the finishing touches on the form (password protecting the code, workbook, etc.) I realized that a significant number of items in the Ribbon were still active and when clicking around I managed to all but destroy the form/formatting. I found a thread that I believe accomplishes my goal of hiding the Ribbon but it's difficult for me to sift through which version of code would work best for our purposes.

The only tab in the Ribbon that I need the users to access is the "File" tab. Thought? Thanks.

http://www.mrexcel.com/forum/excel-q...-workbook.html

BTW, the VBA code in the most recent version of the form (see attached) has been password protected and the workbook has been protected. The password for the code, worksheets and workbook is "j". Thanks.

**jaslake** · 03-18-2014, 11:23 AM

Hi Matthew

Gotta be honest with you, I've not had occasion to play with the Ribbon. So, your guess will be as good or better than mine.

**moosetales** · 03-18-2014, 03:46 PM

John,

Well, I've done some digging and found a solution. I proved the concept works but then began chasing my tail regarding the order and placement of the new lines of code so everything meshed perfectly and all my buttons would work. I was able to HIDE the toolbar upon open and then assign two macros to two separate buttons that allowed me to SHOW or HIDE the toolbar once inside the form.

Adding this functionality will allow the user to enter the form without seeing the toolbar (hence reducing the chance of them clicking into it and messing with something) and then when they go to save the document they'll just need to click the SAVE/SHOW TOOLBAR button.

Here are the lines of code I used:

To HIDE the toolbar upon open I added the following code in the ThisWorkbook module
[SOURCE] http://www.mrexcel.com/forum/excel-q...book-open.html

Please Login or Register  to view this content.

Then, to SHOW or HIDE the toolbar once inside the form I used the following lines of code:
[SOURCE] http://www.mrexcel.com/forum/excel-q...ndkeys-f1.html

to HIDE the toolbar

Please Login or Register  to view this content.

to SHOW the toolbar

Please Login or Register  to view this content.

In the attached version (1.28) I have two buttons on Sheet1 (labeled SAVE/SHOW TOOLBAR & HIDE TOOLBAR). I have NOT entered the above lines of code since my first, second and third take at it proved the concept but I got in over my head regarding the order by which to place them in the existing code. Is this a simple process to add or have I opened Pandora's Box?

Thanks.

**moosetales** · 03-18-2014, 05:22 PM

John,

Being new to the forum means I'm trying hard to learn the culture and the etiquette. If you would prefer that I throw this last issue out to the forum under a separate thread I'm more than happy to do that at the same time, if it's something you're able to weigh in on I've got a ton of confidence in your guidance. Thanks.

Matthew

**jaslake** · 03-18-2014, 06:07 PM

Hi Matthew

I don't see the Code you referenced in your Workbook. Please insert it then I'll look at it...I'm transitioning from Windows XP to Windows 2007 so have some hiccups of my own to deal with...moving Files...getting them coordinated...

**moosetales** · 03-18-2014, 10:20 PM

John,

Oh boy...you do have you're hands full. I think we just got rid of our last XP machine a short while ago.

...I'm transitioning from Windows XP to Windows 2007 so have some hiccups of my own to deal with

I'm working on the code and will post when I've got it somewhat sorted out. Thanks.

**jaslake** · 03-19-2014, 09:40 AM

Hi Matthew

In the attached I've added the Code to the Modules where I thought they should reside. Notice in the ThisWorkbook Module I've Commented out Subs such as this

Please Login or Register  to view this content.

and this

Please Login or Register  to view this content.

and incorporated the Code in the existing Event Code.

Buttons 138 and 139 Code is in Module 9. Button 138 Code has a Save Line of Code as the Button Title infers. If you don't wish to save the File comment out this line of Code.

You'll notice the attachment has a strange name...I'm having file name downloading issues and have a request in to figure out why.

**moosetales** · 03-19-2014, 01:11 PM

John,

I merged the code in the version you attached into a version I'd been working on since last night and the code works great...best I can tell at this time. I too started down the "Call....." path in my code but couldn't wrap my mind around the order and placement of the code. Thanks.

We're running into some hiccups with individual users using the form but it appears to be issues pertaining to the reference libraries which I need IT to look. The errors I can think of off the top of my head are, "Compile Error: Can't find project in library." and "Could not load an object because it is not available on this machine." I compared my "Available References" in VBAProject that are clicked with the other user that got the above errors and his machine did not have some of the References that my machine had checked. No need to do anything on this now....I'll let our IT Department work on that.

I've added buttons on the page to move back and forth between worksheets for some of our users who are more accustomed to that format of moving through a form. My last challenge with that is to find a way to get a "Previous Page" button to stay put on Sheet3 since every time I update the Copy of the ITP it replaces everything on that page.

I'll keep you posted on my progress. Roll out is going slow but we're making progress.

Thanks.

Matthew

**jaslake** · 03-19-2014, 01:21 PM

Hi Matthew

I'm 99.9999% certain this issue is caused by your Calendar Control...I had mentioned this potential several Posts ago. There are ways around it if it continues to be an issue.

"Compile Error: Can't find project in library." and "Could not load an object because it is not available on this machine."

**moosetales** · 03-19-2014, 02:06 PM

John,

I've removed the calendar from a saved version of the form and am about to run a trial on the offending machine. In the meantime, here's the code that appears to have triggered the error message:

Please Login or Register  to view this content.

Thanks. I'll be in touch.

Matthew

BTW...I was wrong...I just trial the form on a desktop machine and realized that machine is STILL running Windows XP so I guess we still have a few of these laying around after all.

**moosetales** · 03-19-2014, 03:01 PM

John,

I'll post some screen scrapes later tonight but it appears to be the calendar on some machines whereas on other machines it's the one or more of the Add or Delete buttons. Like I said, I have the screen scrapes saved but need to head out to another meeting. Here are two screen scrapes that show what references I have on my machine WITHOUT the form open and WITH the form open (I pulled these from going to VBA Project...tools....references):

WITHOUT form open

without form open.jpg

WITH form open

with form open.jpg

and here's a screen scrape from one of my crew's machines while the form was open on their machine

references on SD machine.jpg

More to come.....aren't roll outs fun

**jaslake** · 03-19-2014, 07:28 PM

Hi Matthew

Yes they are...been through many myself...stressful, painful, hopefully, eventually, gratifying.

Let me know what I can do to help.

**moosetales** · 03-20-2014, 12:49 AM

John,

A long days work is coming to an end....finally. I've sent a list of the user names that will need to have their Excel programs checked and updated with the critical References. The guys in IT are confident they'll be able to get everything in order. One of the challenges of this roll out is that this form will be used by 38 clinicians and supervisors in 5 different office across the State. Thanks goodness IT has the ability to remote into each user's machine.

As you may have seen, in one of the more recent versions of the form I added buttons that allow the user to move between worksheets (PREVIOUS PAGE and NEXT PAGE). I figured out how to do this for all sheets with the exception of Sheet3. Every time I add a PREVIOUS PAGE button on Sheet3 and then Update the COPY of ITP using the UPDATE "Copy of ITP Goals & Obj." the button disappears.

I found come code and modified it to meet my needs but I'm stuck. I can get the code to create a button (similar in format to my other PREVIOUS PAGE buttons but not exactly) but I can't get it to take me back to the previous page when I click into the button. I placed the code in Module 1. Here's the code I was using:

[SOURCE of CODE]
http://en.kioskea.net/faq/1105-addin...ctive-the-code

Please Login or Register  to view this content.

Ideally I'd like the PREVIOUS BUTTON on Sheet 3 to be identical to the other button so named but I won't squabble if that's not possible. Obviously I've missed something in the code or maybe I'm go about this ALL wrong...both are real possibilities. Thanks for weighing in.

Matthew

**jaslake** · 03-20-2014, 09:49 AM

Hi Matthew

Put your Button in Sheet3 as you want it and assign Code to it as desired. Then in Module 6 Code make these changes:

At the Top of the Module add the indicated Line of Code

Please Login or Register  to view this content.

Comment out the indicated Line of Code

Please Login or Register  to view this content.

At the bottom of the Module add the indicated Line of Code

Please Login or Register  to view this content.

Hope the Roll Out is going well...

**moosetales** · 03-20-2014, 11:27 AM

John,

Worked like a charm. Thanks. Working with IT now on the update. More later.

**jaslake** · 03-20-2014, 11:39 AM

Keep in touch...

**moosetales** · 03-20-2014, 03:48 PM

John,

Just found another hiccup that inadvertently allowed cells to be deleted that were not intended to be deleted. The problem button is on Sheet2; DELETE OBJECTIVE INDICATOR Button. When you CLICK delete and then CLICK into unlocked cells above the Objective Indicator section the program allows the user to delete those cells. I need the DELETE OBJECTIVE INDICATOR button to delete ONLY those rows immediately below the row that states Objective Indicators. Here's the code for the button in question:

Please Login or Register  to view this content.

If I'm not making sense let me know and I'll try again. BTW, the early returns on feedback are looking very positive. It's an adjustment for most but the work we've done has created a fairly intuitive layout and a user friendly design. Thanks again.

Matthew

**jaslake** · 03-20-2014, 04:03 PM

Hi Matthew

I'll need to think on that for the moment...

**jaslake** · 03-20-2014, 04:52 PM

Hi Matthew

Replace the DeleteObjIndicator_Click() Code with this

Please Login or Register  to view this content.

**moosetales** · 03-20-2014, 09:28 PM

John,

Thanks for the code revision....problem solved. I got word back from our IT Department and we're running up against a missing Reference that seems to be pretty consistent across many machines. The reference is "Microsoft Windows Common Controls 2 6.0 (SP4)". I'm working tonight on zeroing in on what part of our code might be triggering this since I eliminated the Calendar in one of my versions and we still were getting a message stating that an Object could not be found...or something like that. I'm confident we'll figure it out it's just hard to be patient.........guess this is part of the learning curve. Thanks again for the code revision and the helping hand.

Matthew

**moosetales** · 03-20-2014, 10:15 PM

John,

Well, everything I can find related to the missing reference mentioned above points to the Calendar.....it's possible I did not extract every bit of the calendar and code from the form prior to trying it on a few machines. I'm going to remove the calendar tonight and save that in a different version and attempt to run it tomorrow and see what happens.Here's to hoping you were correct in that it's the calendar that's causing the SNAFU!

Thanks.

Matthew

**jaslake** · 03-20-2014, 10:52 PM

Hi Matthew

I'm off to bed...the Calendar Control you're dealing with will cause you heartburn...do without it for the moment. If you truely want a Calendar control we'll build one for you that will work accross all platforms.

**moosetales** · 03-20-2014, 10:55 PM

John,

I'm heading that way fairly soon myself. I'll test the waters with my crew and see what the vote is on the calendar topic. Thanks again.

Matthew

**jaslake** · 03-21-2014, 10:43 AM

Hi Matthew

Try the attached...

**moosetales** · 03-21-2014, 11:06 AM

John,

That's really nifty how the calendar pops up just by being in the date field. I'll play with it a little and try it across various machines and see what response I get. BTW, removing the previous calendar has thus far proven the eliminate the problem; you can update your previous post and change it from 99.9999999% to 100% sure it's the calendar

Part of my crew is currently using the form in "real time" and I'm waiting (with bated breath) for their initial responses. I found one of our machines that is running Windows 2003 and the form performed remarkably well (given that a conversion pack had been previously loaded). For anyone attempting to use our form on 2003 will experience a Run Time Error 438. I'm not concerned with this as that machine is slated to be replaced with a new machine shortly.

I'll keep you posted. Thanks again for the updated calendar.

Matthew

**moosetales** · 03-23-2014, 11:07 AM

John,

Hi. Hope all is well with you and yours. I've been putting the calendar through its paces this weekend and have grown to really like the fact that it pops up when you click into the cell...very nice feature. I ran into a hiccup...

The hiccup has to do with where the calendar pops up after clicking into the "date" cell. If the ribbon is hidden the calendar pops up dead center on the page but if the ribbon is showing the calendar pops up off the bottom of the screen but still centered horizontally (see screen scrape).

calendar glitch.jpg

BTW, the early returns from my crew are favorable. So far they've found one capitalization error and have had a few questions about file format for saving the form, printing active worksheets vs. the entire workbook and a few other minor issues BUT that's it. This coming week will be the real test as 10-15 of my crew begin to field test the form. I'll keep you posted.

Thanks.

Matthew

**jaslake** · 03-23-2014, 02:21 PM

Hi Matthew

I can't duplicate the issue...see screen shots attached with and without the Ribbon...

myScreen.jpg

myScreen1.png

**jaslake** · 03-23-2014, 03:37 PM

Hi Matthew

Just for putzin around sake see what this does for you on your platform. In the Module ModFormPositioner, do a search for Application.CommandBar

You should find this Code...add the Line of Code as indicated...save and close the Form...reopen and run the Calendar Code...any change? For the good or worse?

Delete the change of not for the good...

Please Login or Register  to view this content.

**moosetales** · 03-24-2014, 08:38 AM

John,

After searching for that line of code for a little while I finally realized (embarrassingly enough) that I was in the version withOUT the calendar. Once I got into the correct version I made the adjustment to the code; the addition of the new line of code did not effect the calendar's position one way or another. I'll try it on another machine and see if it might be my settings. I'll be in touch. Thanks.

Matthew

**moosetales** · 03-24-2014, 11:23 AM

John,

Not sure this is anything we can control BUT I thought I'd throw it out there. The buttons (HIDE..., SHOW..., SPELL..., etc.) on the far right of the screen on Sheet2 enlarge to twice their size at times. I've had this happen and I'm getting reports that this has happened to a few users. Is this just a fluke or is there something I did in the setup of the buttons that is making them mildly unstable? Thanks.

Matthew

**jaslake** · 03-24-2014, 11:28 AM

Hi Matthew

What are you doing when this happens...

enlarge to twice their size at times

**moosetales** · 03-24-2014, 12:33 PM

John,

As bizarre as it sounds...just clicking into that worksheet seems to be the only thing that triggers it at times...no other definable pattern. I ran into a similar issue today when I was doing a training with one of our staff. I was using Team Viewer (remoted into their desktop) and was showing them around the form and when I clicked into Sheet2 the buttons were gone...but then I noticed really small buttons way up in the left hand corner of the page. I could not get the page to refresh so I canceled out of the form, went back to the email I had sent her, copied and pasted the form onto her desktop and then re-opened the form from there. Once I did this the buttons were in their proper position but larger than expected. Maybe it's gremlins

Matthew

**jaslake** · 03-24-2014, 02:19 PM

Hi Matthew

Sorry, I can't duplicate the issue...

**moosetales** · 03-25-2014, 08:32 AM

John,

This is one of those "Duh" moments for me. I was able to replicate the problem of the buttons changing size/proportion. Last night I added a large number of goals to the Goals & Objectives page and bingo, the click boxes expanded. I had grouped the boxes so I checked the properties of the grouping and found that I had clicked the move and size button instead of the move but don't size button (see below). Once I made the switch all was good. Phew!

expanding boxes.jpg

probelm solved.jpg

Matthew

**jaslake** · 03-25-2014, 09:30 AM

Hi Matthew

Glad you got it sorted...

**moosetales** · 03-25-2014, 10:23 PM

John,

Today proved to be a great test of the form. Here are a few thing that have been tweaked since the last version I posted:

1) Changed to a font that's recommended for print documents (Garamond).

2) Adjusted positioning of some of the command buttons in an attempt to address an issue with some of the buttons disappearing at random times.

3) Fixed a few typos and structural changes.

4) Adjusted the Labels on the "Sync ITP Goals..." button in an attempt to make it more intuitive.

5) Removed a drop down menu that had snuck into Sheet1; even though it was in a locked cell I removed it just in case.

6) I removed a few extra rows on the ITP Goals & Obj. Sheet since they were causing an extra blank page to appear.

Printing the workbook has proven to be a bit of a challenge since we need to print the ITP double sided. I found a great resource that finally shed some light on why we weren't able to print double sided. Here's the link:

http://www.mrexcel.com/forum/excel-q...-sheets-2.html

I had one user inadvertently save the form (after completion) in a non macros enabled format which as you know removed ALL macros. I spent a bit of my day transferring the contents of their treatment plan back into a macros enabled version of the form. I adjusted their machine so that it now defaults to the .xlsm format.

One of our users reported today that the new format cut the time she typically spends creating an ITP in half. I was pleased with this news.

I keep running into a persistent issue that seems to be an issue that's known to plague Excel 2010; the issue of the command buttons going missing. Here's a link to one of the threads dedicated to the issue:

http://www.mrexcel.com/forum/excel-q...el-2010-a.html

I have yet to identify the pattern and I cannot replicate the issue...it just happens. Currently the only cure is closing the file and re-opening it. If I've needed to save the form before I closed it though I've run into the issue of the missing buttons never reappearing even after re-opening the file. UGH!

Overall, the soft launch is going well. Having access to Team Viewer is making the introduction process for our team members across the State to see it demonstrated ahead of getting their hands on the form. Thanks again for all your hard effort in assisting me through out the process.

Matthew

**jaslake** · 03-26-2014, 10:43 AM

Hi Matthew

Several links attribute the disappearing Buttons to Printing of the Workbook.

http://social.msdn.microsoft.com/For...forum=exceldev

http://www.mrexcel.com/forum/excel-q...disappear.html

My research indicates this happens when you select to print the Entire Workbook. When you print each Worksheet individually the issue does not happen.

I tested this theory and it appears to be correct. In printing the Entire Workbook, some Buttons resized and appear to have disappeared. In printing each Worksheet individually, the Buttons kept their formatting.

Test this yourself...if it proves to be true, you may wish to consider writing a Print routine that prints each Worksheet individually. Let me know how it goes...

**moosetales** · 03-26-2014, 11:33 AM

John,

You are spot on with your assessment! I can replicate the problem every time. For me, the buttons on Sheet1 remain as is but the buttons on the following sheets either shrink or go away altogether. I've tried it several times and the Sheet I am in when I go to print is the ONLY Sheet where the buttons stay put. Hmmmmmm.

When you refer to a Macro for printing what are you thinking? A "Print" command button? Thanks.

Matthew

**jaslake** · 03-26-2014, 11:39 AM

Hi Matthew

Yes, exactly

A "Print" command button?

You can start out with the Macro Recorder and then modify the Code to suit. If you wish, I'll look at it.

**moosetales** · 03-26-2014, 11:59 AM

John,

Ok. I'll instruct users to either print one worksheet at a time or print the entire workbook as their LAST step and not save after they print. Fun stuff! Thanks again. I'll keep you posted on my progress with the print "work around".

Matthew

**moosetales** · 03-26-2014, 02:39 PM

John,

I'm running into an issue with changing the font on the STGTemplate Sheet. I need the font to be Garamond and I can't find the spot to change it. I've been round and round in the code and I'm still coming up with a blank. Thoughts?

Before I hit send here I it dawned on me that I may have misspelled Garamond when I changed the font.....when back in the form and I had indeed spelled it Garmond. Problem solved.

Matthew

**moosetales** · 03-26-2014, 06:41 PM

John,

I've been at it now for a while and I found three different lines of code that I've trialed with mixed results:

Please Login or Register  to view this content.

This is a REALLY cool line of code. This allows the user to click a button in the worksheet and it will display the print preview page...at least in Excel 2010...not sure about earlier versions.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

All three are still in the form but I settled on the last option in the list above (Sub printsheets). It works great and does NOT mess with the buttons at all. It does NOT however allow for progressive page numbering (resets the page numbering to 1) between worksheets and it does NOT duplex when printing. The other issue I'll eventually have to contend with is when a user needs to change the source to which they are printing. We have around 10 copiers to choose from although I will say most users will be printing to the same source every time so this is not a huge deal.

I've got almost an hours drive home so I'm done for now. I'll be back at it later tonight once kiddos are settled. Thanks again for cheering me on.

Matthew

**jaslake** · 03-26-2014, 07:27 PM

Hi Matthew

Keep truckin'...when you hit a wall let me know. You're doing fine.

Keep those

kiddos

as your primary focus. This stuff is secondary...

**moosetales** · 03-27-2014, 05:01 PM

John,

Help! I've come so far but have hit a wall. I need to find a way for my code below to find the path to the desktop without having to hard code everyone's username into the path in the code below. Any thoughts? Thanks. The code below is in Module 10 in the form.

Please Login or Register  to view this content.

**jaslake** · 03-27-2014, 07:53 PM

Hi Matthew

Do a search on

PHP Code:


environ excel vba

**jaslake** · 03-27-2014, 08:28 PM

You'll probably end up with something like this

Please Login or Register  to view this content.

**moosetales** · 03-28-2014, 08:26 AM

John,

Thanks for troubleshooting this one. I've done a preliminary test and it's working on my machine...now to try it on different environments. I'll keep you posted. Thanks again.

Matthew

**moosetales** · 03-28-2014, 03:38 PM

Hi John,

While completing a "run through" with one of my crew today I realized that the printing issue (i.e. printing entire workbook and the buttons go missing) we've been running into does NOT occur when you print the entire workbook in Excel 2007. This is great news for some of my users who are still running 2007. Now, if only Microsoft could find the line of code in 2010 that's messing up the process and I'd be a happy camper. The roll out continues to go very well with only minor hiccups. Thanks again.

Matthew

**jaslake** · 03-28-2014, 03:52 PM

Hi Matthew

I believe we were aware of this and that the issue is an Excel 2010 issue

does NOT occur when you print the entire workbook in Excel 2007

I'd suggest you write a Print Routine (for ALL users) to keep your Code consistent across ALL users.

**moosetales** · 03-28-2014, 04:00 PM

John,

Ooops! Obviously my brain did not retain that tidbit. Thanks for keeping me honest. My plan is to leave the Print to PDF code in the form and instruct users to print in that fashion. Thanks.

Matthew

**SamT** · 03-28-2014, 05:18 PM

Moosetales,

I have been following this thread with real interest because it is the best example of a spreadsheet based Form I have ever seen. I hope to learn much from it.

When you really have it ready for production, can you upload a copy here. I would like to save it and this entire thread for future study.

Thanks for the all fish and see you later.
SamT

**moosetales** · 03-28-2014, 08:34 PM

SamT,

Thanks for the interest. It has definitely been a great learning experience. I give John all the credit in the world for coding the engine that drives this form. He has been an invaluable resource and a fantastic source of encouragement. I'll be posting updates as we finely tune the finished product. Thanks again for following along and weighing in.

Matthew

**moosetales** · 03-29-2014, 07:50 AM

John,

Good morning. I received some feedback yesterday that I've been working to address and I've got the matter resolved......almost. One of my crew requested that the placement of the "cursor", when clicking "ADD" or "PREVIOUS PAGE" or "NEXT PAGE" buttons, be more intuitive. For instance, when clicking the NEXT PAGE button on the Demographics (Sheet1) worksheet the "cursor" now advances to the Presenting Problems & Progress (Sheet6) worksheet, range B2.

I used the following line of code to get where I'm at thus far:

Please Login or Register  to view this content.

I also used this line of code in the open sequence to place the "cursor" in range D3 on the Demographics (Sheet1) worksheet when the form opens.

I've used the above line of code for Button1 on Sheet6 and Buttons10, 11 & 14 on Sheet2 with some success but I was only able to figure out how to select the original range that we built from (e.g. Button1-Sheet6 selects B2, Button10-Sheet2 selects D9, and so on). This solution works but I would like to rewrite the code so the "cursor" advances to the next cell the user is likely to begin typing in after clicking Buttons1, 10, 11 & 14. Thoughts?

BTW, it's sunny here and the snow is melting....hoping you're getting some relief from Winter as well.

Thanks.

Matthew

**jaslake** · 03-29-2014, 09:51 AM

Hi Matthew

Our snow is all but gone from the rains of the last two days...until tonight and tomorrow. More coming...a few inches. Be gone on Monday...high of 63 forecast. Typical Ohio March weather.

If you can figure this out you're a better man than I, Gunga Din

I would like to rewrite the code so the "cursor" advances to the next cell the user is likely to begin typing in

**moosetales** · 03-29-2014, 10:54 AM

John,

I think I got it.........I added this line of code in Module 4 for Button 1:

Please Login or Register  to view this content.

I also added the same line to the other "ADD..." buttons. The only one I don't have working yet is the "ADD OBJ"/Button 11.

Would you please test and see if all but Button 11 work for you as I'm hoping? Thanks.

**jaslake** · 03-29-2014, 11:41 AM

Hi Matthew

Try this

Please Login or Register  to view this content.

**moosetales** · 03-29-2014, 11:41 AM

John,

I'm one step closer with Button 11 just can't figure out the pattern yet.

Thanks for taking a look.

Matthew

**moosetales** · 03-29-2014, 11:50 AM

John,

It appears our last posts were entered simultaneously. I entered your adjusted code and it works perfectly. I'm off to try the same thing with the "Delete" buttons. BTW, this only proves I'm REALLY curious and determined and I've had great tutoring over the past month

Thanks.

Matthew

**moosetales** · 04-04-2014, 12:12 AM

Hi John,

The "soft launch" is progressing nicely. I have about 10 of my crew actively using the form with much success. We ran into a hiccup and a discovery today. I am hoping you might be able to shed some light on both.

Hiccup....one of my crew uses the clipboard when transferring data from Word over to the new Excel form. This is a great way of efficiently moving data from Word to Excel with one exception; when pasting from the clipboard the user is able to clear the formatting that is in the Excel cell. For some reason our CUTCOPYPASTE macro does not stop this from happening. I would like not to disable the clipboard but at the same time I can't afford to have users deleting the formatting in the form. I've pasted a screen shot of what I'm talking about below. Any ideas for me?

Paste Special.jpg

Discovery....regarding the disappearing buttons. You will recall that if the user prints the "entire workbook" in Excel 2010 the command buttons disappear? Well, if after the user discovers the command buttons are "gone" all they need to do is click the View tab and then select Page Break Preview and the buttons will reappear. If the user then clicks "Normal" view again they will be back to the screen they were accustomed to seeing. I found a piece of code that automatically changes the view to Page Break Preview and then back again to Normal but I haven't found a way to effectively incorporate it into the form.

Please Login or Register  to view this content.

As always, thanks for taking a look and giving your feedback.

Matthew

**jaslake** · 04-04-2014, 02:03 PM

Hi Matthew

How disciplined are your Users? If they use the Paste Option Button as indicated (see Screen Shot) Destination Formatting is retained.

Paste Destination Formats.jpg

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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