Conditional Unlocking

**hektisk** · 02-06-2011, 08:29 PM

Hello: I would like to add VBA code to a worksheet which unlocks certain cells if certain criteria are met. Basically, if cell a3 says anything other than 'New Assessment (No DC)' or is left blank, then b3:h3 should be unlocked. The same applies for a4 and b4:h4, a5 and b5:h5, and so on and so forth.

I've found numerous examples of conditional locking/unlocking in google, but none that quite do what I'm looking to do, and I don't know VBA well enough (that is, at all really

) to modify it accordingly. Any help is greatly appreciated.

**jaslake** · 02-06-2011, 09:13 PM

Hi hektisk

I think this does as you requested

Please Login or Register  to view this content.

Place the code in Sheet1 Module.

Let me know of issues.

**nimrod** · 02-06-2011, 09:27 PM

Hello hektisk:
Have you thought of using Data Validation instead ? This would require no macros .

Example
1) Select B3:H3
2) Open "Data Validation" tool window
3) Select Setting Tab
4) Select "Custom" from "Allow" drop down list
5) In Validations "Formula" text box type = $A$3 <>"'New Assessment (No DC)"
6. Select Validations "Error Alert" tab and type in the error message you want displayed.

... now user cannot type in b3:h3 when the text "'New Assessment (No DC)" is in cell A3

**jaslake** · 02-06-2011, 09:37 PM

Hi nimrod

Your approach is sound...however, with the sheet protected, I'm unfamiliar with how to unlock the requisite cells if 'New Assessment (No DC)" is selected via data validation without code. How do you do this?

**hektisk** · 02-06-2011, 09:44 PM

Thank you very much. To make sure I'm doing this right...the sheet is protected, and b3:h14 are locked. I right click on the sheet, and go to 'View Code'. I then go to Insert->Module and copy and paste the code in there. Is there anything else I need to do to 'run' the subroutine?

Originally Posted by jaslake

Hi hektisk

I think this does as you requested

Please Login or Register  to view this content.

Place the code in Sheet1 Module.

Let me know of issues.

**protonLeah** · 02-06-2011, 09:52 PM

My contribution:

Please Login or Register  to view this content.

**jaslake** · 02-06-2011, 09:53 PM

Hi hektisk
Almost...right click on the sheet (Sheet1) based on your sample file...copy the code into the window on the right. The code DOES NOT go in a general module...it goes in the sheet module.
See attached.

**hektisk** · 02-06-2011, 10:00 PM

OK, prior to your post I had put the code both in the worksheet module and general module just in case, and but neither seemed to work. I opened the workbook you sent me, enabled macros, and every cell from b3:h13 are locked, no matter the data in a3:a13. I see the text you inputted in b7:b9, so I'm not sure what I'm doing wrong. Is there something else I need to do? Thank you again.

Originally Posted by jaslake

Hi hektisk
Almost...right click on the sheet (Sheet1) based on your sample file...copy the code into the window on the right. The code DOES NOT go in a general module...it goes in the sheet module.
See attached.

**jaslake** · 02-06-2011, 10:37 PM

Hi hektisk
I'd be glad to help you debug this but I'm color blind...not kidding...remove the colors from your file and repost...can't "see" what's going on.

**hektisk** · 02-06-2011, 10:51 PM

Originally Posted by jaslake

Hi hektisk
I'd be glad to help you debug this but I'm color blind...not kidding...remove the colors from your file and repost...can't "see" what's going on.

Ah! Sorry. Most of the colors were unnecessary, but I did use conditional formatting to fill the cells in b:h with a very dark green when the corresponding cell in column a was blank or said 'New Assessment (No DC)' - basically my first solution, until I realized I'd be better off with locking/unlocking as appropriate.

The colorless file is attached.

**jaslake** · 02-06-2011, 11:03 PM

Hi hektisk

Ah...much better. Try the attached. I'm not certain where you're going with this. Let me know of issues.

**hektisk** · 02-06-2011, 11:23 PM

Aha! It's working great now. Thank you so much!

One thing I'm noticing, however...since the sheet is now protected by default, new rows cannot be added to the table. It's vital that the table be expandable. I'm not sure if there's any way around this...unless if the worksheet ONLY becomes protected when one of the cells in b3:h13 (or beyond) is selected. Is this practical?

Originally Posted by jaslake

Hi hektisk

Ah...much better. Try the attached. I'm not certain where you're going with this. Let me know of issues.

**jaslake** · 02-06-2011, 11:32 PM

Hi hektisk

A new twist...how do you enter new rows? Do you type them directly into the table...another process?

