VBA code and merged cells

[jdluke6545]

I am needing a VBA code to unlock cells but when looking for an answer I came across a thread that said VBA and merged cells don't work.
Well I have merged cells and there is no way I can redo the sheet and not have merged cells.
is there any way to get VBA code to play nice with merged cells?
I can post images of my sheet that I need help with VBA code If anyone has a possible solution to this problem first.
Or I can try the XL2BB thing.... never used that before tho....

 

[Michael M]

Simple answer is "NO"
The code both Alex and I provided works perfectly.
There has to be an issue with your process !!

 

[jdluke6545]

Your password gets entered into the code here....and is usually a text string.
Did you protect the worksheet manually first using the correct password ( the same as the one that is in the code) ??
Then you run the code

[/B]
Sub MM1()
Dim r As Long
activesheet.Unprotect Password:=[COLOR=rgb(184, 49, 47)][B]"your password"[/B][/COLOR]
For r = 35 To 71
If Cells(r, 1).Value = "ST" Or Cells(r, 1).Value = "NT" Then
Cells(r, 7).MergeArea.Locked = False
End If
Next r
End Sub

Once the password is within the code structure, it won't be needed again as the code will store that password

yes. I entered my password where you said "your password"
Pasted the code, edited the password, then protected the sheet.

 

[jdluke6545]

... and no quotes around the password

 

[Alex Blakenburg]

Can you try the "whole" of my code in post #42 in the form sheet's vba module.

 

[jdluke6545]

Simple answer is "NO"
The code both Alex and I provided works perfectly.
There has to be an issue with your process !!

Hummm,,, well let me work on getting a screen recording of my process and I'll upload it to my drop box.
will have to be tomorrow sometime. It's now 1:00 AM here and a storm is in process and power has blinked twice.
So I will work on that later and post back.

My THANKS to all of you tho!!!!

 

[jdluke6545]

Can you try the "whole" of my code in post #42 in the form sheet's vba module.

Oh..... yes I can. I thought I did at some point during all of this.
But yes, I will try it tomorrow.
I'm afraid the storm is going to knock power out here very soon. it has blinked again once since my last post.
Thanks Alex for reminding of that code in #42!

 

[jdluke6545]

@ Alex Blakenburg
Well preliminary test shows that your code in post #42 looks like it is working.
I will test more later, but it seems to be doing what I need.
After I do some more testing I will mark this thread as solved.
Thank all of you for your hard work on this!!

 

[Alex Blakenburg]

Thanks for the update.

 

[jdluke6545]

UPDATE:
Sorry guys for the long delay but I have been having to deal with some family medical emergencies....
Anyway,,,
While the code does seem to work as requested, there is one glitch. I am trying to look into what may be causing it.
The glitch is, if I type a mistake number and go back to the cell with the incorrect number in it and hit "delete" I get an error message.
The error message says:

Run-time error '13':
Type mismatch

but if I just type over the wrong number with the correct number then it seems to work.
Just when ever I use the delete key it gives the error.
The line in the code that highlights when debugging the error is:

If Target.Value = "ST" Or Target.Value = "NT" Then

I sorta get what it is saying... I guess its looking for an input of only a number or one of the two letter sets and does not allow for a delete key input.
This normally would not be an issue except that I have another VBA code in a module that is linked to a button that goes thru the sheet and deletes all the inputed data.
That code not only deletes the all inputed data but also "resets" some check boxes.
So it saves a lot of time from manually going thru and highlighting cells and deleting them and re-checking and un-checking check boxes.
Even if I were to do this manually I would still get this error when I use the delete key manually to clear the cells.
This is a sheet I use on a regular basis but every time I use it I need to start with a new clean sheet. To me, this is a template of sort.
Oh, I am using the delete key in the ID column where I would enter a number or the one of the two letter sets: NT or ST.
Other than this glitch, it seems to be working as I need.

 

[Alex Blakenburg]

UPDATE:
Run-time error '13':
Type mismatch

Hit another little quirk of merged cells.

See if this works for you:-

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngID As Range
    Dim ws As Worksheet
    Dim pwd As String
    
    Set ws = ActiveSheet
    Set rngID = Range(Cells(35, 1), Cells(71, 1))
    pwd = "test"                            ' XXX PASSWORD
    
    If Not Intersect(Target.Cells(1, 1), rngID) Is Nothing Then
        
            If Target.Cells(1, 1).Value = "ST" Or Target.Cells(1, 1).Value = "NT" Then
                ws.Unprotect pwd            ' XXX PASSWORD OFF
                Target.Offset(0, 1).MergeArea.Locked = False
                ws.Protect pwd              ' XXX PASSWORD ON
                
            ElseIf Target.Offset(0, 1).MergeArea.Locked = False Then
                ws.Unprotect pwd            ' XXX PASSWORD OFF
                Target.Offset(0, 1).MergeArea.Locked = True
                ws.Protect pwd              ' XXX PASSWORD ON
            End If
    End If
End Sub