Using Offset Properly

Question

I have this code:

If Not (Application.Intersect(Target, Range("E2:F5000")) Is Nothing) Then
       If StrComp("pp voice", Target.Value, vbTextCompare) = 0 Then
          Target.Value = "PP Voice"
         'disable app activation cell
       End If
End If

That code is fine, does what it's supposed to. What I also need it to do, is disable the cell in the same row from column M. Like disable it and fill it Grey or something.

Regards, Ben.

Are you looking to lock a cell in column M of the `Target` row, if `StrComp() = 0`? — BruceWayne, Jul 20 '17 at 20:55
Yep. That IF statement just cleans up peoples crappy typing, but I also need the cell in column M locked for certain values. (The ones in the StrComp) — Ben Logan, Jul 20 '17 at 21:03
I was looking at something like, `Target.Offset(Target.Row, 8).Locked = True` But that doesn't seem to do a thing. — Ben Logan, Jul 20 '17 at 21:18
Try `Cells(Target.Row,13).Locked = True` If that doesn't lock the cell, then take a look at [this SO thread](https://stackoverflow.com/questions/3037400/how-to-lock-the-data-in-a-cell-in-excel-using-vba), or [this one](https://stackoverflow.com/questions/17111648/how-to-lock-excel-cells-in-vba) — BruceWayne, Jul 20 '17 at 21:22
`Target.Offset(0, 8).Value = "this one"` - this targets the correct cell, because the value changes, but if I do `.Locked = True`, it has not effect. — Ben Logan, Jul 20 '17 at 21:29

score 0 · Answer 1 · answered Jul 20 '17 at 21:41

0

Nevermind, worked it out. Unlocking entire sheet, then relocking solved it.

   If StrComp("pp voice", Target.Value, vbTextCompare) = 0 Then
     Target.Value = "PP Voice"
     ThisWorkbook.Sheets("Sales Entry").Unprotect "password!"
     Target.Offset(0, 8).Locked = True
     ThisWorkbook.Sheets("Sales Entry").Protect "password!"
   End If

Thanks for your input!

answered Jul 20 '17 at 21:41

Ben Logan

187
10

I'm going to bet it's because you have it locked, therefore can't do anything to the cell. Instead of doing the unlocking in the `If` statement, try just adding it at the beginning of your sub, then locking it at the end. That will also probably speed it up, since it's not locking/unlocking each time. – BruceWayne Jul 20 '17 at 21:43
That's exactly what I did, as there is about 25 of those IF statements lol. Cheers – Ben Logan Jul 20 '17 at 21:47

Using Offset Properly

1 Answers1