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]

Umm...is the worksheet protected? ?

 

[Alex Blakenburg]

@Michael M , technically the offset to get from A to G is 6. It is zero based
? range("A1").Offset(0,6).Address
$G$1

Having said that it looks like because we have 2 sets of merged column butting up against if I only offset by 1 I get to G, so I should probably change my offset from 6 to 1.
Both work because they are in the merged cell range and we are adding the MergeArea property.

 

[Michael M]

True but we aren't using offset in the code

 

[Michael M]

I think we have proven the point though. ..never use merged cells with vba ??

 

[Alex Blakenburg]

Totally agree

 

[vincent86kapelski]

jour je vous mes une video comment reparer un pc portable

Oke.io

Download Files Safely - All links are thoroughly verified for viruses, malware, phishing, malicious content, session stealing, cross-site scripting attacks, etc

oke.io

 

[jdluke6545]

@ Michael M​

I uploaded a copy to dropbox. here is the link

VBA Unlock Test-full sheet.xlsx

Shared with Dropbox

www.dropbox.com

I dont have the code we have been working on in there in case pasted it wrong or worse put it in the wrong spot in the VBA editor.
Thanks again for your help!!

 

[Michael M]

The original code (with slight modifications) works as expected
But you do understand that Locked / Unlocked cells only work when the sheet is protected, otherwise locked / unlocked has no effect

Sub MM1()
 Dim r As Long
 For r = 35 To 71
    If Cells(r, 1).Value = "ST" Or Cells(r, 1).Value = "NT" Then
        Cells(r, 7).MergeArea.Locked = False
    MsgBox "Cells unlocked"
    End If
Next r
End Sub

 

[jdluke6545]

OK, thanks!!
I'll give that a try latter tonight
Yes, I Know, sheet protection must be active.

 

[Alex Blakenburg]

Yes, I Know, sheet protection must be active.

Just to clarify.
What / when are you expecting the macro to trigger ?
At the moment the macro is designed for a standard module, I am thinking you actually need a worksheet event ?
Where are you testing it from, a standard module ?
Are you saving the spreadsheet as an xlsm file ?
The macro would need to unprotect the workbook then unlock the cells then re-protect the workbook. Is that how you want it to work ?