For Dave Hawley...

Posted by Nona Sloven on April 10, 2001 7:42 AM

Dave:

Again, thank you for your help. I wonder if you could possible make a little change in the code you sent me. This code is:

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")

Application.EnableEvents = True
On Error GoTo 0
Set WatchRange = Nothing

End If

End Sub

It worked very well and solved me a problem. Could you please add some coding to this code so that it runs a macro named TOTAL when you select "TOTAL >>" in the cell with data validation? I tried but I did not succeed. Thank you.

Note: Below is the message that I posted:

I have a worksheet in Excel 97. I have seven cells set up each cell with data validation in column F forming a range from F25 to F31. Each of these seven cells only accept the following words: PART, LABOR, MISCELANEOUS and TOTAL. I have another column to enter amounts (numbers). This column is made up of cells J25 to J31. I want Excel to do the following: First after selecting any of the specific words listed above in the data validated cells, for example, in cell F25; if I then enter any number in J25, this number should be multiplied in this same cell by 1.12 only when it sees the word PART in F25 (the cell on the left in the same row). The same thing should happen entering any number in any of the cells J26 thru J31 and the same word (PART) is seen in in their adyacent F26 thru F31 cells, the number entered should be multiplied by 1.12. I also want Excel to do the same if the word selected is LABOR instead of PART. In this case, the multiplication should be by 1.24 instead of 1.12. Could anybody help? Your answer will be appreciated. Thanks.

Nona

Posted by salvatore on April 10, 2001 7:15 PM

Maybe Dave is busy or he doesn't saw your question. I'll try to help. Hope I don't mess the things up:

You have to add another case selection in your code (I'll mark it with -->>)
:-->> case "TOTAL>>"
:-->> Call total

Hope it helps.
Salvatore.

Posted by Dave Hawley on April 10, 2001 8:06 PM

Hi Nona

Salvatore may have already supplied the answer for you, Thanks Salvatore!
BTW I was asleep :o)

Here is a another way that allows for your macro "Total" to be in either a normal module or within the Private module of the Worksheet (where the code is now)

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
Case "Total"
On Error Resume Next
Run "Total" 'If in a normal sub
Total 'If in this sub
On Error GoTo 0
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 11, 2001 7:17 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.