need help with "Private Sub Worksheet_Change"

[framednlv]

I need to do something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim rng As Range
Set rng = Range("D9:D53")
If Intersect(Target, rng) Is Nothing Then Exit Sub
If ActiveCell.Range("A1").Value > "" Then 'blank then run Private Sub delete_data
If ActiveCell.Offset(0, -1).Value = "P" Then 'not blank and cell to left is "P" run Private Sub Add_data_P
If ActiveCell.Offset(0, -1).Value = "S" Then 'not blank and cell to left is "S" run Private Sub Add_data_S
If ActiveCell.Offset(0, -1).Value = "" Then 'not blank and cell to left is blank do nothing
End If
End If
End If
Application.ScreenUpdating = true
End Sub

When I make a change in the within the target range I need to do one of the following:

1) If blank then run "delete_data"
2) not blank and cell to left is "P" run Private Sub Add_data_P
3) not blank and cell to left is "S" run Private Sub Add_data_S
4) not bland and cell to left is not "P" or "S" then do nothing


The only training I have is what I can strip out of the forum examples. So if someone can help I would appreciate it.

Thanks,
Chris

 

[Smitty]

Maybe something like this:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Range("D9:D53")<br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Value > "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> delete_data<br>            <SPAN style="color:#00007F">If</SPAN> Target.Offset(, -1).Value = "P" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> Add_data_P<br>            <SPAN style="color:#00007F">If</SPAN> Target.Offset(, -1).Value = "S" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> Add_data_S<br>            <SPAN style="color:#00007F">If</SPAN> Target.Offset(, -1).Value = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> delete_data()<br>    MsgBox "delete data"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Add_data_P()<br>    MsgBox "Add data P"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Add_data_S()<br>    MsgBox "Add data S"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[framednlv]

Thanks Smitty,

When I delete a value I get an error:
run-time error '13': type mismatch.

Debug is on :
"If Target.Value > "" Then Call delete_data".


Thanks,
Chris

 

[Smitty]

Hmmm, I just tested it and don't get an error.

 

[Colin Legg]

2 initial thoughts on why that error might crop up...

Does the cell "target" is referencing contain an error value?
Does "target" reference more than 1 cell? eg. an array formula was entered across an array range.

Hope that helps...

 

[framednlv]

Ok, after deleting everything else in the VBA Project I unmerged the cells on the sheet and it doesn't error out. Seems that I'll have to do some work on my template.


Thanks again. I really appreciate it.


Chris

 

[Smitty]

Yeah, merged cells can cause all sorts of problems.

 

[framednlv]

I'm still having some problems with the code. I have the code that Smitty helped with and some other code within the worksheet_change. One of the problems is that I also have this code:

Dim rng As Range    
       Set rng = Range("C9:C53")
        On Error GoTo Errorhandler
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        Target.Value = UCase(Target)

I have two different ranges to Dim but I'm not sure how to do it. I tried to change rng to rng1 but only one set seems to work.


Thanks again,
Chris

 

[Smitty]

Give this a shot:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("C9:D53")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN><br>            <br>            <SPAN style="color:#00007F">With</SPAN> Application<br>                .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>                .EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                <br>                    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Column<br>                        <SPAN style="color:#00007F">Case</SPAN> 4<br>                            <SPAN style="color:#00007F">If</SPAN> Target.Value > "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> delete_data<br>                            <SPAN style="color:#00007F">If</SPAN> Target.Offset(, -1).Value = "P" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> Add_data_P<br>                            <SPAN style="color:#00007F">If</SPAN> Target.Offset(, -1).Value = "S" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> Add_data_S<br>                            <SPAN style="color:#00007F">If</SPAN> Target.Offset(, -1).Value = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>                        <SPAN style="color:#00007F">Case</SPAN> 3<br>                            Target.Value = UCase(Target.Value)<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>            <br>            .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>            .EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[framednlv]

Smitty,
Thanks Big time!!!!!!

It's one thing trying to follow the codes on the forum but a competly different story actually knowing how to program.


I'm going to change one thing but it shouldn't be a problem.

If Target.Offset(, -1).Value = "P" And Target.Value > "" Then Call Add_data_P

If Target.Offset(, -1).Value = "S" And Target.Value > "" Then Call Add_data_S

This part of the code helped with the rest of my code on the same sheet:

If Target.Count > 1 Then Exit Sub

Thanks,
Chris