Copy & Pasting Ranges (VBA) by way of a CommandButton

John,

The adjusted code does as expected with one hitch....the cell adjacent to "Progress" remains locked so no text may be entered. Other than that, it's looking great. I really think the added Obj will be a net gain to ensure the user includes comments on progress for both STGs and Objs. Thanks.

Matthew

John,

Thanks. That line did the job. I'm currently working on the missing border (LeftEdge)in the cell to the right of "Progress". I'd like to try my hand at solving this matter but am running into errors. To make sure I'm entering code in the correct placement in the line of code would you mind pointing me to the line my code SHOULD be started on? Thanks. Hope it's sunny in your parts...it is here. No rush on this as I'm heading outdoors for a bit of Spring Cleaning. Thanks again.

Matthew

Please Login or Register  to view this content.

The line color is a dark grey and the tint/shade leaves the cell white.

I was up early this morning working on a few more tweaks ahead of the full launch of the form this week. Here's what I've accomplished thus far:

1) When the user clicks into the first cell (upper left corner) on Sheet1, depending which program title they choose from the drop down menu 4 cells on Sheet1 and 1 cell on Sheet6 auto populate with preset values. Prior to this adjustment, the user was required to click into each of these cells and make adjustments accordingly.

2) When the radio buttons for 90-day Review, 6 month Review or Addendum are clicked the "Click here BEFORE making changes to ITP Goals & Objectives" command button on Sheet6 is activated automatically. Since a review of the goals and objectives is required every time the user completes a 90-day review, 6 month review or an addendum I figured this would be one less step. In fact, I am contemplating removing the "Click here BEFORE...." button altogether but I want to get feedback from my team before making that change permanently.

I'm working on adding a few more questions for users to fill out on Sheet6 and am making a few more tweaks to the Demographics sheet. I'm REALLY pleased thus far and think my crew will find the adjustments to their liking. Thanks again for all your support in this effort.

Matthew

John,

Good evening. As stated before, I've been successful in programming three of the four option buttons on Sheet1 (i.e. 90-day review, 6 month review & Addendum) so they trigger the "ITP Review" button on Sheet6; this button used to be labeled "Click here BEFORE making changes to the ITP Goals & Objectives". I'm at a point where I'm stumped...I've got a vision but not sure how to proceed.

Since anytime the user completes a 90-day review, 6 month review or Addendum they need to report on the progress the client has made since the last review, the idea here is to take out the step of the user having to click the button on Sheet6 to pull over the ITP goals and objectives from the ITP Goals & Obj worksheet (Sheet2).

Clicking the "ITP Review" button on Sheet6 works great except I'm now having to add 4 questions to the review based on feedback we received from the State during our recent review. I've placed the 4 questions on a separate worksheet (Sheet9 labeled "ITP Review"); I've given it a named range of "Review_Questions". The plan is to hide this worksheet (as I have done with the other worksheet I am using for data validation.

In the version I've attached to this thread I placed the questions (mock up) on Sheet6 as I would like them to appear when either the 90-day review, 6 month review or Addendum Option Buttons are clicked on Sheet1; they come directly under the STGs and Obj. in Sheet6.

Here's what I'm thinking.......when the Original ITP Option Button is clicked on Sheet1 the only thing that would appear on Sheet6 would be the Presenting Problems section at the top of the worksheet. However, when the 90-day review, 6 month review or Addendum Option Buttons on Sheet1 were clicked the ITP Goals and Objectives and the 4 questions referenced above would appear on Sheet6.

I've taken a look at the code for the "ITP Review" command button on Sheet6 and I'm guessing that's where I would begin adjusting the code to do what I'm proposing? If you wouldn't mind pointing me in the right direction I'll see what I can do on my own. As always, thanks.

Matthew

John,

Thanks for the modification.....I'm on it and will report back shortly.

Matthew

John,

Even though the time I've had this morning to work on the modifications has been limited...the returns are fantastic. I've got a few more tweaks and adjustments to make but we're not far off at all. Thanks. I'll be in touch.

BTW, just received another email from user stating that the flow is great and the form itself is very user friendly. All positive thus far . I can't wait till they see the recent additions to the functionality.

Matthew

John,

Good morning. I completed a few more modifications last night.

1st--added code into from which alerts the user when they exit Sheet1 D3:E3 without choosing an option from the drop down.
2nd--added code to show/hide rows 5:6 on Sheet1 when the check box at the top of Sheet1, labeled "This ITP is late.", is checked or unchecked.
3rd--modified code assigned to the "ITP Review" Option Button at the top of Sheet1 to more or less hide the STGs and Objs on Sheet6 until the ITP Review Option Button is clicked.

I have yet to be successful in figuring where to put code to show/hide B6 on Sheet6 when the ITP Review Option Button on Sheet1 is selected/unselected. If you would be willing to point me in the right direction I'll try my hand at the coding.

Also, I've entered the following code in This Workbook but it doesn't seem to be working. The intent is to disable "save" or "save as" if the user has not entered a late note in Sheet1 E6 (which is a merged cell).

Please Login or Register  to view this content.

Thanks again.

John,

Just had another thought about a way to ensure the user chooses a program name from the drop down on Sheet1 (D3:E3). How about if I have all the cells locked in the worksheet/workbook, except D3:E3, until the user chooses a program name. Maybe something like this?

Please Login or Register  to view this content.

Thanks.

Matthew

Okay, this is what I've come up with. I'm using the following codes to prevent a user from printing OR saving if they've not entered a late note on Sheet1 E6

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The only problem I've uncovered is that if CheckBox 134 ("This Review is Late") is unchecked then a late note in on Sheet1 E6 is not necessary to print or save. I've tried to code this but to no avail. Thanks.

John,

I've run into an odd situation that I'm sure to the veterans on this site is no biggie but to me it's both cool and frustrating all at the same time. My coding for Sheet1 (see below) is working wonderfully and doing as I expected with one hiccup. While testing the functionality and sequencing I found that if I click (on Sheet1) the checkbox in row 3 while D3:E3 is blank row 3 hides instead of rows 5:6. However, when D3:E3 is populated with one of the options in the drop down list rows 5:6 hide/show perfectly. Here are the two codes:

Code for Checkbox

Please Login or Register  to view this content.

Code for D3:E3

Please Login or Register  to view this content.

I've poured over the codes and can't see the cause of this hiccup. Again, there is no run-time error associated with this nor does it occur unless one is blank when the other is clicked. I know there's a logical explanation but I just can't put my finger on it. Have you ever run into this? Thanks.

Matthew

Originally Posted by jaslake

Try this for the Before Print Code[CODE]Private Sub Workbook_BeforePrint(Cancel As Boolean)

John,

This worked great. I actually removed the code for the "BeforeSave" feature since this accomplishes everything I needed it to accomplish. Thanks again.

Matthew

John,

That's perfect.


NEW Code

Please Login or Register  to view this content.

OLD Code

Please Login or Register  to view this content.

Comparing the codes I can see what was added to the code and am I presuming correctly that {If .Shapes("Check Box 134").ControlFormat.Value = 1 _} isolated (for lack of a better term) the macro from its surroundings? As always, thanks.

Matthew

BTW, I've sent a couple PM your way and wanted to make sure I'm sending them to the correct address. Are they coming through? Thanks.

John,

Thanks for the explanation. What I was trying to say is that your new line(s) of code prevented row 3 from being hidden when cells D3:E3 were blank and CheckBox 134 was checked. It's all good now.

Matthew

John,

Here's the newest version of the form. Thanks.

Matthew

For those that have been following along with this thread....work on this form continues in the following thread:

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

Matthew