Automate Macro and lock cells (Re-post)

**TheRetroChief** · 10-06-2008, 06:39 AM

Not sure this posted first time around, as myself and two others at around the same time all have one view and no replies!!! So here it is again...

Greetings.

I have a macro in Module1 of workbook "BAU" called "ExtractToSheets" which exports data depending on the contents of a cell (Thanks royUK et al)
At the moment however, you must run the macro manually to export the data.
Now, i have put some simple validation in column K of worksheet "BAU Data" within the "BAU" workbook. Whenever a cell within a predefined range with column K is changed to "Yes" i want the macro to run AND i want the row the cell is in to be locked so it cannot be edited further by the user. I have had too many users mess up previous sheets i have made to collect my data, and i wish to make this one somewhat more idiot-proof!!!
Any pearls of wisdom folks??

I would have attached a representation of my sheet, unfortunately because there is now a macro in my spreadsheet, our trigger-happy security software won't let me post it!! I still hope someone will be able to help.

Ta muchly,
Ali

**royUK** · 10-06-2008, 11:19 AM

The code shouldn't be difficult but as I said in the earlier post(http://www.excelforum.com/excel-prog...n-keyword.html) I think it will really slow input. I'll take a look at the previous example if you want.

**TheRetroChief** · 10-06-2008, 11:35 AM

Whatever you recommend captain.
In hindsight, don't worry about the automatic locking thingy. I think i can do that.
Basically i want it to run whenever a cell in column K is changed to "Yes" in a validation drop-down. I will do the locking based on what's in another cell.
Anything you can advise would be groovy.
Thanks

**royUK** · 10-06-2008, 12:06 PM

Try this, I have amended the code to protect/unprotect the sheet

Option Explicit
Option Compare Text
Private Sub Calendar1_Click()
    ActiveSheet.Unprotect "secret"
    With ActiveCell
        .Value = CDbl(Calendar1.Value)
        .NumberFormat = "dd/mm/yyyy"
        .Select
        End With
        Calendar1.Visible = False
        ActiveSheet.Protect "secret"
    End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("k7:k86"), Target) Is Nothing Then
        If Target.Value = "Yes" Then
            ActiveSheet.Unprotect "secret"
            Call ExtractToSheets
            Target.EntireRow.Cells.Locked = True
            ActiveSheet.Protect "secret"
        End If
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("i7:i86"), Target) Is Nothing Then
        ActiveSheet.Unprotect "secret"
        With Calendar1
            .Left = Target.Left + Target.Width - Calendar1.Width
            .Top = Target.Top + Target.Height
            .Visible = True
            ' select Today's date in the Calendar
            .Value = Date
        End With
    ElseIf Calendar1.Visible Then Calendar1.Visible = False
        ActiveSheet.Protect "secret"
    End If
End Sub

**TheRetroChief** · 10-06-2008, 12:36 PM

Looks tasty. I shall give it a whirl tomorrow.

So you reckon that will run when any cell in column is changed to "Yes" and will lock as well?

Here's the thing tho....thinking about it, people may not have to upload after every entry....
Cells in column J tell me whether something has been uploaded (i.e. the macro has moved that data). I've set it up so that when a cell in K is changed to yes, the corresponding cell in J and all those above it change to Yes as well - whenever you run the macro it copies whatever is there doesn't it, not just what has been added since the last time it was run?

