Code not functioning properly

Hi guys,

I have attached a sample wb. User John password test. I seem to have identical codes in sheet 4 & 5 bar a couple of different cell refrences and the code seems to be working fine in sheet 4 & not in sheet 5. The latest code I have introduced was to autocapitalise names so for example if I input jOE blOGgs it would automatically go to Joe Bloggs. This works perfect in sheet 4 but not in sheet 5. I am very new to VBA and don't know it very well so would love some feedback if there is an error or something in it. I do get help from you guys then when I insert it into existing code that's where I sometimes make the mistake.

The capitalising code doesn't seem to work for sheet 5. Also in sheet 5 it seems to be acting funny. You can click on a cell and alter it but it doesn't indicate that it is selected. Usually when you select a cell it is highlighted on all four sides. You cannot see the cell you selected and then the autocapitalisations don't work either

Hey,

Thanks for the reply. if I understand you right then I have changed the date column so its not the same as the other one. So when anything is typed in D the date populates in A. But the autocapitalising is in column E instead See below. However this is causing a weird bug now that when I type in E in sheet 4 the sheet freezes and then does something funny with the calculations at the top of the page.

Option Explicit
Option Compare Text
Dim rw As Long
Dim thisrow As Long
Dim c As Excel.Range
Dim str              As String
Dim v                As Variant
Dim iStart           As Integer
Dim iEnd             As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
Const sPW As String = "$P$2"
Const sHide As String = "I:I, O:O"
If Not Intersect(Target, Range(sPW)) Is Nothing Then
    If Target.Value = 1234 Then
        ActiveSheet.Unprotect
        'Range(sHide & 1).EntireColumn.Hidden = False
        Range(sHide).EntireColumn.Hidden = False
        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    ElseIf Target.Value = "" Then
        ActiveSheet.Unprotect
        'Range(sHide & 1).EntireColumn.Hidden = True
        Range(sHide).EntireColumn.Hidden = True
        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    End If
    End If
   With Target
    Select Case True
 Case .Column = 4
            Range("A" & Target.Row) = Date

         Case Not Intersect(Target, Range("E4:E500")) Is Nothing

            For Each c In Intersect(Target, Range("E4:E500"))

               ' don't process - just remove the .
               If Left(c.Value, 1) = "~" Then
                  str = Mid(c.Value, 2)
               Else
                  str = StrConv(c.Value, vbProperCase)

                  ' O'Leary, D'Alton,A'Courcey, N'Dou, De'Ath (really!)

                  If InStr(str, "o'") > 0 Or _
                     InStr(str, "d'") > 0 Or _
                     InStr(str, "a'") > 0 Or _
                     InStr(str, "n'") > 0 Or _
                     InStr(str, "de'") > 0 Then

                     iStart = InStr(str, "'") - 1
                     str = Left(str, iStart) & "'" & StrConv(Mid(str, iStart + 2), vbProperCase)
                  End If

                  ' von Adler, van Dieman
                  If InStr(str, " von ") > 0 Or InStr(str, " van ") > 0 Then
                     iStart = InStr(str, " v") - 1
                     str = Left(str, iStart) & StrConv(Mid(str, iStart + 1, 4), vbLowerCase) & Mid(str, iStart + 5)
                  End If

                  ' von der Recke - but the von has already been handled. 1 of the reasons this is not a 'Select Case' block
                  If InStr(str, " der ") > 0 Then
                     iStart = InStr(str, " der ") - 1
                     str = Left(str, iStart) & StrConv(Mid(str, iStart + 1, 4), vbLowerCase) & Mid(str, iStart + 5)
                  End If

                  ' Hyphenated
                  If InStr(str, "-") > 0 Then

                     ' Ignore if spaced already
                     If Mid(str, iStart + 2) <> " " Then
                        iStart = InStr(str, "-") - 1
                        str = Left(str, iStart) & "-" & StrConv(Mid(str, iStart + 2), vbProperCase)
                     End If
                  End If

                  ' Let's just include a Mc
                  If InStr(str, " mc") > 0 Then
                     iStart = InStr(str, " mc") + 2
                     str = Left(str, iStart) & StrConv(Mid(str, iStart + 1), vbProperCase)
                  End If

                  ' Never mind the de la, della and about 20 others but getting vanishingly small in numbers
               End If
               c.Value = str
            Next

      End Select

   End With

