| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need worksheet Event for Sum
Dear Sir, I have one worksheet : DailyPurchase, and there are some fields A: Date B: Material Name C: Qty D: Price E: Total Price I want worksheet event who will calculate automatically when I enter Qty and Price then Automatically it will give me the sum in E column. Qty*Price in E column. without inserting the Sum furmula in worsheet. Thanks hope you understand. Regards Shahzad |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need worksheet Event for Sum
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C:D" On Error GoTo ws_exit Application.EnableEvents = False If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Me.Cells(.Row, "C").Value < "" And _ Me.Cells(.Row, "D").Value < "" Then Me.Cells(.Row, "E").Value = Me.Cells(.Row, "C").Value * _ Me.Cells(.Row, "D").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Dear Sir, I have one worksheet : DailyPurchase, and there are some fields A: Date B: Material Name C: Qty D: Price E: Total Price I want worksheet event who will calculate automatically when I enter Qty and Price then Automatically it will give me the sum in E column. Qty*Price in E column. without inserting the Sum furmula in worsheet. Thanks hope you understand. Regards Shahzad |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need worksheet Event for Sum
Hi Shazad4u,
To allow for a deletion of a quantity or a price, perhaps add an else condition. Try replacing Bob's: If Me.Cells(.Row, "C").Value < "" And _ Me.Cells(.Row, "D").Value < "" Then Me.Cells(.Row, "E").Value = Me.Cells(.Row, "C").Value * _ Me.Cells(.Row, "D").Value End If with If Me.Cells(.Row, "C").Value < "" And _ Me.Cells(.Row, "D").Value < "" Then Me.Cells(.Row, "E").Value = Me.Cells(.Row, "C").Value * _ Me.Cells(.Row, "D").Value Else Me.Cells(.Row, "E").ClearContents End If --- Regards. Norman "Bob Phillips" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:D" On Error GoTo ws_exit Application.EnableEvents = False If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Me.Cells(.Row, "C").Value < "" And _ Me.Cells(.Row, "D").Value < "" Then Me.Cells(.Row, "E").Value = Me.Cells(.Row, "C").Value * _ Me.Cells(.Row, "D").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Dear Sir, I have one worksheet : DailyPurchase, and there are some fields A: Date B: Material Name C: Qty D: Price E: Total Price I want worksheet event who will calculate automatically when I enter Qty and Price then Automatically it will give me the sum in E column. Qty*Price in E column. without inserting the Sum furmula in worsheet. Thanks hope you understand. Regards Shahzad |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need worksheet Event for Sum
sorry for jumping in.
How to prevent user from entering/deleting any data in Row E? cheers "Norman Jones" wrote in message ... Hi Shazad4u, To allow for a deletion of a quantity or a price, perhaps add an else condition. Try replacing Bob's: If Me.Cells(.Row, "C").Value < "" And _ Me.Cells(.Row, "D").Value < "" Then Me.Cells(.Row, "E").Value = Me.Cells(.Row, "C").Value * _ Me.Cells(.Row, "D").Value End If with If Me.Cells(.Row, "C").Value < "" And _ Me.Cells(.Row, "D").Value < "" Then Me.Cells(.Row, "E").Value = Me.Cells(.Row, "C").Value * _ Me.Cells(.Row, "D").Value Else Me.Cells(.Row, "E").ClearContents End If --- Regards. Norman "Bob Phillips" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:D" On Error GoTo ws_exit Application.EnableEvents = False If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Me.Cells(.Row, "C").Value < "" And _ Me.Cells(.Row, "D").Value < "" Then Me.Cells(.Row, "E").Value = Me.Cells(.Row, "C").Value * _ Me.Cells(.Row, "D").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Dear Sir, I have one worksheet : DailyPurchase, and there are some fields A: Date B: Material Name C: Qty D: Price E: Total Price I want worksheet event who will calculate automatically when I enter Qty and Price then Automatically it will give me the sum in E column. Qty*Price in E column. without inserting the Sum furmula in worsheet. Thanks hope you understand. Regards Shahzad |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need worksheet Event for Sum
Perhaps, try this minor adaptation of
Bob's code: '========== Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:D" Const PWORD As String = "ABC" '<<==== CHANGE On Error GoTo ws_exit Application.EnableEvents = False If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Unprotect Password:=PWORD With Target If Me.Cells(.Row, "C").Value < "" And _ Me.Cells(.Row, "D").Value < "" Then Me.Cells(.Row, "E").Value _ = Me.Cells(.Row, "C").Value * _ Me.Cells(.Row, "D").Value Else Me.Cells(.Row, "E").ClearContents End If End With End If ws_exit: Me.Protect Password:=PWORD Application.EnableEvents = True End Sub ''<<========== --- Regards. Norman "tim" wrote in message ... sorry for jumping in. How to prevent user from entering/deleting any data in Row E? cheers "Norman Jones" wrote in message ... Hi Shazad4u, To allow for a deletion of a quantity or a price, perhaps add an else condition. Try replacing Bob's: If Me.Cells(.Row, "C").Value < "" And _ Me.Cells(.Row, "D").Value < "" Then Me.Cells(.Row, "E").Value = Me.Cells(.Row, "C").Value * _ Me.Cells(.Row, "D").Value End If with If Me.Cells(.Row, "C").Value < "" And _ Me.Cells(.Row, "D").Value < "" Then Me.Cells(.Row, "E").Value = Me.Cells(.Row, "C").Value * _ Me.Cells(.Row, "D").Value Else Me.Cells(.Row, "E").ClearContents End If --- Regards. Norman |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need worksheet Event for Sum
On May 21, 1:18*pm, "Norman Jones"
wrote: Perhaps, try this minor adaptation of Bob's code: '========== Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:D" Const PWORD As String = "ABC" * * '<<==== CHANGE * * On Error GoTo ws_exit * * Application.EnableEvents = False * * If Target.Cells.Count 1 Then Exit Sub * * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then * * * * Me.Unprotect Password:=PWORD * * * * With Target * * * * * * If Me.Cells(.Row, "C").Value < "" And _ * * * * * * * * Me.Cells(.Row, "D").Value < "" Then * * * * * * * * Me.Cells(.Row, "E").Value _ * * * * * * * * * * * * = Me.Cells(.Row, "C").Value * _ * * * * * * * * * * * * * * * * Me.Cells(.Row, "D").Value * * * * * * Else * * * * * * * * Me.Cells(.Row, "E").ClearContents * * * * * * End If * * * * End With * * End If ws_exit: * * Me.Protect Password:=PWORD * * Application.EnableEvents = True End Sub ''<<========== --- Regards. Norman "tim" wrote in message ... sorry for jumping in. How to prevent user from entering/deleting any data in Row E? cheers "Norman Jones" wrote in message ... Hi Shazad4u, To allow for a deletion of a quantity or a price, perhaps add an else condition. Try replacing Bob's: * * * * * *If Me.Cells(.Row, "C").Value < "" And _ * * * * * * * *Me.Cells(.Row, "D").Value < "" Then * * * * * * * *Me.Cells(.Row, "E").Value = Me.Cells(..Row, "C").Value * _ * * * * * * * *Me.Cells(.Row, "D").Value * * * * * *End If with * * * * * *If Me.Cells(.Row, "C").Value < "" And _ * * * * * * * *Me.Cells(.Row, "D").Value < "" Then * * * * * * * *Me.Cells(.Row, "E").Value = Me.Cells(.Row, "C").Value * _ * * * * * * * * * * * * * * * *Me.Cells(.Row, "D").Value * * * * * *Else * * * * * * * *Me.Cells(.Row, "E").ClearContents * * * * * *End If --- Regards. Norman- Hide quoted text - - Show quoted text - Hi Norman I am already using the following event in the worksheet. Private Sub Worksheet_Change(ByVal Target As Range) again same event giving the errors. now what to do. Regards. Shahzad |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with worksheet event | Excel Worksheet Functions | |||
| Event Procedures: Event on Worksheet to fire Event on another Worksheet | Excel Programming | |||
| Copying Worksheet triggers Click event of combobox on another worksheet | Excel Programming | |||
| macro to apply worksheet event to active worksheet | Excel Programming | |||
Hybrid Mode
