Stop user editing cells - Excel

[aikidokid]

I am trying to stop the user from being able to edit some of the cells in the worksheet, such as column titles etc.

I have tried the Locked property, but this doesn't seem to work as I can still change the titles.
I have looked through the help files and the properties, but I can't seem to find one that works, or maybe I am not calling them correctly, althought those that I have tried I copied from the VB help files!

[koolsid]

Hi

You need to protect the sheet after locking the cells...

Hope this helps...

[aikidokid]

I have this code here and in the Protect worksheet I have put a tick in the unlocked cells, but the whole sheet seems to be locked?

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A1:AI7").Locked = True

[koolsid]

By default the property of all cells in a worksheet are set to Locked=true.
If you don't want specific cells to be locked then set their locked property to false

for example

'assuming this is the range you want to unlock
    Range("A1:F1").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

or if you don't want just the column headings to be changed then select all cells in the worksheet. Set the entire cells locked property to false and then change the property of the relevant cells to true

for example...

'entire worksheet
    Cells.Select
    Selection.Locked = True
    Selection.FormulaHidden = False
 
    'assuming this is the range you want to unlock
    Range("A1:E1").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Hope this helps...

[Mitch_s_s]

you need to protect the sheet for this to work

[koolsid]

you need to protect the sheet for this to work

@Mitch: Refer to post#2

[aikidokid]

In the sheets properties dialog I have changed

EnableSelection TO xlUnlockedCells

Now to stop the cells in the first 7 rows to be edited, I would use this code is that correct, and would I put it in the sheets 'Activate' sub?

'entire worksheet
    Range("A1:AI7").Select
    Selection.Locked = True
    Selection.FormulaHidden = False

My command button is in "A1" - Which consists of "A1:A6" merged cells.

Locking this, would it stop the user being able to select the button, or should I just change the Range in the code to

Range("B2:AI7").select

[koolsid]

Locking this, would it stop the user being able to select the button,

locking/unlocking cells don't effect the properties of Commandbutton.

you need to right click commandbutton and set it's locked property to "true" in case you don't want user's to select it... once it is done, protect the sheet to see the effect...

[aikidokid]

Hey koolsid, thanks again, but I think I am missing something here.

If I added my worksheet to an attachment would you take a look at it for me just to point me in the right direction.
I am not asking you to do anything to it, just let me know where I am going wrong.

[koolsid]

Definitely...

[aikidokid]

What I am rtying to recreate is a matrix style sheet that I can use to see which drivers have been either fully trained or still need training.

If the drivers records are complete then the drivers name is in white on a black background. (This could be changed - I just want it to stand out to make it easier to see who is not fully trained)
If a record is incomplete then the empty cell should be coloured Yellow. (Don't ask me why, that's just what they want)
If when I manually enter onto the sheet some data, the yellow cell should become white, provided the data meets the requirement.
Then to check if all cells in that row are full, with the exception of the last cell which is optional, make the first cell in the row as above. (White text on Black back colour)

I have a lot of this, but when the sheet is protected, all sorts of errors seem to occur.

There is no password to protect the sheet. I have ticked the allow insert rows and allow autofilter options. I am not quite sure to do with the cells to allow as I am getting mixed up on that one.
I want to let them alter any cell after the 7th row.

By the way, I have removed the code to close all the forms as this is still looping when it shouldn't .

P.S. Is there a way to stop the first column being selected when the user selects the command button?

[koolsid]

P.S. Is there a way to stop the first column being selected when the user selects the command button?

I want to let them alter any cell after the 7th row.

this is not possible... cause the very first cell in the Eight Row i.e. A8 will be from the 1st column...

If you want that the command button can be selected and any cell in the range A8:IV65536 can be selected then that is possible.

If the drivers records are complete then the drivers name is in white on a black background. (This could be changed - I just want it to stand out to make it easier to see who is not fully trained)
If a record is incomplete then the empty cell should be coloured Yellow. (Don't ask me why, that's just what they want)
If when I manually enter onto the sheet some data, the yellow cell should become white, provided the data meets the requirement.
Then to check if all cells in that row are full, with the exception of the last cell which is optional, make the first cell in the row as above.

For all this you don't need to write a code. you can achieve the same by conditional formating...

I could see a lot of forms and lots of coding..... need some time to understand the same....