Let me know...probably won't get back to you tonight...Super Bowl is over...got Grandson "off to school" duty in the AM...going to bed.

**hektisk** · 02-06-2011, 11:42 PM

I've been typing them directly into the table. Open to other ideas, however!

FYI, to give you a bit more background...The actual spreadsheet I'm designing runs from column A to AG. A, B, and C are all user-edited, D:V are all look-ups from another sheet, W:Z are user-edited and AA:AG are user-edited or else locked depending on the value of X. (X is column A in text.xlsm, while AA:AG are B:H). As I cannot always watch over the person/people editing this sheet, I'd like to make it as foolproof as possible.

Regarding the timing, no problem - I need to get some rest myself. Thank you again.

Originally Posted by jaslake

Hi hektisk

A new twist...how do you enter new rows? Do you type them directly into the table...another process?

Let me know...probably won't get back to you tonight...Super Bowl is over...got Grandson "off to school" duty in the AM...going to bed.

**jaslake** · 02-07-2011, 10:35 AM

Hi hektisk

To add new rows, you'll need to unlock all cells in Column A from A2 down (I've done this in the attached) then you'll need to extend Data Validation down as far as you like (I've extended DV down to Row 29 in the attached). I've also changed this line of code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

This will give you the ability to add items in Column A.

Now, the code I provided is designed to work on your sample data requirements

add VBA code to a worksheet which unlocks certain cells if certain criteria are met. Basically, if cell a3 says anything other than 'New Assessment (No DC)' or is left blank, then b3:h3 should be unlocked. The same applies for a4 and b4:h4, a5 and b5:h5, and so on and so forth

It will need to be modified to accommodate this

The actual spreadsheet I'm designing runs from column A to AG. A, B, and C are all user-edited, D:V are all look-ups from another sheet, W:Z are user-edited and AA:AG are user-edited or else locked depending on the value of X. (X is column A in text.xlsm, while AA:AG are B:H).

**hektisk** · 02-07-2011, 01:48 PM

Yes, I expected to have to modify the code accordingly. I just thought it was easier to send you a truncated portion!

I took your latest spreadsheet, unlocked it, and then turned it into a table. When I entered any value in A18 (row 17 being the last row of the table) other than 'New Assessment (No DC)', however, it didn't expand the table accordingly. When I entered in 'New Assessment (No DC)', it did expand. I'm thinking this is because when any other value is entered, the corresponding cells lock and the sheet becomes protected - making it so that the table doesn't expand.

Is it possible - and practical - to have the cells in B:H lock only when they're selected? Or do you have another solution?

Thanks again.

**jaslake** · 02-07-2011, 06:59 PM

Hi hektisk

Didn't know we were dealing with Tables. Tables are a bit of a different animal. I've not much experience with Tables coupled with VBA but have some ideas. Get back to you.

**hektisk** · 02-07-2011, 10:43 PM

I found some code online and came up with this:

Please Login or Register  to view this content.

Basically, it protects the sheet only when b3:h3 are selected. Since the sheet isn't protected when I'm in cell A17, it allows me to enter a new row.

This seems to accomplish what I'm trying to accomplish, but as I'm open to comments/criticisms of this approach!

I'm thinking that one possible issue is if I were to protect other portions of the worksheet (which I would), but I suppose I could always add lines to the above code to protect the sheet when those cells are selected as well.

**jaslake** · 02-07-2011, 11:11 PM

Hi hektisk

My personal opinion? Congratulations!! Solving your own issue is most gratifying and is the "best solution" simply because you developed it.

There may be more efficient methods and those can be implemented at an appropriate time...you've whetted my appetite with this issue (Tables specifically) and I'll figure it out. When/If I do, I'll let you know.

In the meantime, go with what works until you get a better solution.

You may need to change your Target Range to allow for expansion.

**hektisk** · 02-08-2011, 08:39 AM

Thanks so much, John! It is gratifying, though I do look forward to being able to draft my own code, but I'll get there. This was a good start.

Please let me know if you find any other creative solutions - I'm very intersted in learning about them.

Thank you everyone - I'll mark this as solved!

Originally Posted by jaslake

Hi hektisku

My personal opinion? Congratulations!! Solving your own issue is most gratifying and is the "best solution" simply because yootu developed it.

There may be more efficient methods and those can be implemented at an appropriate time...you've whetted my appetite with this issue (Tables specifically) and I'll figure it out. When/If I do, I'll let you know.

In the meantime, go with what works until you get a better solution.

You may need to change your Target Range to allow for expansion.

**jaslake** · 02-08-2011, 04:58 PM

Hi hektisk

If you're still interested, see the attached. I believe I got it working with the Table.

