VBA code and merged cells

jdluke6545

New Member
Joined
Jul 30, 2021
Messages
44
Office Version
  1. 2019
Platform
  1. Windows
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....
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Short answer is NO....VBA and merged cells are kryptonite in Excel.
Merged cells should have been removed from Excel at birth.
Another option to merged cells is.....Highlight the cells >>RClick>>Format cells>>Alignment tab>>Horizontal Dropdownbox>>Center across selection
 
Upvote 0
How are you identifying which cells need to be unlocked ?
I have had a play with it and locking / unlocking seems to require either of:-
  • "Select" any cell that is part of the merged range
    OR
  • Use a range object which is the physical cell address of the merged cells.
    eg Range("A1:D5").Locked = False
    where "A1:D5" is exactly the cells that have been merged
 
Upvote 0
In the second option, you can change that to
Range("C3").MergeArea.Locked = False
Where C3 is "any" cell that is part of the merged cell
 
Upvote 0
Thanks for the replies.
So Alex, you may be on to something. I can see where using "Select" might do something but I am not sure how to properly write that in the code.
Let me upload a screenshot of my sheet and follow that with some instructions on what I am trying to accomplish.
Gimme a min...…
 
Upvote 0
Here is what I am trying to do.. See Screenshot also
Merged Cells are Columns A:F (6 columns merged) and Columns G:AQ (37 Columns merged)


If there are the letters "NT" or "ST"(without quotes) in one of the cells in the ID column then the corresponding adjacent DESCRIPTION cell will unlock.

Otherwise if the ID column is blank or has a number then the DESCRIPTION Column cells are to remain locked.

So, only the "NT" or "ST" in the ID column will UNLOCK the cell.

All cells in the ID column are formatted as UNLOCKED cells

All cells in the DESCRIPTION column will have formulas and are formatted as LOCKED cells

The QTY Column is formatted as UNLOCKED but has no relevancy to this scenario

The COST Column will have formulas and are formatted as LOCKED but has no relevancy to this scenario

EXAMPLE of what I am trying to accomplish:

Cell A5 has the number 55 so cell G5 should remain LOCKED (this should be true for any number)

Cell A6 is BLANK so cell G6 should remain LOCKED

Cell A7 has the letters NT so cell G7 should switch to UNLOCKED

Cell A8 has the number 22 so cell G8 should remain LOCKED

Cell A9 has the letters ST so cell G9 should switch to UNLOCKED

All the rest of the cells in the ID column are blank and so all the adjacent cells in the DESCRIPTION column should remain LOCKED

So ALL cells in the DESCRIPTION column should always remain LOCKED unless a cell in the ID column has the "NT" or "ST" in it.

The purpose for this is from time to time I will need to override the formula in a particular DESCRIPTION cell but do not want to UNPROTECT the whole sheet just to override a few cells. If the ID cell has a number then a formula in the DESCRIPTION cell will take over.

But if I want to enter a specific description in the DESCRIPTION cell then I will enter NT or ST and the adjacent cell will unlock to allow me to enter the specific data.

I already have conditional formatting applied to the DESCRIPTION column cells so my "fix" needs to be a VBA code.
 

Attachments

  • Capture.JPG
    Capture.JPG
    132.2 KB · Views: 51
Upvote 0
I can't look at it right know but I will have a look at it later this afternoon (I am on Sydney Aust time).
 
Upvote 0
This might get us started
VBA Code:
Sub MM1()
 Dim r As Long
 For r = 2 To Cells(Rows.Count, "B").End(xlUp).Row
    If Cells(r, 1).Value = "ST" Or Cells(r, 1).Value = "NT" Then
        Cells(r, 2).MergeArea.Locked = False
    End If
Next r
End Sub
 
Upvote 0
@Michael M, you just snuck in before me. Let me know if I haven't gotten this right.

@jdluke6545
See if this does the job.
The sheet will need to be unprotected first.

VBA Code:
Sub FindAndUnlockRelated()

    Dim searchRng As Range
    Dim ws As Worksheet
    Dim findStr As String
    Dim foundCell As Range
    Dim firstMatch As String
    Dim findArr() As String
    Dim i As Long
    
    findArr = Split("NT,ST", ",")

    Set ws = ActiveSheet
    
    Set searchRng = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
    
    For i = LBound(findArr) To UBound(findArr)
        findStr = Trim(findArr(i))
        Set foundCell = Cells.Find(what:=findStr, LookIn:=xlFormulas, MatchCase:=False, lookat:=xlWhole)
        firstMatch = foundCell.Address
        
        Do While Not foundCell Is Nothing
            Set foundCell = Cells.FindNext(foundCell)
            foundCell.Offset(0, 6).MergeArea.Locked = False
            If firstMatch = foundCell.Address Then Exit Do    
        Loop

    Next i
End Sub

XL2BB of the sample data

20210731 VBA Merged Cells Locked Cells and Identification.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1ID #Description
255This cell to remain locked
3This cell to remain locked
4NTThis cell to remain UNLOCKED
522This cell to remain locked
6STThis cell to remain UNLOCKED
7This cell to remain locked
855This cell to remain locked
9This cell to remain locked
10NTThis cell to remain UNLOCKED
1122This cell to remain locked
12STThis cell to remain UNLOCKED
13
Data
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,756
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top