XL 2003 VBA: Merge a Macro Code with a Worksheet Code

[TechTank]

Hi All,

I'm hoping someone can help with the following scenario:

I have a worksheet code that "auto-height's" a row with merged cells. The work sheet is protected with the cells required unlocked. However, when it executes it seems to lock the cell that contains the data and prevents users from deleting the row should they wish to do so. The code I'm using is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
ActiveSheet.Unprotect
With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
         MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
     ma.MergeCells = False
      c.ColumnWidth = MrgeWdth
       c.EntireRow.AutoFit
        NewRwHt = c.RowHeight
       c.ColumnWidth = cWdth
     ma.MergeCells = True
    ma.RowHeight = NewRwHt
   cWdth = 0: MrgeWdth = 0
End If
End With
Application.ScreenUpdating = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True, AllowDeletingRows:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    
End Sub

To get around this I can manually execute this macro:

Option Explicit
Sub Unlock_Cells()
ActiveSheet.Unprotect
    Range("D" & Cells.Rows.Count).End(xlUp).Select
        Selection.Locked = False
        Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True, AllowDeletingRows:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    
End Sub

I would, however, like to incorporate this so that once the Worksheet code has executed it runs the Macro code as well. I haven't been able to debug the 1st set of code to stop it from setting this cell to locked. (In the properties for the locked cell the box is shaded green for "Locked" and not the full "green tick" if you follow me.)

Can anyone help me merge the two codes together or know how to get the Worksheet code to "call" the macro?

My VBA skills are limited and I'm picking up a lot from the members here and trying to understand the code I'm finding.

Thank you for your time.

 

[VoG]

Try putting

Call Unlock_Cells

at the appropriate point of your worksheet code.

 

[TechTank]

Try putting

Call Unlock_Cells

at the appropriate point of your worksheet code.

Goodness me, thanks VoG!!!

I had no idea it would be that simple and works a charm! Thank you so much.