To try and explin better (i sound like i'm waffling...):
If it locked everything with a Yes value in column J then that would be beautiful!
The problem is, does a macro take preference over a formula? which will be done first?
e.g. Let's say K13 is a yes and that's the last time the macro was run. If cell K25 is changed to Yes, all the data is exported isn't it, not just rows 14 to 25? I have formula in J which will change all the entries (J14 to J25) to "Yes" as a consequence of K25 being "Yes". I need all those with a Yes in column J to be locked. However if the Macro runs before the formula, after K25 has been changed to Yes, then it will only lock row 25 and row 14 will it not?

Does that rambling make sense??

**royUK** · 10-07-2008, 04:17 AM

Because the default setting for cells is Locked you will need to change this in the Format menu >- Protection.

If you don't want to copy rows that are Locked then the original code will need amending.

**TheRetroChief** · 10-07-2008, 06:29 AM

Roy,

Once again, i apprecate you giving up your time to help me....

I dont mind copying rows that are locked...all it will do is go over the top of the info already extracted won't it? I just dont want people fiddling with something after it has been extracted once, then when the macro is run again, the data will change.

Looking at the text only attachment...
Let's say the Macro was last run at K18
The data D19:I30 is subsequently entered. At this point J19:J30 will all be "No" and K19:K30 will all be blank or have "TBC" in them if people can be bothered to put it in.
When K30 is changed to a "Yes" by the user, J19:J30 will change to "Yes" due to the formula there. Because there is a "Yes" in column J the corresponding row will lock so it cannot be selected or edited by the user. A "Yes" in column J means that the data has been extracted therefore cannot be edited, all the subsequent rows will be unlocked due to a "No" in column J and can be edited freely until the next time a cell in K in changed to "Yes" at which point, all the predeeding rows are locked.

Can you see what i want to get to??

**royUK** · 10-07-2008, 07:40 AM

If you sue this code after making sure that all input cells are not locked, then when a cell in K is changed to Yes, the row will be locked to prevent further changes & the macro to copy data will run.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("k7:k86"), Target) Is Nothing Then
        If Target.Value = "Yes" Then
            ActiveSheet.Unprotect "secret"
            Call ExtractToSheets
            Target.EntireRow.Cells.Locked = True
            ActiveSheet.Protect "secret"
        End If
        End If
End Sub

**TheRetroChief** · 10-07-2008, 12:23 PM

It's not K that needs to be looked at to determine which rows to lock, as there may only be one or two in there with "Yes", it's rows that change to "Yes" in J after one has changed to "Yes" in K that need to be locked.
Unfortunately i haven't been able to spend any time on this today at all!

**royUK** · 10-07-2008, 12:27 PM

Try changing the range reference in the Intersect part

**TheRetroChief** · 10-08-2008, 07:29 AM

As suspected it ALMOST works.
the intersect does need to be in the k range as you suggest. It is Yes in that column that i want to trigger the Macro running. However, i wish to lock all rows that have a Yes in Column J! At the moment it only locks those with a Yes in K
This is so close!!!!

**royUK** · 10-08-2008, 07:44 AM

Surely once the worksheet is running properly the cells will be locked. It would be easier to just manually lock the existing rows that should be locked. From that point it should not matter.

**TheRetroChief** · 10-08-2008, 08:51 AM

I don't think i am explaining very well...or alternatively not understanding what you are getting at!!!

Ok....each month i shall issue a new copy of the spreadsheet. At that point, before it is first used cells C7:I606 will be unlocked. This is the area that will get filled by the user.

Let's say the user puts in 6 rows of data; they fully fill in C7:I12.

Once they have put the data in they will then put Yes into K12. Formulae will then automatically put Yes into J7:J12 as the macro will have placed the 6 rows of data into the correct sheet depending on who it is allocated to (D7:D12).

Once that has been done, i want the macro to lock C7:K12 as this data has been copied to the other sheets in the workbook and shouldn't then be edited. It can do this by looking to see which rows in Column J have a Yes in.

I am happy for people to edit whatever has been put in, up to the point they engage the macro by putting a yes somewhere in K

**royUK** · 10-08-2008, 09:14 AM

I think you will need to loop through J & lock all rows that contain yes. Can you attach an example of the current workbook?

**TheRetroChief** · 10-08-2008, 09:32 AM

Unfortunately, the security settings here won't allow me to post anything with a macro in it would seem. Do you have an email address i could perhaps send to?? Only if you have the time/inclination. We're so close to the solution i can almost smell it!!!