Help with a code

J

Joe@excel

I have these code and it works find: but i also want to use it for
different culumn within the same sheet. how do i add Column D with
different target? meaning "S" for Study and "B" for books and s
on...

and maybe adding a Msg box to it that will only allow the Letters
specify..
thank you in advance..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
If UCase(Target) = "C" Then
Target = "Contribution"
Else
If UCase(Target) = "D" Then
Target = "Deposits"
Else
If UCase(Target) = "N" Then Target = "N/A"
End If
End If
End If
End Su
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
If UCase(Target) = "C" Then
Target = "Contribution"
Else
If UCase(Target) = "D" Then
Target = "Deposits"
Else
If UCase(Target) = "N" Then Target = "N/A"
End If
End If
Else
If Not Intersect(Target, Range("D6:D50")) Is Nothing Then
If UCase(Target) = "S" Then
Target = "Study"
Else
If UCase(Target) = "B" Then
Target = "Books"
Else
If UCase(Target) = "N" Then Target = "N/A"
End If
End If
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

A typo here, and can be simplified with

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
If UCase(Target) = "C" Then
Target = "Contribution"
ElseIf UCase(Target) = "D" Then
Target = "Deposits"
ElseIf UCase(Target) = "N" Then
Target = "N/A"
End If
ElseIf Not Intersect(Target, Range("D6:D50")) Is Nothing Then
If UCase(Target) = "S" Then
Target = "Study"
ElseIf UCase(Target) = "B" Then
Target = "Books"
ElseIf UCase(Target) = "N" Then
Target = "N/A"
End If
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


Bob Phillips said:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
If UCase(Target) = "C" Then
Target = "Contribution"
Else
If UCase(Target) = "D" Then
Target = "Deposits"
Else
If UCase(Target) = "N" Then Target = "N/A"
End If
End If
Else
If Not Intersect(Target, Range("D6:D50")) Is Nothing Then
If UCase(Target) = "S" Then
Target = "Study"
Else
If UCase(Target) = "B" Then
Target = "Books"
Else
If UCase(Target) = "N" Then Target = "N/A"
End If
End If
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


message news:[email protected]...
Click to expand...
 
D

Don Guillett

Another idea using select case

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 3 Then
Select Case UCase(Target)
Case "D": x = "Dee"
Case "E": x = "Eeee"

Case Else: x = Target
End Select
Target = x
End If
'---------
If Target.Column = 4 Then
Select Case UCase(Target)
Case "D": x = "Dee"
Case "E": x = "Eeee"

Case Else: x = Target
End Select
Target = x
End If
Application.EnableEvents = True
End Sub
 
D

Dave Peterson

One more!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim tempVal As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("c6:c50,d6:d50")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False

tempVal = ""
Select Case Target.Column
Case Is = Me.Range("C1").Column
Select Case LCase(Target.Value)
Case "c": tempVal = "Contribution"
Case "d": tempVal = "Deposit"
Case "n": tempVal = "N/A"
End Select
Case Is = Me.Range("d1").Column
Select Case LCase(Target.Value)
Case "s": tempVal = "Study"
Case "b": tempVal = "Books"
End Select
End Select

If tempVal = "" Then
'do nothing
Else
Target.Value = tempVal
End If

errHandler:
Application.EnableEvents = True
End Sub
 
Top