**jaslake** · 02-10-2011, 05:17 PM

Hi Adam

Based on our PM's I'm attaching a new file with code that works as I described. I'd recommend once again that perhaps you should open up this thread as Unsolved. You may well be able to do that the same way you marked it as solved...don't know.

**jaslake** · 02-11-2011, 12:19 AM

This is the description of what I perceive the OP is looking for (could be way off the mark)

I've studied your worksheet "Outcomes" to see if I could figure out how it's working and to see if I could make sense of how I think you want things to happen.
Based on that, I've made the following assumptions:

1. That nothing should happen in Table 1 until an entry is made in the last row of Column A in worksheet “Outcomes”. I’ve unhidden Column A in the file for my purposes. It can be hidden if you wish.

2. Once an entry is made in the last row of column A (call it New Row), the following things happen:

Table 1 is expanded to include this New Row
Cell X of this New Row in Table 1 is unlocked.
All other columns of the New Row in Table 1 (Y through AG) remain locked.

3. Now, move on to Table 1

It appears to me that Column Z should be locked at all times as it has a formula. Accordingly, it’s been locked and never gets unlocked in the procedure.
New Row Cell X is unlocked and available to the user as described above.
All other New Row cells in Table 1 (Y and AA-AG) remain locked until an entry is made in Column X.

4. Column X Entry

If the user leaves X empty, all new row cells (Y and AA-AG) remain locked.
If the user enters “New Assessment (No DC)” in X then all new row cells (Y and AA-AG) remain locked.
If the user enters anything else in X, then all new row cells (Y and AA-AG) get unlocked and become available to the user.

5. In Table 1, ALL cells below the last row are locked. The appropriate cells become unlocked and available as described above.

This describes how I perceive the flow of things and so is how the code is written. Let me know where I went off the rails. I’ve attached your file with the code included to a new post to your thread; you can download it from there

**snb** · 02-11-2011, 06:31 AM

Please Login or Register  to view this content.

**hektisk** · 02-20-2011, 12:27 PM

OK, this is the flow:

1) A new patient comes in. We enter their demographic and clinical information, which remains static, into the table found in 'Client Demos'.

While the table accomodates multiple insurances, the vast majority of patients accepted have Medicare as their primary insurance. Medicare covers up to 100 days following a hospitalization, and these 100 days are broken up, for reimbursement purposes, into multiple assessment periods. For example, the first assessment done, which is called the 5-day assessment, covers payment from days 1-14, while the second assessment, called the 14-day assessment, covers payment from days 15-30.
So...

2) At the end of an assessment period (for example, on day 14 or day 30), OR upon discharge, the sheet 'Outcomes' is used. The patient's MR # is entered into column A and admit date is entered into column C. From those two pieces of data, the demographic and clinical information found in 'Client Demos' is copied over into 'Outcomes'. (Note that a patient who has been with us two or more times has the same medical record number, which is why column A (MR #) and column C (Admit Date) must be filled in for D:V to calculate correctly.)

3) The assessment type is entered into column B (for example, 5 day), and then W:Y is entered in by the user. Column W, 'RUG Category', represents the reimbursement code for that period, while X ('New Assessment or D/C') is where either the discharge location is entered, or where New Assessment (No DC) is entered. This latter option is used when the patient exhausts an assessment period, but has not yet been discharged from service. It's in this circumstance where AA:AG should NOT be filled out, as it will be inaccurate and incomplete.

Take a Medicare patient John Doe with the MR # 1050 and an admit date of 10/1/2010. Upon admission, his admission information is entered into worksheet 'Client Demos'. At the end of his first assessment period (day 14), the MR # is entered into the 'Outcomes' worksheet, column A, and admission date into column C. '5' is entered into column B, as the information we'll be using is for his 5-day assessment. Columns D:V are now copied over (these can be hidden, but are there for convenience). His RUG category (reimbursement) is then entered into column W. John Doe was not discharged at the end of his assessment period, so 'New Assessment (No DC)' is entered into column X. Accordingly, AA:AG remain locked.

Let's say that John Doe is then discharged on day 23 - part way through his second assessment period. Again, his MR # and initial admit dates are entered into columns A and C, respectively, while '14' is entered into column B, reflecting that this is his second assessment period. His discharge location - say, 'Home' - is entered into Column X. This unlocks AA:AG, as the clerk can now enter in some final clinical outcomes from his stay.

I hope this helps clarify the intent of the questions. Thank you as always.

Adam

**hektisk** · 02-20-2011, 06:09 PM

