Error code 13

[Yvester]

I receive the error code 13 when I close the workbook. here is the code :

Private Sub CLIENT_Change()

Worksheets("Facture").Cells(5, 2).Value =
Worksheets("Donnees").Cells(CLIENT.Value + 2, 1)

End Sub

This sub's function is to put a text value taked from the CLIENT combobox
value and to put it in a specific cell.

tx

 

[sebastienm]

Him

CLIENT.Value returns a string, say "Hello" therefore
CLIENT.Value + 2 returns 'hello2'

If you are looking for the index of the selection within the combo, use:
Client.ListIndex + 2 ''' Note ListIndex starts at 0 for the first item

If your combo contains numbers (stored as strings) and you meant to use
these numbers, you have to convert them from strings to numbers first:
Clng(Client.Value) + 2

 

[Yvester]

Hi, maybe I didn't explain correctly my toughts.

the CLIENT.Value + 2 refeer to a specific row, based on the value returned
by the CLIENT combobox + a constant (2 in this case). and then, this value
(string) is put in a specific cell (5,2 or "B5".

Sorry for my poor explications on the first post

Yves

 

[sebastienm]

the CLIENT.Value + 2 refeer to a specific row
I understand, but it don't think it is what it does.
What does the following expression returns (in the _Change sub):
debug.print CLIENT.Value + 2

If Client.Value retuns the a 'number' (as a String though: "10" and not 10),
try replacing the above expression with
Clng(Client.Value) + 2
Still having the issue?

 

[Yvester]

I try the Cint and it works but now, it's the error 94 who pop up! geezzzz...

 

[sebastienm]

Not sure what error 94 is but i wouldn't be surprised that there is a Null
somewhere that messes up things.

You may want to cut your line of code into multiple lines to better grasp
what's going on and which expression triggers the error.

Private Sub CLIENT_Change()
Dim row as Long
Dim v as variant

row= Clng(CLIENT.Value) + 2
v=Worksheets("Donnees").Cells(rows, 1)
Worksheets("Facture").Cells(5, 2).Value = v
End Sub

Note: an Integer only goes up to 32768 so the expression will fail passed
that, therfore Cint will fail the same way. Instead, use CLng (Long).

 

[Yvester]

It's weird. I divise the code like you suggest and I take the liberty to
insert msgbox between each instruction and the result in each msgbox are ok.

The error code comes only when I close the workbook.

I never think that excel can be so picky

thanks again

 

[sebastienm]

Weird. I tried the code (and I replace the typo-error 'rows' by 'row') and i
works fine for me , even when closing the book.

Also, in the Immediate Window:
? Error(94)
returns: Invalid use of Null
So it seems lilke there is definitely some issue with a Null value somewhere.

Does it break within that CLIENT_Change or at some other place ?
Do you have some code in the Workbook_BeforeClose event ?
Finally, what about replacing CLIENT_Change by CLIENT_CLick instead ?

 

[Yvester]

I'm very confuse. Because I can't change the event from change to click (I
need the new selection for the rest of program) I try to divise the problem.
And now, the line :

row = CLIENT.value

generate the error. I try with or withon the CLng, CInt, with dim row as well

Can someone do a miracle? ; - )

 

[George Nicholson]

works fine for me , even when closing the book.
The difference might be due to how the combo is being filled (or emptied
during close...) between versions. Sounds like some sort of timing issue, so
it could even be a CPU difference.

In any case, the combo is the most likely (only possible?) source of a Null,
so maybe this would help:

If(IsNull(CLIENT) Then
Exit Sub
'Alternatively, assign row a specific value if CLIENT has no selection.
'row = 2
Else
row= Clng(CLIENT.Value) + 2
End If

 

[sebastienm]

It pobably happens because CLIENT.Value is Null which happens when nothing is
selected in the Combo. Could that be possible? (when the combo is reset /
when the book opens/ ...)

Try the following: _Change --> exit sub if nothing selected : ListIndex<0

''' ----------------------------
Private Sub CLIENT_Change()
Dim row as Long
Dim v as variant

If Client.ListIndex<0 then Exit SUb

row= Clng(CLIENT.Value) + 2
v=Worksheets("Donnees").Cells(row, 1)
Worksheets("Facture").Cells(5, 2).Value = v
End Sub
''' ---------------------------------

Does that fix the error?

 

[Yvester]

Oh yeah! I've got it... with your solution Sébastien. It works with the exit
sub before the wanted action...

Private Sub Clients_Change()

If Clients.ListIndex < 0 Then Exit Sub

Worksheets("Facture").Cells(5, 2).Value =
Worksheets("Donnees").Cells(Clients.Value + 2, 1)

End Sub

It prevents the case of Clients.Value = null.

Thanks for your time Sébastien and also for you, George.

Have a nice Holidays everyone

 

[sebastienm]

Alright
That was an issue with a Null value then.

ListIndex returns -1 when no item from the combo is selected, else it
returns 0 for the first item, 1 for the second, ....
Whenever you enter a sub and need to check the value of a combo (or
listvox), check its ListIndex to see if somthing is selected else exit.
Also it is a read/write property. That is, once you fill up a combobox with
values, you can set the combo's selection to the 1st item in the list by
doing:
combo.ListIndex = 0

A bientot et bonne chance,