Unlock cell based on location of active cell

[EssKayKay]

I’m not sure I want to do this but I’d like to try it. I am looking for some VBA routine that unprotects a cell based on the location of the active cell.

All cells in Column H are locked. If the active cell is in column M, I want the corresponding row/cell in Column H to become “unlocked”. If I move the active cell to some other column other than M then all cells in column H again become locked.

Example: if the active cell is M44, then I want H44 to become “unlocked”. If I cursor down to cell M45, then H44 becomes locked and H45 becomes unlocked. If the active cell is B3 then all cells in column H remain locked.

 

[kevin9999]

I'm assuming you don't want to lock all of column H if you select a cell in column H after you've already selected a cell in column M (why would you want to lock that cell again after you've just unlocked it?) so please try the following code. Change the password to whatever you use (or remove that requirement if not needed).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("M:M")) Is Nothing Then
        Me.Unprotect Password:="123"
        Range("H" & Target.Row).Locked = False
        Me.Protect Password:="123"
    Else
        If Intersect(Target, Range("H:H")) Is Nothing Then
            Me.Unprotect Password:="123"
            Range("H:H").EntireColumn.Locked = True
            Me.Protect Password:="123"
        End If
    End If
End Sub

 

[EssKayKay]

I'm assuming you don't want to lock all of column H if you select a cell in column H after you've already selected a cell in column M (why would you want to lock that cell again after you've just unlocked it?) so please try the following code. Change the password to whatever you use (or remove that requirement if not needed).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("M:M")) Is Nothing Then
        Me.Unprotect Password:="123"
        Range("H" & Target.Row).Locked = False
        Me.Protect Password:="123"
    Else
        If Intersect(Target, Range("H:H")) Is Nothing Then
            Me.Unprotect Password:="123"
            Range("H:H").EntireColumn.Locked = True
            Me.Protect Password:="123"
        End If
    End If
End Sub

Hello Kevin,

Thanks for your response. The reason I want to unprotect the cell in column H is because it is updated (copy>paste special) when a date is entered in column M. This is working great. However, once a date is entered into a cell in column M, if you highlight a populated cell in column M (i.e., one with a date) you receive an error message; blank cells in column M receive no error.

The error reads:
Error 1004 – the cell or chart you are trying to change is protected and therefore read-only. . . .

That makes sense because the cell in column H is protected so it cannot inadvertently be changed. If the cell in column H is marked as “unlocked”, then the error is not triggered. That is why I would like the error to not display if the user is simply cursoring down column M. Thus, each time a populated M cell is reached the error pops up.

Now I understand the error, so no big woof. However, some users (read – my sister) freak out when they see ANY error message.

Like I’ve alluded to, maybe this is just something they will have to deal with. But, if possible, I’d like just remove the error, or maybe display some other message that explains what’s happening, not the generic Excel message.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

I added a password to my spreadsheet; then added your code. I am able to open the spreadsheet without having to input the password which is great. However, I still receive the error message when a populated cell in column M is the active cell. Maybe I’m missing something which is more than likely.

I’m not sure this is possible but I’d like to upload my spreadsheet for you (or others) to review. However, I do not see that as an option. Any suggestions?

Thanks again,
Steve K.

 

[kevin9999]

Hi Steve, could you share your file via Google Drive, Dropbox or similar file sharing platform? Remember to share the file with anyone with the link.

 

[kevin9999]

Just wondering, did you unlock the cells in column M before you ran the code? Cells Format/Protection/ uncheck the Locked box.

 

[EssKayKay]

Kevin,

Yes, column M is always unlocked.
I’m not sure how to share files via Google Drive, Dropbox, etc. (maybe you could talk me through that) – sorry

I thought of a couple other ideas about this late last night (around 2:00 AM) that may make this easier.

• Rather than having to unlock the corresponding cell in column H maybe it would be easier to unlock all of column H anytime an active cell is in column M. If the user moves to some other column other than M, column H again becomes locked.
• Leave Column H always unlocked unless some cell in column H becomes active. That is if the active cell is in column H, then lock column H.

Thanks again,
Steve

 

[kevin9999]

if you highlight a populated cell in column M (i.e., one with a date) you receive an error message; blank cells in column M receive no error.

This is what I don't understand. That's why I need to see your file.
This will explain how to upload a file better than I can:
Upload files & folders to Google Drive - Computer - Google Drive Help

 

[EssKayKay]

This is what I don't understand. That's why I need to see your file.
This will explain how to upload a file better than I can:
Upload files & folders to Google Drive - Computer - Google Drive Help

I could not get Google Drive to work. I did upload a file but apparently it is not in Excel format but rather some other Google format. I thought in the past we could upload files directly here but apparently no longer. Oh well we tried.

Thanks for your support and help,
Steve K.

 

[EssKayKay]

This is what I don't understand. That's why I need to see your file.
This will explain how to upload a file better than I can:
Upload files & folders to Google Drive - Computer - Google Drive Help

Not sure but I may have been able to upload the file to Dropbox. I believe the link is -

Amortize.xlsm

Shared with Dropbox

www.dropbox.com

Hopefully this works, if not we'll just have to leave all as is. If successful, please read on. . . .

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Kevin,

I don’t expect you to spend much time on this as I realize this may be an effort in futility for me particularly since this old guy is not the most proficient spreadsheeter (read not at all). Therefore, if you wish to pass on this I totally understand.

I have included a sample of the AMORTIZE spreadsheet I have been working on. I originally downloaded this from the internet a few years ago and have made some changes since. As such, when you look at the code you are going to be amazed at how sloppy and inefficient it is. I have been pretty much piecing this together over time. However, for the most part, it works.

My concern right now is the error that pops up when a populated cell in column M is highlighted.
“Error 1004 – the cell or chart you are trying to change is protected and therefore read-only. . . .”

As a test, if you open the spreadsheet and highlight an empty cell in column M – say M37, then arrow up to M34 you will see the error message pop up. I believe this is happening because column H is locked. If we unlock a cell in column H (or the entire Column H) where a cell in the same row in column M is populated, then when we cursor up to the populated “M” cell the error does not appear.

For most users, this is probably no biggie but some become concerned when they see this (or any error message). I was hoping to find a way to disable this message or have some other message appear that states what’s happening.

Again, unless you know some easy resolution, I don’t expect you to rack your brain on this – it’s just something I’ve been looking at.

Once more, my thanks. . . .
Steve

 

[EssKayKay]

Here's another version (I usually save as an older XLS file) -

Amortize.xls

Shared with Dropbox

www.dropbox.com