-
Stop user editing cells - Excel
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! :eek:
-
Re: Stop user editing cells - Excel
Hi
You need to protect the sheet after locking the cells...
Hope this helps...
-
Re: Stop user editing cells - Excel
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?
VB Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Range("A1:AI7").Locked = True
-
Re: Stop user editing cells - Excel
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
VB Code:
'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...
VB Code:
'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...
-
Re: Stop user editing cells - Excel
you need to protect the sheet for this to work
-
Re: Stop user editing cells - Excel
Quote:
you need to protect the sheet for this to work
@Mitch: Refer to post#2 ;)
-
Re: Stop user editing cells - Excel
In the sheets properties dialog I have changed
Code:
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?
VB Code:
'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
-
Re: Stop user editing cells - Excel
Quote:
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...
-
Re: Stop user editing cells - Excel
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. :D
-
Re: Stop user editing cells - Excel
-
Re: Stop user editing cells - Excel
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?
-
Re: Stop user editing cells - Excel
Quote:
P.S. Is there a way to stop the first column being selected when the user selects the command button?
Quote:
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.
Quote:
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....
-
Re: Stop user editing cells - Excel
Quote:
For all this you don't need to write a code. you can achieve the same by conditional formating...
:eek: I wish I had worked this out about a week or two ago :cry:
Is this what you mean to stop the first column being selected in the cmdAdd_Click :
VB Code:
'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. :confused:
Quote:
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 :bigyello:
I thought VB was hard enough, trying to move to VBA where a lot of VB things don't apply is, well ......
-
Re: Stop user editing cells - Excel
im findin it hard goin the other way lol
vba - vb
-
Re: Stop user editing cells - Excel
Quote:
Originally Posted by Mitch_s_s
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. :wave:
-
Re: Stop user editing cells - Excel
Whoa!!!
There are many errors :D
Okay, what is your deadline for this project? (this is the 1st question that comes to my mind...)
-
Re: Stop user editing cells - Excel
Quote:
Originally Posted by koolsid
Whoa!!!
There are many errors :D
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.
:eek: What do you mean you can tell I'm not a professional programmer .....
HOW! :lol:
-
Re: Stop user editing cells - Excel
Quote:
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...
-
Re: Stop user editing cells - Excel
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 :rolleyes:
-
Re: Stop user editing cells - Excel
-
Re: Stop user editing cells - Excel
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)
-
Re: Stop user editing cells - Excel
Quote:
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...
VB Code:
Private Sub CommandButton1_Click()
MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Column
End Sub
Hope this helps...
-
Re: Stop user editing cells - Excel
Thanks koolsid
I am getting there :wave:
One more question and I may have got what I am looking for - well this bit anyway :rolleyes:
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 ?
Just seem something about cStr so going to take a look into this in the mean time.
-
Re: Stop user editing cells - Excel
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.
-
Re: Stop user editing cells - Excel
Quote:
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?
VB Code:
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
-
Re: Stop user editing cells - Excel
Quote:
LastCol = Range(OldCell.Address).SpecialCells(xlCellTypeLastCell).Column
what is oldcell.address?
-
Re: Stop user editing cells - Excel
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
-
Re: Stop user editing cells - Excel
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