PDA

View Full Version : Solved: adding locked cells via macro



Lindie
06-12-2008, 04:06 PM
Having used the "Protect cell or cells on sheet without protecting sheet" by simon Lloyd I would like to do the following.

add a button to my sheet that will run a macro to add a column to the protected range.
In Simon's great example one has to change the code manually. I need the user to click a button so the last completed column is automatically added to the range.

Any suggestions please?

Simon Lloyd
06-12-2008, 04:19 PM
I've not worked on the problem as such but if you change your range to be protected to perhaps:
Dim acColumn
acColumn = ActiveCell.Column
If Not Intersect(Target, Range(Cells(ActiveCell.Row, acColumn), Cells(acColumn.Rows.Count, acColumn).End(xlUp))) Is Nothing Then

Lindie
06-16-2008, 09:16 AM
Many thanks Simon; I'm fairly new to VBA and having used the code you suggested did not lead me anywhere. Any chance yo giving me a hint as to where to paste the code? I used your code from your "Protect cell or cells on sheet without protecting sheet" but got nowhere. all I'm trying to do is to protect a pupil registration sheet so that once new data is entered and the user clicks on "confirm" all the data-cells are locked.
L

Simon Lloyd
06-16-2008, 03:01 PM
Lindie could you upload a sample workbook please and i'll sort it for you!

Lindie
06-18-2008, 12:22 PM
That's a very kind offer Simon. Many thanks. This is quite a simplistic spreadsheet, an attendance register. I have tried to put a button on the sheet saying "correct" so it locks all comleted cells.
You will also see that some attendees have a ONP. Absent with a reason. These are not counted in the Total (Totaal). I guess there is a way to assign a '1' value to these ONP's so they are automatically totalled.
Whatever advice you are able to give is most apreciated.
L

Simon Lloyd
06-18-2008, 01:49 PM
I would like to do the following.
add a button to my sheet that will run a macro to add a column to the protected range.
....... I need the user to click a button so the last completed column is automatically added to the range. What constitutes a completed column?

I have tried to put a button on the sheet saying "correct" so it locks all comleted cells.
What is it you want cells or columns locked?, do you really want them password protected or would the cells just being locked suffice?


You will also see that some attendees have a ONP. Absent with a reason. These are not counted in the Total (Totaal). I guess there is a way to assign a '1' value to these ONP's so they are automatically totalled.

=SUMIF(D6:AK6,1) + COUNTIF(D6:AK6, "onp") and copy down!

Simon Lloyd
06-18-2008, 02:05 PM
If you just want the cells protected after they are filled then this should suffice:

Sub LockMyCells()
ActiveSheet.Unprotect Password:="password"
Application.ScreenUpdating = False
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas).Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect Password:="password"
Application.ScreenUpdating = True
End Sub
it unlocks all cells then locks only those cells that have data in them and reprotects the sheet!

Lindie
06-19-2008, 01:32 PM
The ONP thing works a treat. thanks. I only want to lock the columns with 1/0/ONP in them. Not the rest. I put the Macro you so kindly wrote into the sheet and the whole lot locks up.
a)How do I unlock it again and
b) how to lock the columns 1/0/ONP only or
is it possible to choose what cells should be locked?
A password is not needed.
L

Simon Lloyd
06-19-2008, 02:44 PM
the code only locks cells that contain a vaue of any kind or a formula nothing else! in your example almost all columns had 1/0/onp in them!

Lindie
06-19-2008, 03:35 PM
how to unlock them again?

Simon Lloyd
06-19-2008, 11:21 PM
unprotect worksheet (password = password) then select all cells (click small square next to the letter A of column A, right click in selected area choose Format cells, then choose Protection and uncheck the Locked check box, record your actions for use with a button!

Lindie
06-20-2008, 04:12 PM
It all makes sense now. Thanks ever so much.
One final thing: If on a particular day there is not a "0" zero in a cell I want to count the content of another cell. The formula I thought might work does not do that for me: =IF(AP43>0,(COUNT AT43,"pass"))
What am I getting wrong?

Simon Lloyd
06-21-2008, 12:57 AM
Where would the formula go?, what else would be counted? when you say not zero does this mean it can contain either another figure, text or be blank? what is it you are trying to achieve?

Lindie
06-22-2008, 11:28 AM
Sorry for the rather vague posting.
for all values in one column except for "0" I would like to count different values in another column.
column A has for instance 0, W, H, ON for each pupil in colum A there is a corresponding column D with values like M, L, C

If a pupil has a value W in column A I want to count the value in D, say M

I would then land up with a total of Ms , Ls Cs and the like.

I tried a formula along the lines of
if value in A6 is not 0 then count D6

Lindie

Simon Lloyd
06-22-2008, 11:43 AM
Sorry for the rather vague posting.
for all values in one column except for "0" I would like to count different values in another column.
column A has for instance 0, W, H, ON for each pupil in colum A there is a corresponding column D with values like M, L, C

If a pupil has a value W in column A I want to count the value in D, say M

I would then land up with a total of Ms , Ls Cs and the like.

I tried a formula along the lines of
if value in A6 is not 0 then count D6

LindieHow about supplying a proper workbook with a before and after? the workbook you previously supplied just had 1's, 0's or onp as you stated but it seems thats not the case? the above still does not make sense.

Lindie
06-22-2008, 01:39 PM
I'm not doing too well here; a sample worksheet is attached. We count achievement for those who attended on the last day of the week.
:think: Lindie

Lindie
06-23-2008, 01:42 PM
Having just come across postings on SUMPRODUCT would that work here for me?