2 VBA codes in one sheets

[MKDC1303]

Hi,

I'm currently using the following simple code in my worksheet.
It's simply changing the status of a job if another columns status changes.

Private Sub Worksheet_Change(ByVal Target As Range)
  
If Not Intersect(Target, Range("C:C")) Is Nothing Then
   Range("B" & Target.Row).Value = Now
End If

If Not Intersect(Target, Range("J:J")) Is Nothing Then
 If LCase(Target.Value) = "yes" Then
   Range("O" & Target.Row).Value = Now
   Range("G" & Target.Row).Value = "Invoiced"
   Else
      Range("O" & Target.Row).Value = ""
   End If


End If
End Sub

I'd also like to run the following in the same sheet, but where I have ####, this would be a 4 digit code, a quote reference number starting at 1500 upwards, so its never the same.

Private Sub Worksheet_Change(ByVal Target As Range)
  
If Not Intersect(Target, Range("C:C")) Is Nothing Then
   Range("B" & Target.Row).Value = Now
End If

If Not Intersect(Target, Range("I:I")) Is Nothing Then
 If LCase(Target.Value) = "####" Then
   Range("O" & Target.Row).Value = Now
   Range("G" & Target.Row).Value = "Quoted"
   Else
      Range("O" & Target.Row).Value = ""
   End If
   

End If
End Sub

Could I please ask if anyone can offer some advice as to how to run them both in the one sheet.

In case its relevant, the status column (G) can either be updated once a job is invoiced, or if a job has been quoted.. So it might get updated first once its quoted, but then again once the job is invoiced ? Not sure if this is makes a difference to the order of the code, but thought it relevant to advise.

Thanks,
Mike

 

[bobsan42]

just put everything in one event code. try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  
If Not Intersect(Target, Range("C:C")) Is Nothing Then
   Range("B" & Target.Row).Value = Now
End If


If Not Intersect(Target, Range("J:J")) Is Nothing Then
 If LCase(Target.Value) = "yes" Then
   Range("O" & Target.Row).Value = Now
   Range("G" & Target.Row).Value = "Invoiced"
   Else
      Range("O" & Target.Row).Value = ""
   End If
End If

If Not Intersect(Target, Range("I:I")) Is Nothing Then
 If LCase(Target.Value) = "####" Then
   Range("O" & Target.Row).Value = Now
   Range("G" & Target.Row).Value = "Quoted"
   Else
      Range("O" & Target.Row).Value = ""
   End If
End If


End Sub

the first three lines are the same in both codes so you only need them once.

A WoW though: this code will probably work fine if you only change one cell at a time (Target.cells.count=1)
However, if you change more cells at once the changes will be reflected for only one of them.
To handle such a case you will need to loop this procedures for all cells in the target range.

 

[MKDC1303]

just put everything in one event code. try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  
If Not Intersect(Target, Range("C:C")) Is Nothing Then
   Range("B" & Target.Row).Value = Now
End If


If Not Intersect(Target, Range("J:J")) Is Nothing Then
 If LCase(Target.Value) = "yes" Then
   Range("O" & Target.Row).Value = Now
   Range("G" & Target.Row).Value = "Invoiced"
   Else
      Range("O" & Target.Row).Value = ""
   End If
End If

If Not Intersect(Target, Range("I:I")) Is Nothing Then
 If LCase(Target.Value) = "####" Then
   Range("O" & Target.Row).Value = Now
   Range("G" & Target.Row).Value = "Quoted"
   Else
      Range("O" & Target.Row).Value = ""
   End If
End If


End Sub

the first three lines are the same in both codes so you only need them once.

A WoW though: this code will probably work fine if you only change one cell at a time (Target.cells.count=1)
However, if you change more cells at once the changes will be reflected for only one of them.
To handle such a case you will need to loop this procedures for all cells in the target range.

Thankyou,. though what changes to the code do I need to replace the #### with the unique reference number ?

Thanks,
Mike

 

[bobsan42]

well yes.

 

[MKDC1303]

well yes.

????

 

[Yongle]

what changes to the code do I need to replace the #### with the unique reference number ?

Shouldn't the code run regardless of the value in Target cell

 

[MKDC1303]

I figured it out..

I changed this line;

[LEFT][COLOR=#333333][FONT=monospace][I] If LCase(Target.Value) = "####" Then[/I][/FONT][/COLOR][/LEFT]

to

If LCase(Target.Value) >= "1" Then

So any number greater than 1 will trigger the status update

Perhaps there's a better way, but this works

 

[bobsan42]

Well yes
But this is actually up to you - I have no idea what reference numbers you have. reference numbers are not always Numbers.
In addition - I don't have a view of the general picture and what exactly you are trying to achieve.