[aikidokid]

For all this you don't need to write a code. you can achieve the same by conditional formating...

I wish I had worked this out about a week or two ago

Is this what you mean to stop the first column being selected in the cmdAdd_Click :

'stops the first column from being selected every time the cmdButton is selected, 
Range("GG600 ").Select

Cos if it is, I got this wrong as well.

I could see a lot of forms and lots of coding..... need some time to understand the same....

you're a brave person to even try to understand what I have written - I struggle most of the time

I thought VB was hard enough, trying to move to VBA where a lot of VB things don't apply is, well ......

[Mitch_s_s]

im findin it hard goin the other way lol
vba - vb

[aikidokid]

im findin it hard goin the other way lol
vba - vb

I'm lookin hard in the small print to see where it says it's all easy to do any of this.

[koolsid]

Whoa!!!

There are many errors

Okay, what is your deadline for this project? (this is the 1st question that comes to my mind...)

[aikidokid]

Whoa!!!

There are many errors

Okay, what is your deadline for this project? (this is the 1st question that comes to my mind...)

I probably have until the end of March I expect, But I could probably get an extention as I am doing it as a favour.
They also know I am not a professional programmer so there would be a fair bit of tolerance with it - I'm hoping.

What do you mean you can tell I'm not a professional programmer .....
HOW!

[koolsid]

What do you mean you can tell I'm not a professional programmer .....
HOW!

I never said that...

I am still working on the file. I am sorry but I think we need to remake the file...

If you are game(I am game) then lets start from the beginning...

What are the exact requirements? If you can list it out pointwise, it'll help me understand much more clearly...

[aikidokid]

I have PM'ed you and I will post a newer Excel sheet once I think I have got close to the conditional formatting I am after, or when I finally give up

[koolsid]

ok

[aikidokid]

Ok give up ... well almost

I have one of the conditions sorted.

Before I do post what I have, can you give me a clue on how to tell whcih row the user has been editing, and also some way to find out how many columns there are used in my sheet.
I can get the total columns in excel, but not just the ones I have used.

(I don't want to hard code this as in the future, although distant it may be, there may be new training items to be added in a new column)

[koolsid]

I can get the total columns in excel, but not just the ones I have used.

let's say the values in
cell A1 = "john"
cell B1 = "andy"
cell C1 = "koolsid"
cell D1 = "vbforums"
cell E1 = "office"

This will give you the column number of the last cell in the used range...

Private Sub CommandButton1_Click()
 
MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Column
 
End Sub

Hope this helps...

[aikidokid]

Thanks koolsid

I am getting there

One more question and I may have got what I am looking for - well this bit anyway

If I have the row number stored in an Integer called "RowNum" and I now have the last column used stored in a string "LastCol" how do I join then up to give me something like ?

Range(LastCol & RowNum)

Just seem something about cStr so going to take a look into this in the mean time.

[aikidokid]

Hmm...

I am using this
LastCol = Range(OldCell.Address).SpecialCells(xlCellTypeLastCell).Column
to store the last column letters (Name) in the row that is used and has data in it.

Firstly, is this correct?
Secondly, if it is, why when I have 35 columns does it return '70'

So this way I wouldn't need the Column Letter, e.g AH, just the number of columns to loop through.
To get this just add the column number from the active cell to the above total.

Or maybe, just maybe, I misunderstood what your last post meant.

[koolsid]

If I have the row number stored in an Integer called "RowNum" and I now have the last column used stored in a string "LastCol" how do I join then up to give me something like ?

Range(LastCol & RowNum)

Does this help?

Private Sub CommandButton1_Click()
 
    aaa = "A"
    bbb = 1
    
    'if you want the address then
    MsgBox Range(aaa & bbb).Address
    
    'if you want the value then
    MsgBox Range(aaa & bbb).Value
    
    'and so on...
    
End Sub

[koolsid]

LastCol = Range(OldCell.Address).SpecialCells(xlCellTypeLastCell).Column

what is oldcell.address?

[aikidokid]

oldcell.Address is the location of the cell that has been edited, before it loses focus to the next cell.

I found this on this forum

It is declared as OldCell As Range

[koolsid]

Hi

Have you gone thru Si's tutorial on Excel?

http://vbforums.com/showthread.php?t=391665

see post no-8 in the above tutorial