VBA Code wanted for Excel
 

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

VBA Code wanted for Excel
 

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

On Mon, 3 Mar 2008 14:50:06 -0800, lost in Chicago
wrote:

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

VBA Code wanted for Excel
 

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

"Gord Dibben" wrote:

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

On Mon, 3 Mar 2008 14:50:06 -0800, lost in Chicago
wrote:

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

VBA Code wanted for Excel
 

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


On Mon, 3 Mar 2008 16:43:26 -0800, lost in Chicago
wrote:

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

"Gord Dibben" wrote:

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

On Mon, 3 Mar 2008 14:50:06 -0800, lost in Chicago
wrote:

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

VBA Code wanted for Excel
 

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" wrote:

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


On Mon, 3 Mar 2008 16:43:26 -0800, lost in Chicago
wrote:

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

"Gord Dibben" wrote:

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

On Mon, 3 Mar 2008 14:50:06 -0800, lost in Chicago
wrote:

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

VBA Code wanted for Excel
 

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

On Mon, 3 Mar 2008 17:39:01 -0800, lost in Chicago
wrote:

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" wrote:

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


On Mon, 3 Mar 2008 16:43:26 -0800, lost in Chicago
wrote:

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

"Gord Dibben" wrote:

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

On Mon, 3 Mar 2008 14:50:06 -0800, lost in Chicago
wrote:

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