Catch:

   Application.EnableEvents = True

End Sub

    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.Unprotect
        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    ActiveSheet.EnableSelection = xlUnlockedCells

End Sub

Hi,

Ok I have it not "acting funny" now. By that I meant it just froze for a sec and then it seemed to recalculate figures on the top to ridiculous figures then recalculated them back but this has stopped. Are you experiencing the same issue as me on sheet 5 for some reason. Unable to highlight a cell. you can select it and alter it but looking at the spreadsheet you have no idea where the curser is. It's only happening on this sheet?

Thanks @alphafrog. Yeah it's strange how it's only happening on that sheet? Hey @cytop I haven't a clue to be honest so any guidance greatly appreciated. I got the extra bits of code tried to insert them but way out of my comfort zone. It's very possible that there is a few things that I have copied off other sheets. For example that before workbook close. Basically I wanted every sheet to protect before close. So I stuck that at the end of every sheet. Going by your reaction above that should probably be just on the "this workbook" code? I just wanted to make sure if any sheet was accidentally left unprotected then on save or close it would reprotect. Maybe if you could point out specifically some recommended changes to my codes i can replicate that across all sheets and hopefully that would help the bug in sheet 5 also.

should probably be just on the "this workbook" code

Yes - you'll find any number of examples with the simplest of searches.

Going back to your procedure. It seems you use the horrible ProperName function in more than one sheet. It should be moved to a module.

Option Explicit
Option Compare Text   ' THIS IS REQUIRED

Public Function ProperName(ByVal str As Variant) As Variant

   Dim vResult As Variant
   Dim iStart As Integer
   
   '// Don't process anything starting with a ~
   '// Just so the user will be able to enter anything not catered
   '// for - Example a 'Fitz with the uppcer letter in the 5th position
   '// 'FitzSimons'
   '// Inpout with a ~ to start and it will be removed and  the remaining
   '// characters passed bask without change
   If Left(str, 1) = "~" Then
      vResult = Mid(str, 2)
   ElseIf IsNumeric(str) Then
      vResult = str
   Else
      vResult = StrConv(str, vbProperCase)

      ' O'Leary, D'Alton,A'Courcey, N'Dou, De'Ath (really!)

      If InStr(vResult, " o'") > 0 Or _
         InStr(vResult, " d'") > 0 Or _
         InStr(vResult, " a'") > 0 Or _
         InStr(vResult, " n'") > 0 Or _
         InStr(vResult, " de'") > 0 Then

         iStart = InStr(vResult, "'") - 1
         
         vResult = Left(vResult, iStart) & "'" & StrConv(Mid(vResult, iStart + 2), vbProperCase)
         
      End If

      ' von Adler, van Dieman
      If InStr(vResult, " von ") > 0 Or InStr(vResult, " van ") > 0 Then
         iStart = InStr(vResult, " v") - 1
         vResult = Left(vResult, iStart) & StrConv(Mid(vResult, iStart + 1, 4), vbLowerCase) & Mid(vResult, iStart + 5)
      End If

      ' von der Recke - but the von has already been handled. 1 of the reasons this is not a 'Select Case' block
      If InStr(vResult, " der ") > 0 Then
         iStart = InStr(vResult, " der ") - 1
         vResult = Left(vResult, iStart) & StrConv(Mid(vResult, iStart + 1, 4), vbLowerCase) & Mid(vResult, iStart + 5)
      End If

      ' Hyphenated
      If InStr(vResult, "-") > 0 Then

         ' Ignore if spaced already
         If Mid(vResult, iStart + 2) <> " " Then
            iStart = InStr(vResult, "-") - 1
            vResult = Left(vResult, iStart) & "-" & StrConv(Mid(vResult, iStart + 2), vbProperCase)
         End If
      End If

      ' Let's just include a Mc
      If InStr(vResult, " mc") > 0 Then
         iStart = InStr(vResult, " mc") + 2
         vResult = Left(vResult, iStart) & StrConv(Mid(vResult, iStart + 1), vbProperCase)
      End If

      ' Never mind the de la, della and about 20 others but getting vanishingly small in numbers
   End If
   
   ProperName = vResult