I've been playing around with John's code for a little while now, and it seems to do exactly what I need it to do. I made a small adjustment reflecting the differences between the assumed flow and the actual flow by unlocking columns B, C, W and Y, and so far, so good.

There is just one issue, which I believe to be unrelated to the VBA, which is that both ranges 'Age_Outcomes' (column I) and 'Rug_Category_Outcomes' (column W) do NOT copy their respective formulas down. No other column seems to have this difficulty - when I add a new row to column to the table, every other formula copies down appropriately. Any idea why this is?

**jaslake** · 02-20-2011, 11:52 PM

Hi Adam

I'm traveling on Family business until Tuesday. Hopefully someone will address your most recent issue before then. If not, I'll look at it as soon as I can.

**hektisk** · 02-21-2011, 01:04 PM

Great, thanks.

**jaslake** · 02-21-2011, 07:03 PM

Hi Adam

I have no clue why

both ranges 'Age_Outcomes' (column I) and 'Rug_Category_Outcomes' (column W) do NOT copy their respective formulas down

I'm certainly no expert on Tables but it appears that they SHOULD copy down but...they don't.

This can be made to happen in the code with something like this

Please Login or Register  to view this content.

with similar code for column W (Column W does not appear to be a formula in the sample file...so, the previous VALUE will be copied and pasted).

You can place

Please Login or Register  to view this content.

at the bottom of the procedure as it only needs to be present one time to clear the clipboard.

One other point...I obviously don't know your process. If you're using Named Ranges elsewhere in your code, you may wish to make the Named Ranges Dynamic. There's plenty of resources on the Web to explain how to do this if you're interested.

If you have issues or questions, please ask.

**hektisk** · 02-21-2011, 10:15 PM

The named ranges do expand automatically, if that's what you mean. =Age_Outcomes automatically expands when a new row is added to Table1, for example. So, I don't think that's the issue. Even though the range expands, however, the formulas still do not copy down.

Column W is actually a list (drop-down). I also notice that column H, date of birth, shows as a general number and not as a date. I have tried setting the whole column to format as date. I think what's happening is that when a new row is added, columns D:V show as #N/A until a date is selected in column C, and this #N/A is resetting the formatting.

What is the code for formatting a range?

The spreadsheet is 99% the way there! Just these few last quirks...

Thanks for your patience

**jaslake** · 02-21-2011, 10:46 PM

Hi Adam

Yes, this is what I meant

The named ranges do expand automatically

Guess I didn't see that happening.

Regarding this

What is the code for formatting a range?

It'll be something like this

Please Login or Register  to view this content.

Or you can use the LR code previously used to do this (my personal preference)

Please Login or Register  to view this content.

Let me know what I can do to help with that last 1%.

**hektisk** · 02-21-2011, 11:53 PM

I've made some alterations and have been playing around with it for the past 20 minutes or so...so far, so good. The only thing I can't figure out how to do right now is delete rows without a 'Run time error '13': Type mismatch' popping up. Not the biggest deal in the world, but if there's an easy workaround I'd certainly like to add it.

I may train someone on this tomorrow, and through that training I may (or may not!) find additional quirks. I'll keep you updated

**jaslake** · 02-22-2011, 01:09 AM

Hi Adam

I'd need to see what you're doing here

delete rows without a 'Run time error '13': Type mismatch' popping up

Work through it and let me know where you need help.

Sounds like you're close. Yes, please keep me updated.

**hektisk** · 02-22-2011, 09:16 AM

When I try to delete a row in the sheet 'Outcomes', I receive that run-time error, and the debugger highlights

Please Login or Register  to view this content.

.

**jaslake** · 02-22-2011, 12:17 PM

Hi Adam

The reason you're getting the error is because deleting a row fires the Change Event Macro. I've added a few lines of code and the issue APPEARS to be corrected. Please test throughly.

Please Login or Register  to view this content.

**hektisk** · 02-23-2011, 09:52 PM

Hi John - This additional code does indeed remove the error when I'm deleting a row that is NOT the last row in the table. Unfortunately, it still gives me a run-time error when I delete the last row.

**jaslake** · 02-23-2011, 09:56 PM

Hi Adam

That's why I wanted you to test it throughly. I'll look at this

it still gives me a run-time error when I delete the last row.

Get back at you ASAP.

**jaslake** · 02-23-2011, 11:51 PM

Hi Adam

I can't duplicate this

it still gives me a run-time error when I delete the last row

In the attached file I can delete the last row without incident.

**hektisk** · 02-26-2011, 08:16 PM

Ah, I fixed it - when I made modifications to the code I excluded an 'End If'! Seems to work now. Thanks

Conditional Unlocking

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Re: Conditional Unlocking

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions