Workbook_change event ' doesnot work

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

bydesign

Board Regular
Joined
Nov 29, 2003
Messages
184
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... (y)
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

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

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171
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

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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...
Code:
If Target.Address = "$c$13" Then
should be...
Code:
If Target.Address = "$C$13" Then

You can also test if the target is in the range by using intersect like this...
Code:
If Not Intersect(Target, [C13]) Is Nothing Then

hth
 

Joke

Board Regular
Joined
Jan 21, 2004
Messages
171
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top