End Function

Your worksheet code then changes to

Private Sub Worksheet_Change(ByVal Target As Range)

   Const sPW            As String = "$P$2"
   Const sHide          As String = "I:I, O:O"
   
   '// Store the current setting for EnableEvents
   '// And turn off
   Dim EventsEnabled As Boolean

   EventsEnabled = Application.EnableEvents
   Application.EnableEvents = False

   '// Always and ever, more especially if working with Events turned off
   On Error GoTo Catch

   If Not Intersect(Target, Range(sPW)) Is Nothing Then

      ActiveSheet.Unprotect

      'Range(sHide & 1).EntireColumn.Hidden = False
      Range(sHide).EntireColumn.Hidden = (Target.Value <> 1234)
      ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                          False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                          AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
                          AllowUsingPivotTables:=True
      ActiveSheet.EnableSelection = xlUnlockedCells
   End If

   With Target
      Select Case True
         Case .Column = 4
            Range("A" & Target.Row) = Date

         Case Not Intersect(Target, Range("E4:E500")) Is Nothing

            For Each c In Intersect(Target, Range("E4:E500"))
               c.Value = ProperName(c.Value)
            Next

      End Select

   End With

Catch:

   Application.EnableEvents = True
   Exit Sub
   
   '// Debugging only - code will never get to here
   Resume

End Sub

Ok I've set up a new module with the first code you gave me. Then on all sheets I amended the code to the second part from "with select" onwards and I have deleted the before close on all sheets. I am getting an error on the individual sheets saying "select case without end select" with the final END SUB highlighted. Also at the start of each sheet I have

Option Explicit
Option Compare Text
Dim rw As Long
Dim thisrow As Long
Dim c As Excel.Range
Dim str              As String
Dim v                As Variant
Dim iStart           As Integer
Dim iEnd             As Integer

I got a lot of it from the "horrible" "proper" code. Now I have moved it to a module do I need most of this stuff.

I am getting an error on the individual sheets saying "select case without end select"

The code I posted was tested, and works in so far as I understand what it is supposed to do - so the issue must be in the changes you made. As you haven't posted the code, there's nothing I can suggest other than the obvious - you have mismatched control structures.

I didn't take the time to clean up redundant variables but as you have an 'Option Explicit' statement in the module you could simply comment them all out and try compiling the project. If it doesn't object then that's fine. If it does object, you can simply uncomment whichever one it complains about and try compiling again.

Thank you for your patience.

This is the module code

Option Explicit
Option Compare Text   ' THIS IS REQUIRED

