Need clarification on Code "Private Sub Worksheet_Change(ByVal Target As Range)".

[pedie]

how many Private Sub Worksheet_Change(ByVal Target As Range) code can we have in a sheet? Can i have 2? Is that possible?

Suppose I want the target to be B1 and then other range is D2:D7...

D2:D7...has diffferent code and B1 has different code again so confused..

Thanks for helping!

 

[Nogslaw]

Use If Statements to form your conditions

Private Sub Worksheet_Change(ByVal Target As Range)
'FOR A SINGLE CELL
If Target.Address = "$B$1" Then
    MsgBox "B1 Code Here"
End If

'FOR A RANGE OF CELLS
If Not Intersect(Target, Range("D2:D7")) Is Nothing Then
    MsgBox "D2:D7 Code Here"
End if

End Sub

 

[HalfAce]

Hi Pedie,
The direct answer to your question is 'Nope. We can only have one Worksheet_Change routine per worksheet.'

The good news is that Nogslaw has shown one of the ways to incorporate both conditions into a single routine, so... well, there it is.

Have a good one.

 

[pedie]

Thank you so much guys!
Dan, sorry bout yesterdays thread...was caught at work hence couldnt reply back...Thanks for the concern.

And here is one more code...error is happening in blue line...

Sub Macro7()
'
' Macro7 Macro
'
'
    Sheets("Transport").Select
    Range("E12:F17").Select
    With Selection.Validation
        .Delete
    End With
    Range("E12:F17").Select
   [COLOR=blue]Selection.NumberFormat = "h:mm;@"[/COLOR]
    Range("A11").Select
End Sub

Runtime error 1004<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Unable to set numberformat property of the range class<o></o>

 

[MrKowz]

Just to provide another possible example for using the Worksheet_Change event for multiple ranges with different results.

Private Sub Worksheet_Change(ByVal target As Range)
    Application.EnableEvents = False
        If Not Intersect(target, Range("C4")) Is Nothing Then
            Call CheckBoxReset
            Call AverageFive
            Call PROIMain
            Call BorderPROI
            Sheets("Client Information").Select
        End If
        If Not Intersect(target, Range("C8")) Is Nothing Then
            Call CheckBoxReset
            Call AverageFive
        End If
        If Not Intersect(target, Range("C5, C8, C10")) Is Nothing Then
            Call ParticipationDate
        End If
    Application.EnableEvents = True
End Sub

As far as your other macro... it shouldn't be erroring on that line... it works fine on my end. Is that range and sheet protected? And here is your code cleaned up a bit

Sub Macro7()
'
' Macro7 Macro
'
'
    With Sheets("Transport")
      .Range("E12:F17").Validation.Delete
      .Range("E12:F17").NumberFormat = "h:mm;@"
      .Range("A11").Select
    End With
End Sub

 

[HalfAce]

Pedie,
No problem about yesterday. I was pretty tied up myself.
As for the formatting code, I too have no problem running it without error.
(And MrKowz is showing you what I was going to to clean it up.)

 

[pedie]

I also can run it without problem but when the sheet is locked.

i used the code sheet1.unprotect password:-"...."
but still it gives error....not sure what was the problem that it is giving error

 

[HalfAce]

I hate to ask the obvious... but is Sheet1 the sheet named 'Transport'?

What happens if you run:
Sheets("Transport").Unprotect "...." ?

 

[pedie]

Dan, I think it is working now....
Thank you soo much...I know you're busy but you're still here trying to help me everynow and then since Peter and Brian is not around these days.....

Dan, thanks alot!

I wonder where are these guys..!