Out of space stack error, simple code?

[Guest]

Am novice in VB. was trying to take baby steps.
The editcolb macro was hand edited, and was working in test mode on 1 cell
when I ran the macro from the macros dialog box.

Things went blooey when I tried to execute if from the private worksheet
coding area.

From the doc I know it's a system error, but I have no clue why I caused it.
Help! Thanks.


Sub EditColB()
Range("b5") = Trim(Range("b5"))
If Range("b5") <> " " And Range("b5") <> "" And _
Range("b5") <> "3" And Range("b5") <> "4"
Then MsgBox "Valid delivery codes are: blank, 3, 4"
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Application.Run "routemodel.xls!editcolb"
End Sub

 

[Guest]

A few things here...

Try this code and see how it goes for you...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$B$5" then
target.value = Trim(target.value)
If target.value <> "" And target.value <> "3" And target.value <> "4"
Then
MsgBox "Valid delivery codes are: blank, 3, 4", vbcritical, "Error"
endif
end if
End Sub

 

[Guest]

Dear Jim -
thanks, how it went was:
valid values are fine, but 1st bad value caused msg box to come up
AND IT STAYED UP. I clicked OK, but it kept coming back. Had to
re-boot.

I will remove vbcritical for now.
1) how do you get user back to the bad cell to fix the input?
2) b5 was first test for me, it's actually all of column b but I don't
yet know how to address that in the macro. Help?
3) and lastly, the final step will be to add a test in conjunction
with column D. e.g. if b56 is good then how do I generalize
the code to bring d56 into play?

Thanks so much. Neal