Public Function ProperName(ByVal str As Variant) As Variant

   Dim vResult As Variant
   Dim iStart As Integer
   
   '// Don't process anything starting with a ~
   '// Just so the user will be able to enter anything not catered
   '// for - Example a 'Fitz with the uppcer letter in the 5th position
   '// 'FitzSimons'
   '// Inpout with a ~ to start and it will be removed and  the remaining
   '// characters passed bask without change
   If Left(str, 1) = "~" Then
      vResult = Mid(str, 2)
   ElseIf IsNumeric(str) Then
      vResult = str
   Else
      vResult = StrConv(str, vbProperCase)

      ' O'Leary, D'Alton,A'Courcey, N'Dou, De'Ath (really!)

      If InStr(vResult, " o'") > 0 Or _
         InStr(vResult, " d'") > 0 Or _
         InStr(vResult, " a'") > 0 Or _
         InStr(vResult, " n'") > 0 Or _
         InStr(vResult, " de'") > 0 Then

         iStart = InStr(vResult, "'") - 1
         
         vResult = Left(vResult, iStart) & "'" & StrConv(Mid(vResult, iStart + 2), vbProperCase)
         
      End If

      ' von Adler, van Dieman
      If InStr(vResult, " von ") > 0 Or InStr(vResult, " van ") > 0 Then
         iStart = InStr(vResult, " v") - 1
         vResult = Left(vResult, iStart) & StrConv(Mid(vResult, iStart + 1, 4), vbLowerCase) & Mid(vResult, iStart + 5)
      End If

      ' von der Recke - but the von has already been handled. 1 of the reasons this is not a 'Select Case' block
      If InStr(vResult, " der ") > 0 Then
         iStart = InStr(vResult, " der ") - 1
         vResult = Left(vResult, iStart) & StrConv(Mid(vResult, iStart + 1, 4), vbLowerCase) & Mid(vResult, iStart + 5)
      End If

      ' Hyphenated
      If InStr(vResult, "-") > 0 Then

         ' Ignore if spaced already
         If Mid(vResult, iStart + 2) <> " " Then
            iStart = InStr(vResult, "-") - 1
            vResult = Left(vResult, iStart) & "-" & StrConv(Mid(vResult, iStart + 2), vbProperCase)
         End If
      End If

      ' Let's just include a Mc
      If InStr(vResult, " mc") > 0 Then
         iStart = InStr(vResult, " mc") + 2
         vResult = Left(vResult, iStart) & StrConv(Mid(vResult, iStart + 1), vbProperCase)
      End If

      ' Never mind the de la, della and about 20 others but getting vanishingly small in numbers
   End If
   
   ProperName = vResult

End Function

And this is the worksheet code

'Option Explicit
'Option Compare Text
'Dim rw As Long
'Dim thisrow As Long
'Dim c As Excel.Range
'Dim str              As String
'Dim v                As Variant
'Dim iStart           As Integer
'Dim iEnd             As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

   Const sPW            As String = "$P$2"
   Const sHide          As String = "I:I, O:O"
   
   '// Store the current setting for EnableEvents
   '// And turn off
   Dim EventsEnabled As Boolean

   EventsEnabled = Application.EnableEvents
   Application.EnableEvents = False

   '// Always and ever, more especially if working with Events turned off
   On Error GoTo Catch

   If Not Intersect(Target, Range(sPW)) Is Nothing Then

      ActiveSheet.Unprotect

      'Range(sHide & 1).EntireColumn.Hidden = False
      Range(sHide).EntireColumn.Hidden = (Target.Value <> 1234)
      ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
                          False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                          AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
                          AllowUsingPivotTables:=True
      ActiveSheet.EnableSelection = xlUnlockedCells
   End If

   With Target
      Select Case True
         Case .Column = 4
            Range("A" & Target.Row) = Date

         Case Not Intersect(Target, Range("E4:E500")) Is Nothing

            For Each c In Intersect(Target, Range("E4:E500"))
               c.Value = ProperName(c.Value)
            Next

      End Select

   End With

Catch:

   Application.EnableEvents = True
   Exit Sub
   
   '// Debugging only - code will never get to here
   Resume

End Sub

That seems to be the code I posted earlier and I know that works so am unsure where you're getting the error. I could be wrong as I'm using a phone at the moment but I honestly cannot see any difference.

Also, you should not comment out the 2 statements

Option Compare Text
Option Explicit

These are not variables and you should consider them mandatory in every code module/worksheet module, userform and Class module.

You also need to uncomment the line

Dim c as Excel.Range

But, more properly, it should be declared in the Change event as it is only used there - not as a module level variable.

Hey,

I have made the amendments you have suggested. I have included the 3 Dims and commented out the rest. Still showing the same error

