MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.