Workbook_change event ' doesnot work

[Joke]

AFter much struggle let me turn to the board again maybe one of you can easily help me out.

I have made the following WS' change event to give a error message if the number entered in c13 already exists in the database which is queried in sheet4'b28 with a SQL.REQUEST.

So if the number doesnot exist b28 gives an error which means otherwise i need the message box and prevent the user to continue before entering antoher number............

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nExiste As Integer
Dim Msg
Msg = "No pueden existir prestamos duplicados"
If Target.Address = "$c$13" Then
nExiste = IIf(IsError(Sheet4.Range("b28").Value), 0, Sheet4.Range("b28").Value)
If nExiste > 0 Then
MsgBox Prompt:=Msg, Buttons:=vbOKOnly, Title:="Error"
Continuar = False
End If
End If
End Sub


Code is accepted by not triggered by entering duplicate number in the cell.

What did i do wrong???

Joke

 

[bydesign]

Joke

The line:
nExiste = IIf(IsError(Sheet4.Range("b28").Value), 0, Sheet4.Range("b28").Value)

has a typo... IIF should be IF

and

to use excel functions in VBA you need to change this line to
nExiste = Application.WorksheetFunction.If(IsError(Sheet4.Range("b28").Value), 0, Sheet4.Range("b28").Value)


Hope that helps...

 

[Juan Pablo González]

Actually, there is an IIF() function in VBA, so that's not a typo. The problem is, as the help file says, that even if the condition statement is True, both the True and False statements are evaluated.

And as the OP is saying, B28 is producing an error, therefor, causing an error in VBA when it tries to convert it to an integer... again, even if the condition is TRUE...

So, the solution is to use the standard IF

If IsError(Sheet4.Range("b28").Value) Then
    nExiste = 0
Else
    nExiste = Sheet4.Range("b28").Value
End If

 

[Joke]

cant get it to work even with your advise. This is the lastest version im trying but doesnot trigger of the event.......

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nExiste As Integer
Dim Msg
Dim Continuar As Boolean
Msg = "No pueden existir prestamos duplicados"
If Target.Address = "$c$13" Then
If IsError(Sheet4.Range("b30").Value) Then
nExiste = 0
Else
nExiste = Sheet4.Range("b30").Value
End If
If nExiste > 0 Then
MsgBox Prompt:=Msg, Buttons:=vbOKOnly, Title:="Error"
Continuar = False
End If
End If
End Sub

MAybe should make my life easier using the validation fucntion instead of a macro. Although i cant stand it becuase it worked a few times after changing the original code.

Any further advise ???????
Joke

 

[parry]

Hi I think I see the problem. Excel is case sensitive when it does its 'equals to' tests. When an address is returned its all in upper case where you have used this line where the C is in lower case therefore its not seeing a change in cell C13...

If Target.Address = "$c$13" Then

should be...

If Target.Address = "$C$13" Then

You can also test if the target is in the range by using intersect like this...

If Not Intersect(Target, [C13]) Is Nothing Then

hth

 

[Joke]

Perfect it is working !!! woh this is really quite a discovery all this VBA stuff. Now I only have to find the code to prevent the user to continue until he filled in a correct numer in cell C12.

Thanks again!

Joke