Dave Hawley, Salvatore. Are you there?
Posted by Nona Sloven on April 12, 2001 5:30 AM
Dave:
Your code seems to assume that, after selecting "TOTAL >>", the user will enter a value in the same row in range J25:J31 for the macro to be run. In the particular case of selecting "TOTAL >>", however, it should be different. The macro Total should run only when the user selects "TOTAL >>" in the Data Validated cell without entering any value in the corresponding cell on the right in the above range. Let me clarify something: The macro Total is a macro I have recorded to automatically make a mathematical operation and to add special formatting on the cell where a total should result for the values previously entered on the cells above it in range J25:J31. Please help. Thank you.
Note: I also appreciate Salvatore wanting to provide a solution to my posting. His input is welcome.
Posted by Dave Hawley on April 12, 2001 6:06 AM
Hi Nona
I must confess I'm not too sure what you mean, but here's a stab in the dark. Hopefully it will put you on the right track.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range, Rw As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub
Set WatchRange = Range("J25:J31")
If Not Intersect(Target, WatchRange) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
Rw = Target.Row
Select Case Range("F" & Rw)
Case "PART"
Target = Target * 1.12
Case "LABOUR"
Target = Target * 0.25
End Select
Select Case Target
Case "Total"
If Range("F" & Rw) = "" Then
On Error Resume Next
Run "Total" 'If in a normal sub
Total 'If in this sub
On Error GoTo 0
End If
End Select
Application.EnableEvents = True
On Error GoTo 0
Set WatchRange = Nothing
End If
End Sub
Dave
OzGrid Business Applications
Posted by Nona Sloven on April 12, 2001 7:25 AM
Re: Not yet solved. Seems to be skipping something
Dave: Thank you for your reply, but the coding you added did not work at all. I suggest you read my note again. When I select "TOTAL >>" from the list provided by one of the data validated cells, I expect the macro to run but it doesn't do it. The code you added may require that the user performs another action besides selecting the option "TOTAL >>" I will be glad and thankful if you solve this for me. Thank you.
Nona Sloven : Dave
Case "PART" Target = Target * 1.12 Case "LABOUR" Target = Target * 0.25 End Select Select Case Target Case "Total" If Range("F" & Rw) = "" Then Run "Total" 'If in a normal sub Total 'If in this sub On Error GoTo 0 End If End Select Application.EnableEvents = True On Error GoTo 0 Set WatchRange = Nothing End If End Sub
Posted by Dave Hawley on April 12, 2001 7:41 AM
Re: Not yet solved. Seems to be skipping something
Nona, exactly which range contains your Validated cells ?
Dave
OzGrid Business Applications
Posted by Nona Sloven on April 12, 2001 8:29 AM
Re: Not yet solved. Seems to be skipping something
Dave: The range is F25:F31. Thanks. Nona, exactly which range contains your Validated cells ?
Posted by Dave Hawley on April 12, 2001 11:56 AM
Re: Not yet solved. Seems to be skipping something
The range is F25:F31. Thanks. : Nona, exactly which range contains your Validated cells ? :
Ok, try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange1 As Range, Rw As Integer
Dim WatchRange2 As Range
If Target.Cells.Count > 1 Then Exit Sub
Set WatchRange2 = Range("F25:F31")
If Not Intersect(Target, WatchRange2) Is Nothing Then
Select Case Target
Case "Total"
On Error Resume Next
Run "Total" 'If in a normal sub
Total 'If in this sub
On Error GoTo 0
End Select
End If
If Not IsNumeric(Target) Then Exit Sub
Set WatchRange1 = Range("J25:J31")
If Not Intersect(Target, WatchRange1) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
Rw = Target.Row
Select Case Range("F" & Rw)
Case "PART"
Target = Target * 1.12
Case "LABOUR"
Target = Target * 0.25
End Select
On Error GoTo 0
Set WatchRange1 = Nothing
Set WatchRange2 = Nothing
End If
Application.EnableEvents = True
End Sub
Dave
OzGrid Business Applications
Posted by salvatore on April 13, 2001 8:55 AM
Re: Not yet solved. Seems to be skipping something
the question would be: If user selects your "total-cell" and inputs TOTAL>>, it has to run a macro and return value to same cell?
Posted by Nona Sloven on April 16, 2001 11:43 AM
Re: Your Code Now Worked Ok. Thanks Dave.
Thank you very much Dave.
Nona : Dave: The range is F25:F31. Thanks.
