excel protecting cells in VBA

March 27, 2009 at 11:40:53
Specs: Windows Vista, pentium 4 processor
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


See More: excel protecting cells in VBA

Report •


#1
March 27, 2009 at 13:03:43
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.


Report •

#2
March 27, 2009 at 13:19:50
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


Report •

#3
March 27, 2009 at 18:04:14
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"


Report •

Related Solutions

#4
March 28, 2009 at 13:43:21
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


Report •

#5
March 28, 2009 at 16:24:03
See if the answer in this thread helps...

http://www.computing.net/answers/of...


Report •


Ask Question