Results 1 to 6 of 6

VBA Code is stopped working?

Threaded View

  1. 03-28-2016, 05:30 PM #1
    stoicy
    stoicy is offline
    Registered User
    Join Date
    03-13-2016
    Location
    Yemen
    MS-Off Ver
    2016
    Posts
    59

    VBA Code is stopped working?

    Hello All

    Many thanks for all people standing here for help and support

    personally I learned allot form this forum in short time and I still

    In my excel sheet I have below code:

    Private Sub Worksheet_Change(ByVal Target As Range)
   
If Intersect(Target, Range("$N$2:$N$100000")) Is Nothing Then Exit Sub

note = Target.Value
    
Select Case note
    Case "Working without authorisation"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Procedure not followed or adequate"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Fail to warn /inform"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Fail to secure / shutdown"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Using inappropriate parameters / speed"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Using equipment beyond safe limits"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Improper cargo fastening / lifting"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Exposing to unknown hazards"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Defective / Lack of equipment"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UC"
    Case "Using defective equipment"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UA"
    Case "Improper use of equipment"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UA"
    Case "Servicing running equipment"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UA"
    Case "Unidentified hazardous substances"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UC"
    Case "Lack of isolation system"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UC"
    Case "Without certification / marking"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UC"
    Case "Without / improper use of PPE"
        score_comment1 = "PPE"
        score_comment2 = "UA"
    Case "Defective / improper PPE"
        score_comment1 = "PPE"
        score_comment2 = "UC"
    Case "Bad housekeeping"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Poor lighting"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Poor ventilation"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Congested / obstructed area"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Bad ergonomy"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Bad visibility"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Hazardous substance exposure"
        score_comment1 = "Workplace Environment"
        score_comment2 = " UC"
    Case "Excessive noise"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Severe weather"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Disabling or removing safety device"
        score_comment1 = "Safety System"
        score_comment2 = "UA"
    Case "Lack of warning / marking system"
        score_comment1 = "Safety System"
        score_comment2 = "UC"
    Case "Inadequate safety guard, barrier etc"
        score_comment1 = "Safety System"
        score_comment2 = "UC"
    Case "Inadequate communication system"
        score_comment1 = "Safety System"
        score_comment2 = "UC"
    Case "Rotating equipment without protection"
        score_comment1 = "Safety System"
        score_comment2 = "UC"
    Case "Deficient process control system"
        score_comment1 = "Safety System"
        score_comment2 = "UC"
    Case "Adopting unsafe work position or posture"
        score_comment1 = "Behavior"
        score_comment2 = "UA"
    Case "Failling to check equipment before use"
        score_comment1 = "Behavior"
        score_comment2 = "UA"
    Case "Under drugs or alcohol influence"
        score_comment1 = "Behavior"
        score_comment2 = "UA"
    Case "Horseplay"
        score_comment1 = "Behavior"
        score_comment2 = "UA"
End Select

'value and result location
Target.Offset(0, 7) = score_comment1
Target.Offset(0, 8) = score_comment2

End Sub

    And it is working great but when I add another code it stopped working I don't know what is the problem?

    Private Sub Worksheet_Change(ByVal Target As Range)
'today date if cell is not blank
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range
Dim MonthList()
   MonthList = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec")
    Set rInt = Intersect(Target, Range("H:H"))
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            Set tCell = rCell.Offset(0, -3)
            If IsEmpty(tCell) Then
                tCell = Date
                tCell.NumberFormat = "dd.mm.yyyy"
            End If
        Next
    End If
'divide date to year,month,days

    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub
    If Target <> "" And IsDate(Target) Then
        Target.Offset(, -3) = Day(Target)
        Target.Offset(, -3).NumberFormat = "00"
        
        Target.Offset(, -2) = Application.WorksheetFunction.Index(MonthList, Month(Target))
        
        Target.Offset(, -1) = Year(Target)
        Target.Offset(, -1).NumberFormat = "0000"
    Else
        Target.Offset(, -3).Resize(, 3) = ""
    
    End If
    

If Intersect(Target, Range("$N$2:$N$100000")) Is Nothing Then Exit Sub

note = Target.Value
    
Select Case note
    Case "Working without authorisation"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Procedure not followed or adequate"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Fail to warn /inform"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Fail to secure / shutdown"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Using inappropriate parameters / speed"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Using equipment beyond safe limits"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Improper cargo fastening / lifting"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Exposing to unknown hazards"
        score_comment1 = "Procedure"
        score_comment2 = "UA"
    Case "Defective / Lack of equipment"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UC"
    Case "Using defective equipment"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UA"
    Case "Improper use of equipment"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UA"
    Case "Servicing running equipment"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UA"
    Case "Unidentified hazardous substances"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UC"
    Case "Lack of isolation system"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UC"
    Case "Without certification / marking"
        score_comment1 = "Tool & Equipment"
        score_comment2 = "UC"
    Case "Without / improper use of PPE"
        score_comment1 = "PPE"
        score_comment2 = "UA"
    Case "Defective / improper PPE"
        score_comment1 = "PPE"
        score_comment2 = "UC"
    Case "Bad housekeeping"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Poor lighting"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Poor ventilation"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Congested / obstructed area"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Bad ergonomy"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Bad visibility"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Hazardous substance exposure"
        score_comment1 = "Workplace Environment"
        score_comment2 = " UC"
    Case "Excessive noise"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Severe weather"
        score_comment1 = "Workplace Environment"
        score_comment2 = "UC"
    Case "Disabling or removing safety device"
        score_comment1 = "Safety System"
        score_comment2 = "UA"
    Case "Lack of warning / marking system"
        score_comment1 = "Safety System"
        score_comment2 = "UC"
    Case "Inadequate safety guard, barrier etc"
        score_comment1 = "Safety System"
        score_comment2 = "UC"
    Case "Inadequate communication system"
        score_comment1 = "Safety System"
        score_comment2 = "UC"
    Case "Rotating equipment without protection"
        score_comment1 = "Safety System"
        score_comment2 = "UC"
    Case "Deficient process control system"
        score_comment1 = "Safety System"
        score_comment2 = "UC"
    Case "Adopting unsafe work position or posture"
        score_comment1 = "Behavior"
        score_comment2 = "UA"
    Case "Failling to check equipment before use"
        score_comment1 = "Behavior"
        score_comment2 = "UA"
    Case "Under drugs or alcohol influence"
        score_comment1 = "Behavior"
        score_comment2 = "UA"
    Case "Horseplay"
        score_comment1 = "Behavior"
        score_comment2 = "UA"
End Select

'value and result location
Target.Offset(0, 7) = score_comment1
Target.Offset(0, 8) = score_comment2

End Sub
    can any body help

    Thanks
    Last edited by stoicy; 03-28-2016 at 05:50 PM.
    Reply With QuoteRegister To Reply
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Combo Box Code Stopped Working
    By ker9 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 05-07-2014, 01:20 PM
  2. [SOLVED] Why has my code stopped working?!
    By JamesFletcher in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-21-2013, 07:07 AM
  3. Code suddenly stopped working
    By bibleguy125 in forum Excel General
    Replies: 1
    Last Post: 02-15-2012, 01:00 PM
  4. Excel code stopped working
    By Jollyfrog in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-28-2010, 07:34 AM
  5. Excel 2007 : Macro Code Stopped Working
    By stevo_300 in forum Excel General
    Replies: 5
    Last Post: 04-06-2010, 07:57 AM
  6. Code stopped working
    By kinleyr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2010, 12:25 PM
  7. Code stopped working
    By chris46521 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2006, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1