Option Explicit
Option Compare Text
'Dim thisrow As Long
'Dim str              As String
'Dim v                As Variant
'Dim iStart           As Integer
'Dim iEnd             As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Excel.Range
Dim rw As Long 
  Application.EnableEvents = False
   On Error GoTo Catch

   With Target

      Select Case True
         Case .Column = 8 And UCase(.Value) = "PROCEEDING TO AIP"
            rw = Sheets("Mort Figs").Range("B" & Rows.Count).End(xlUp).Row + 1
            Range("E" & .Row).Copy Destination:=Sheets("Mort Figs").Range("b" & rw)
            Range("g" & .Row).Copy Destination:=Sheets("Mort Figs").Range("e" & rw)
            Range("d" & .Row).Copy Destination:=Sheets("Mort Figs").Range("d" & rw)


         With Target
      Select Case True
         Case .Column = 4
            Range("A" & Target.Row) = Date

         Case Not Intersect(Target, Range("E4:E500")) Is Nothing

            For Each c In Intersect(Target, Range("E4:E500"))
               c.Value = ProperName(c.Value)
            Next

      End Select

   End With

Catch:

   Application.EnableEvents = True
   Exit Sub
   
   '// Debugging only - code will never get to here
   Resume

End Sub

I have attached as real an example of my situation as I can. It includes all the sheets that require your code as well as the module. Hope you can make some sense on my mess. User John password test

If you indent your code correctly, the issue will be fairly obvious:

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim c                As Excel.Range
   Dim rw               As Long

   Application.EnableEvents = False
   On Error GoTo Catch

   With Target

      Select Case True
         Case .Column = 8 And UCase(.Value) = "PROCEEDING TO AIP"
            rw = Sheets("Mort Figs").Range("B" & Rows.Count).End(xlUp).Row + 1
            Range("E" & .Row).Copy Destination:=Sheets("Mort Figs").Range("b" & rw)
            Range("g" & .Row).Copy Destination:=Sheets("Mort Figs").Range("e" & rw)
            Range("d" & .Row).Copy Destination:=Sheets("Mort Figs").Range("d" & rw)


            With Target
               Select Case True
                  Case .Column = 4
                     Range("A" & Target.Row) = Date

                  Case Not Intersect(Target, Range("E4:E500")) Is Nothing

                     For Each c In Intersect(Target, Range("E4:E500"))
                        c.Value = ProperName(c.Value)
                     Next

               End Select

            End With

Catch:

            Application.EnableEvents = True
            Exit Sub

            '// Debugging only - code will never get to here
            Resume

         End Sub

The 'End Sub' should finish up in column 1 of the editor, so something is mismatched. or left hanging open - in this case, 2 control structures (If, Do, Select Case, Case) do not have a corresponding End If, Loop, End Case (The Case statement does not have a closing statement - it is followed by another Case statement or End Select).

You mentioned earlier you are new to VBA, so treat this as a learning exercise - read the code slowly following the logic of what it is doing, considering if it is appropriate at that point.

You should also be aware that a Select Case statement will only ever execute the first Case that is True, the others will be skipped. You test if the changed column is 8 and then move some information. Problem is, it is only when that Case is True that the current code will then enter the Select Case for Cols 4 & 5. As the column can only be 8 for that code to execute those conditions will never be True. Confused yet?

To help you indent code there's a link to a free utility in another recent thread

I get that the error I made was I copied the bit in red ahich was repeating so therefore wasn't working right. I have deleted these 2 lines on each sheet and they do appear to be working properly.

