Why do I get an error for having the username code under the Private Sub ()

[liabilityquek]

Hi

I would like to insert a username code under the existing Private Sub (). But whenever the code is triggered, a debug error surfaces and the workbook closes. Is there a way to fix this issue?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TriggerCell As Range

Set TriggerCell = Range("C7")
   
    If Not Application.Intersect(TriggerCell, Target) Is Nothing Then
            If TriggerCell.Value = "Yes" Then
                Rows("14:16").Hidden = False
                Rows("17:25").Hidden = True
                             
                ElseIf TriggerCell.Value = "Credit Re-assessment" Then
                Rows("14:25").Hidden = False
                             
                ElseIf TriggerCell.Value = "No" Then
                Rows("14:25").Hidden = False
                              
                ElseIf TriggerCell.Value = "No/Credit Re-assessment - Sole Proprietorship (Owned by Company)" Then
                Rows("14:25").Hidden = False
                
                ElseIf TriggerCell.Value = "Please Select" Then
                Rows("14:25").Hidden = False
                
            End If
    End If

Dim User_Name As String

User_Name = Application.UserName
Range("C4") = User_Name

 

[jasonb75]

You're not pausing events while the code runs so it creates an infinite loop. Note that I've added 2 lines, 1 at the top, 1 at the bottom. When your code performs an action that could cause it to run itself these lines are necessary to prevent that from happening.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Dim TriggerCell As Range

Set TriggerCell = Range("C7")
  
    If Not Application.Intersect(TriggerCell, Target) Is Nothing Then
            If TriggerCell.Value = "Yes" Then
                Rows("14:16").Hidden = False
                Rows("17:25").Hidden = True
                            
                ElseIf TriggerCell.Value = "Credit Re-assessment" Then
                Rows("14:25").Hidden = False
                            
                ElseIf TriggerCell.Value = "No" Then
                Rows("14:25").Hidden = False
                              
                ElseIf TriggerCell.Value = "No/Credit Re-assessment - Sole Proprietorship (Owned by Company)" Then
                Rows("14:25").Hidden = False
                
                ElseIf TriggerCell.Value = "Please Select" Then
                Rows("14:25").Hidden = False
                
            End If
    End If

Dim User_Name As String

User_Name = Application.UserName
Range("C4") = User_Name
Application.EnableEvents = True
End Sub

 

[liabilityquek]

You're not pausing events while the code runs so it creates an infinite loop. Note that I've added 2 lines, 1 at the top, 1 at the bottom. When your code performs an action that could cause it to run itself these lines are necessary to prevent that from happening.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Dim TriggerCell As Range

Set TriggerCell = Range("C7")
 
    If Not Application.Intersect(TriggerCell, Target) Is Nothing Then
            If TriggerCell.Value = "Yes" Then
                Rows("14:16").Hidden = False
                Rows("17:25").Hidden = True
                           
                ElseIf TriggerCell.Value = "Credit Re-assessment" Then
                Rows("14:25").Hidden = False
                           
                ElseIf TriggerCell.Value = "No" Then
                Rows("14:25").Hidden = False
                             
                ElseIf TriggerCell.Value = "No/Credit Re-assessment - Sole Proprietorship (Owned by Company)" Then
                Rows("14:25").Hidden = False
               
                ElseIf TriggerCell.Value = "Please Select" Then
                Rows("14:25").Hidden = False
               
            End If
    End If

Dim User_Name As String

User_Name = Application.UserName
Range("C4") = User_Name
Application.EnableEvents = True
End Sub

Thanks for the assist Jasonb!