is it psossible to update cells via a button but protect them so that noone can type anything into it ive got this code Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim a As String '' dim variable a = ActiveCell.Address '' detects what cell is being clicked a = Mid(a, 2, 1) '' check cell If (a = "E") Then '' column E MsgBox "You may not edit this cell" '' the msgbox Range("A1").Select '' jump to a1 End If End Sub this works for updating an individual column. i want to protect the entire column but if i do i cant use the buttons if there is no way around this how would i protect cells e6 : e26 can anyone help i would rather protect the whole column tho

I'm confused, but we'll get to that later. First, about your code: As far as I can tell, your code is supposed to prevent users from editing any cell in Column E. At least that what it does when I put it in a worksheet module. If that's the case, then I think the proper way to do this is like this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 5 Then MsgBox "You may not edit this cell" Range("A1").Select End If End Sub The ByVal Target As Range argument passes everything that Excel knows about the Target cell to the Sub procedure so that you can use the attributes in the Sub. "Buried" in the argument is the contents of the target cell, the column, the row, the format, etc. etc. Therefore you don't have to parse out the "E" from the ActiveCell.Address, you just have to check and see if the Target.Column = 5. Using Target is very powerful. OK, now back to my confusion... You said: this works for updating an individual column. i want to protect the entire column but if i do i cant use the buttons As far as I can tell your code prevents the updating of an individual column (specifically "E") and therefore protects it. I must be missing what you are trying to do.

im using a button to update a column the button highlights the top 4 cells this code is completly separate and it works i dont want anyone to change the numbers in these cells when i click the buton my code stops it from running because it thinks im trying to edit the cell. sorry about the confusion ive changed your code a little but it kind of works how do i get it to range from b6 : b26 ? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 2 And Target.Row = 6 Then MsgBox "You may not edit this cell" Range("A1").Select End If End Sub

Remember that I said the Target argument contains lots of information about the Target, including the address. If you specify the Target.Row and the Target.Column, you have essentially specified a single cell, so you might as well just use: If Target.Address = "$B$6" Then ... That said, I'm still confused... You say the code stops your buttons from working, yet you say the code works. Do you want the code to stop your buttons from working? Anyway, to make a SelectionChange (or Change) event work across a range, you have to check and see if the Target intersects with the Range. Please note that the Intersect method is one of those methods that is either Nothing or Not Nothing, so while the syntax might look a little strange, it is correct: If Not Intersect(Target, Range("B2:B6")) Is Nothing Then _ MsgBox "It's In There"

sorry im not that good at explaining things i dont want my code to stop the buttons from working thats my main problem i need a way that blocks the user from changing whatever is in the cell when the cell has been updated by the button

See if the answer in this thread helps... http://www.computing.net/answers/of...