The date code however does not seem to be working correctly. If I type in the specific column the date appears grand. However if a code puts text in the column rather than me putting it in manually then the date does not populate.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim c                As Excel.Range
   Dim rw               As Long

   Application.EnableEvents = False
   On Error GoTo Catch

   With Target

      Select Case True
         Case .Column = 8 And UCase(.Value) = "PROCEEDING TO AIP"
            rw = Sheets("Mort Figs").Range("B" & Rows.Count).End(xlUp).Row + 1
            Range("E" & .Row).Copy Destination:=Sheets("Mort Figs").Range("b" & rw)
            Range("g" & .Row).Copy Destination:=Sheets("Mort Figs").Range("e" & rw)
            Range("d" & .Row).Copy Destination:=Sheets("Mort Figs").Range("d" & rw)


            With Target
               Select Case True               

   Case .Column = 4
                     Range("A" & Target.Row) = Date

                  Case Not Intersect(Target, Range("E4:E500")) Is Nothing

                     For Each c In Intersect(Target, Range("E4:E500"))
                        c.Value = ProperName(c.Value)
                     Next

               End Select

            End With

Catch:

            Application.EnableEvents = True
            Exit Sub

            '// Debugging only - code will never get to here
            Resume

         End Sub

and they do appear to be working properly

Looking at the code you posted, I can't see how. You still have unmatched control structures. Yes, I know I could simply post the the correction but this is too fundamental. You have to understand both the error and the logic.

You need to indent this correctly in order to see the issue.

Once I'm not annoying you I'm happy to learn. I don't use VBA in my day to day. I just use excel as a hobby and to manage my company finances and activity. I thought I was great with my sum(), If's, even lookups. I asked how to do something one day and I got a VBA code and it has completely opened Excel to me.

I have a Private sub & End Sub
I have a with & end With
I have a select case & end select
I have CASE but no END Case, but going by your earlier comments Case doesn't have end just a end select so do I put that in once to end the case and again to end the select?

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim c                As Excel.Range
   Dim rw               As Long

   Application.EnableEvents = False
   On Error GoTo Catch

   With Target

      Select Case True
         Case .Column = 8 And UCase(.Value) = "PROCEEDING TO AIP"
            rw = Sheets("Mort Figs").Range("B" & Rows.Count).End(xlUp).Row + 1
            Range("E" & .Row).Copy Destination:=Sheets("Mort Figs").Range("b" & rw)
            Range("g" & .Row).Copy Destination:=Sheets("Mort Figs").Range("e" & rw)
            Range("d" & .Row).Copy Destination:=Sheets("Mort Figs").Range("d" & rw)

   Case .Column = 4
                     Range("A" & Target.Row) = Date

                  Case Not Intersect(Target, Range("E4:E500")) Is Nothing

end Select

                     For Each c In Intersect(Target, Range("E4:E500"))
                        c.Value = ProperName(c.Value)
                     Next
               End Select

            End With

Catch:

            Application.EnableEvents = True
            Exit Sub

            '// Debugging only - code will never get to here
            Resume

         End Sub

Hey @cytop,

I'm a bit confused. I've taken your new code above. I used to have all if's on my code and the guy who wrote the proper name code in the worksheet changed everything to case. I have changed all the sheets back to if's now which does make it a little easier to understand.

Having done this all seems well. The date is populating when text is manually typed in the cell or drop down. However the date does not go in the cell if the data is transferred to the trigger cell automatically. Also this code is not working correctly. The bit i red is flagging as a problem. I have tried to indent all the codes as you have suggested but I cannot see why it is no working. The very same code in yellow is working ok so I'm guessing its something wrong with the target sheet 4 but the code seems identical to sheet 5 where it is working. User john password test. Sorry I know I'm probably wrecking your head.

