How do I use Worksheet_Change to use target value and to update 3 other sheets

[Upex]

Hi All,

I think this is a quick one, but I cant seem to get it to work and wonder if you can cast your expert eyes over it for me please

I need:

When cell G5:I5 is changed, I need it to use the value and put it in 3 different sheets at various cells.

I have this at present, the green bit is what I cant get working:

Private Sub Worksheet_Change(ByVal Target As Range)
       
    If Target.Count > 8 Then Exit Sub
    
        [COLOR=seagreen]If Target.Address = "G5:I5" Then
            Sheets("Review of 1st period").Range("h3").Value = Target.Value
            Sheets("Member Progression 1st period").Range("h3").Value = Target.Value             [/COLOR]
[COLOR=seagreen]            Sheets("Observations 1st period").Range("i3").Value = Target.Value
        End If
[/COLOR]        
    If Not Application.Intersect(Target, Rows("18:18")) Is Nothing Then
        If Len(Target.Value) = 0 Then
            Application.EnableEvents = False
            Target.Value = "Select Planned Course"
            Application.EnableEvents = True
        End If
    End If
    If Not Application.Intersect(Target, Range("b13")) Is Nothing Then
        If Len(Target.Value) = 0 Then
            Application.EnableEvents = False
            Target.Value = "300"
            Application.EnableEvents = True
        End If
    End If
End Sub

I think I may be using the target.value wrong (if it even exists) do I need to copy and paste the value or something similar? I get no errors, just doesnt transpose the target cell value to anyway.

Any help would be brill, as I'm feeling very stupid at the moment.

Thanks in advance, Upex

 

[VoG]

Try

If Not Intersect(Target, Range("G5:I5")) Is Nothing Then
    Sheets("Review of 1st period").Range("h3").Value = Target.Value
    Sheets("Member Progression 1st period").Range("h3").Value = Target.Value
    Sheets("Observations 1st period").Range("i3").Value = Target.Value
End If

 

[Upex]

Hi VOG,

Many thanks for your speedy reply.

Your sugesstion didn't work, but I added 'application.' before intersect and it worked. Many thanks

For future reference the working code looks like:

If Not [B]Application.[/B]Intersect(Target, Range("G5")) Is Nothing Then
            Sheets("Review of 1st period").Range("h3").Value = Target.Value
            Sheets("Member Progression 1st period").Range("h3").Value = Target.Value
            Sheets("Observations 1st period").Range("i3").Value = Target.Value
        End If

Again, thanks VOG, saved my sanity.

Regards, Upex

 

[Upex]

Hi VoG & Others.

Just a quick question: the above solution works brilliantly, but if I delete the entry in the target cell, the places that it copied the value to, remain with that value. If i wanted to have the other cells clear when the target cell is cleared, would that be a separate bit of code - i.e. upon clearing target, clear others. OR can it be added into the existing code to say that also do clearing as well as value entry?

I have this at present:

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Target.Count > 1 Then Exit Sub
    
    If Not Application.Intersect(Target, [G5]) Is Nothing Then
        Review_1.[h3].Value = Target.Value
        Progression_1.[h3].Value = Target.Value
        Observations_1.[i3].Value = Target.Value
    End If
    If Not Application.Intersect(Target, [e3]) Is Nothing Then
        Review_1.[c4].Value = Target.Value
        Progression_1.[c4].Value = Target.Value
        Observations_1.[b5].Value = Target.Value
    End If
    If Not Application.Intersect(Target, Rows("18:18")) Is Nothing Then
        If Len(Target.Value) = 0 Then
        Application.EnableEvents = False
        Target.Value = "Select Planned Course"
        Application.EnableEvents = True
        End If
    End If
    If Not Application.Intersect(Target, [b13]) Is Nothing Then
        If Len(Target.Value) = 0 Then
        Application.EnableEvents = False
        Target.Value = "300"
        Application.EnableEvents = True
        End If
    End If
End Sub

Many thanks for your input

Upex

 

[VoG]

Clearing just the target cell should clear the destination cells as well. If you are clearing more than one cell at at time then

If Target.Count > 1 Then Exit Sub

will prevent the code from running.

 

[Upex]

VoG

Thanks again for your reply.

I have commented out

If Target.Count > 1 Then Exit Sub

and it now clears the other cells with the target cell. I think this may be due to the merged cells that it uses (I know merge is bad, but couldnt find a better way).

One question that you may also be able to offer a sugestion on:

I need to apply this code to 39 different sheets. It it possible to be stored in a module just the once and called upon within the sheet code as a change event that calls the module proceedure name? Havent tried as would need to change the code to accomodate the different period numbers, but wonder if it is possible, as if it is, I may try.

Many thanks for your help

regards, Upex

 

[VoG]

Not easily as Target is only meaningful within the event procedure. You would need to pass Target as an argument to a sub within a regular module.

 

[Upex]

Sounds like I'll leave that till a day where I have more time. Good to know its possible though.

Many thanks for your help with getting all these questions answered and problems sorted.

Have a great day, Cheers, Upex