VBA Code wanted for Excel

[lost in Chicago]

VBA code needed to: enter choice of 1 or 2 in column 33 with 1 being equal
to 1495 and 2 equal to 3995, at the same time enter 1 in column 31 and the
Date in column 32

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 33 Then
Application.EnableEvents = False
With Target
If .Value = 1 Then
.Value = 1495
ElseIf .Value = 2 Then
.Value = 3995
End If
End With
With Target
.Offset(0, -2).Value = 1
.Offset(0, -1).Value = Format(Date, "mm/dd/yyyy")
End With
End If
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module. Alt + q to return to the Excel window.

Type a 1 or two in any cell of column 33(AG)


Gord Dibben MS Excel MVP

 

[lost in Chicago]

hmmmmmm nothing happened I've got excel 2003 tried it twice with no results???

 

[Gord Dibben]

hmmmmmmmmmmmm that's strange.

What do you mean you "tried it twice"?

Did you copy it into the worksheet module?

Did you enter a 1 or 2 in AG1?

Tested with your requirements as I interpreted them before posting.

I will re-post with an error trap added. I meant to put it in first time.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 33 Then
On Error GoTo endit
Application.EnableEvents = False
With Target
If .Value = 1 Then
.Value = 1495
ElseIf .Value = 2 Then
.Value = 3995
End If
End With
With Target
.Offset(0, -2).Value = 1
.Offset(0, -1).Value = Format(Date, "mm/dd/yyyy")
End With
End If
endit:
Application.EnableEvents = True
End Sub

I can post a workbook to a file host for you to download if you can't get this
working.


Gord

 

[lost in Chicago]

Ah working now. I changed: Private Sub Worksheet_Change(ByVal Target As... to
Private Sub Worksheet_Changeit(ByVal Target As... because I ALREADY HAD a
code with that name that game me an ambiguous error. I switched the names and
now it works Great! You are the MAN!! Thanks very much.

 

[Gord Dibben]

Good to hear, but now you have only one set of event code that works.

Private Sub Worksheet_Changeit(ByVal Target As Range) is not a legal event so
Excel will just ignore it.


Gord