Dim rw As Long, CopyTo As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("AB:AC")) Is Nothing Then
  With Target
    If UCase(.Value) = "BLOCK" Then
      If .Column = 28 Then
        CopyTo = "Banc ***"
        rw = Sheets(CopyTo).Range("E" & Rows.Count).End(xlUp).Row + 1
        Range("B" & .Row).Copy Destination:=Sheets(CopyTo).Range("E" & rw)
        Range("AB" & .Row).Copy Destination:=Sheets(CopyTo).Range("f" & rw)
        Sheets(CopyTo).Range("J" & rw).Value = "Sent Up"  'Put "Sent Up" in Column J
    ElseIf .Column = 29 Then
            CopyTo = "Home Ins"
        rw = Sheets(CopyTo).Range("D" & Rows.Count).End(xlUp).Row + 1
        Range("B" & .Row & ":C" & .Row).Copy Destination:=Sheets(CopyTo).Range("D" & rw)
        Range("AC" & .Row).Copy Destination:=Sheets(CopyTo).Range("F" & rw)
        Range("D" & .Row).Copy Destination:=Sheets(CopyTo).Range("g" & rw)
        Sheets(CopyTo).Range("J" & rw).Value = "Sent Up" 'Put "Sent Up" in Column K
        
      End If
    End If
  End With
End If
Const sPW As String = "$AJ$1"
Const sHide As String = "Aa:Aa, AG:AG, Ak:Ak, Ap:Ap, AQ:AQ, Av:Av, Aw:Aw, Bb:Bb, Bc:Bc, Bh:Bh, Bi:Bi, Bn:Bn, Bo:Bo, Bt:Bt, Bu:Bu, Bz:Bz, Ca:Ca "
If Not Intersect(Target, Range(sPW)) Is Nothing Then
    If Target.Value = 1234 Then
        ActiveSheet.Unprotect
        'Range(sHide & 1).EntireColumn.Hidden = False
        Range(sHide).EntireColumn.Hidden = False
        ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    ElseIf Target.Value = "" Then
        ActiveSheet.Unprotect
        'Range(sHide & 1).EntireColumn.Hidden = True
        Range(sHide).EntireColumn.Hidden = True
        ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    End If
    End If

If Target.Column = 24 Then
    ActiveSheet.Unprotect
    If Range("AB" & Target.Row) = "Block" Then
        lr = Sheets("Banc ***").Range("B" & Rows.Count).End(xlUp).Row
        test = Sheets("Mort Figs").Range("B" & Target.Row).Value & Sheets("Mort Figs").Range("AB" & Target.Row).Value
        For x = 5 To lr
            test2 = Sheets("Banc ***").Range("E" & x).Value & Sheets("Banc ***").Range("F" & x).Value
            If test2 = test Then
                my_row = x
                Exit For
            End If
        Next
        Sheets("Banc ***").Range("J" & my_row) = "Issued"
        Sheets("Banc ***").Range("K" & my_row) = Target
        
    End If
    If Range("AC" & Target.Row) = "Block" Then
        lr = Sheets("Home Ins").Range("B" & Rows.Count).End(xlUp).Row
        test = Sheets("Mort Figs").Range("B" & Target.Row).Value & Sheets("Mort Figs").Range("AC" & Target.Row).Value
        For x = 5 To lr
            test2 = Sheets("Home Ins").Range("D" & x).Value & Sheets("Home Ins").Range("F" & x).Value
            If test2 = test Then
                my_row = x
                Exit For
            End If
        Next
        Sheets("Home Ins").Range("j" & my_row) = "Issued"
        Sheets("Home Ins").Range("k" & my_row) = Target
        
    
        
    End If

   ActiveSheet.Unprotect
        ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
End If
End Sub

The Case Statements were just as easy to understand if you had indented the code correctly - I won't stop emphasizing how critical to understanding code that can be, especially when you start to get nested control structures. And you should go and re-read your other thread and note who "the guy who wrote the proper name code" is.

On that tack, I have to ask why do you use 2 IDs...? Am curious.

if the data is transferred to the trigger cell automatically

You don't explain how the data is transferred 'automatically' ... remember no one except you understands the processes, procedures and data flow of your workbook. You need to be very precise when asking about errors - you need to detail the steps leading up to the error.

In the sample workbook you posted, the error highlighted Red is raised because a referenced cell has a #REF error - I can't comment any further as I have no idea what should be there.

For these additional errors I think you would be better off starting new threads, 1 for each problem and keep each thread focused on just the one problem.

FYI that error did not happen on the full version of my workbook. Thanks again.