help with this code please

[d0wnt0wn]

hi there.. Pennysaver has been working with me to get this code going but i think he/she has about given up on me now.... thanks again Pennysaver

could someone else please look at it and tell me what you think.... the first part of the code works fine... its the part with the input boxes that does not work at all..... what i want to do is click on a cell that has a data validation list..in the list you click on (new entry) and a input box appears and you can add to a list (we had that part working)..... then if you input something in there and press enter.. another box appears so you can populate the cell next to the list with the corresponding price


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Dim vResp As Variant
Dim sTestValid As String
Dim NewPrice As Currency

' Runs only when you change G6
If Target.Address = "$G$6" Then
' Clear values in G8 and G10
Range("G8, G10").ClearContents
End If

' Make sure the cell has validation
On Error Resume Next
sTestValid = Target.Validation.Formula1
On Error GoTo 0

' If the validation refers to our list and the user
' selected New Entry
If sTestValid = "= ValList" Then
If Target.Value = "(new entry)" Then
' Get the new value from the user
vResp = InputBox("Enter new item", "New Entry")
' If the user didn’t click cancel
If Len(vResp) > 0 Then
' add the new entry to just below ValList
NewPrice = Application.InputBox("Please enter the new price", _
"Price update", Type:=1)
' If the user didn’t click cancel
If Len(NewPrice) > 0 Then
' add the new entry to the right
With Range("ValList")
With Cells(.Cells.Count + 1)
.Value = vResp
.Offset(0, 1).Value = NewPrice
End With
End With
' Set the cell to the new entry
End If
End If
Target.Value = vResp
Else
' If the user cancelled, clear the cell
Target.ClearContents
End If
End If
End If

Application.EnableEvents = True

End Sub

 

[Tazguy37]

One thing I noticed was this:

If sTestValid = "= ValList" Then

If your source field for validation isn't exactly equal to that (if it says "=ValList", for example) you'll jump right out of the routine. Other than that, it looks like it's doing what you want. Do you want the new items and their prices in the same spot all the time? It looks like that's where they're going.

Hope that helps!

 

[d0wnt0wn]

ill make that adjustment and give it a try taz.... thanks... what the pupose of this is .... I want our estimators to be able to make a custom list of miscellaneous materials and theri prices..... they will each have a worksheet that gets 99% of it's pricing from a database but all of us seem to do things a little different so i thought id leave a custom list space for them on their sheet that does not attach itself to the database.

 

[d0wnt0wn]

TAZ i did that and i get this error

end if without block if......

so i delete an end if

and now nothing happens at all again

 

[d0wnt0wn]

anyone else have any ideas????