When true isn't true - help for newbie

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

m.s.w - 20 Mar 2006 07:24 GMT

Hi !

I try to learn how to write macros in excel. This is my first macro. I
have one problem. In a sheet I have few ComboBoxes which have
ListFillRange defined as an range of cells in another sheet (in the same
Workbook).
What i want to do is to check if the current value in ComboBox is a
value from the ListFillRange. If not, then it should be corrected.
Procedure is:

Sub Popraw_Wartosc(X As Object)
Dim K As Integer
Dim ItFits As Boolean
Dim Ilosc As Integer

ItFits = False
Ilosc = X.ListCount

For K = 0 To Ilosc - 1
If X.Value = X.List(K) Then
ItFits = True
End If
Next

If ItFits = False Then
X.Value = X.List(Ilosc - 1)
End If

End Sub

The problem is, that even if the current value of COmboBox has a
correspodning value in X.List, the "ItFits" variable never is true.
I try to watch this process, and on some stage I can read that X.Value
is lets say 1200, and the X.List(K) is also 1200 , but "ItFits" is still
False.

What am I missing?

Best Regards,
m.s.w

Reply to this Message

Gary Keramidas - 20 Mar 2006 07:33 GMT

just a guess, but what happens when you put quotes around true and false?

ItFits = "False"

Signature

Gary

> Hi !
>
[quoted text clipped - 36 lines]
> Best Regards,
> m.s.w

Reply to this Message

m.s.w - 20 Mar 2006 11:02 GMT

> just a guess, but what happens when you put quotes around true and false?
>
> ItFits = "False"

Thanks for your advise but it's not the case.
It looks, that even when X.Value is eqal to lets say X.List(4) (both
values are the same) the "If X.Value = X.List(K) Then..." is not run.
Like the values would be not equal.

m.s.w

Reply to this Message

Tom Ogilvy - 20 Mar 2006 15:55 GMT

Easier to check the ListIndex property

if x.ListIndex = -1 then
x.ListIndex = x.listcount - 1
else
msgbox "Item is in list"
End if

I don't think putting double qoutes on "False" will help since you declared
itfits as boolean and not string.

Signature

Regards,
Tom Ogilvy

> > just a guess, but what happens when you put quotes around true and false?
> >
[quoted text clipped - 6 lines]
>
> m.s.w

Reply to this Message

Gary Keramidas - 20 Mar 2006 16:15 GMT

that's true, i missed that part

Signature

Gary

> Easier to check the ListIndex property
>
[quoted text clipped - 17 lines]
>>
>> m.s.w

Reply to this Message

K Dales - 20 Mar 2006 18:16 GMT

If you are sure that X.Value is 1200 and X.List(K) is also 1200, then they
must be different variable types (e.g. one is a string) for ItFits to turn
out False. Put a breakpoint in your code on the line ItFits=True. When the
code breaks, go to the immediate pane and type the following:
? X.Value
? VarType(X.Value)
? X.List(K)
? VarType(X.List(K))
Look in Help or the object browser for the meaning of the corresponding codes.
This should be instructive in determining why you are getting ItFits=False.
3 possibilities:
1) for some reason the code is never reaching this line, so ItFits retains a
False value
2) the values are not what you think they are
3) the variables are incompatible types

Signature

- K Dales

> Hi !
>
[quoted text clipped - 36 lines]
> Best Regards,
> m.s.w

Reply to this Message

m.s.w - 20 Mar 2006 19:51 GMT

> If you are sure that X.Value is 1200 and X.List(K) is also 1200, then they
> must be different variable types (e.g. one is a string) for ItFits to turn
[quoted text clipped - 11 lines]
> 2) the values are not what you think they are
> 3) the variables are incompatible types

Thank you very much. That gave me a very surprising informations.
TO clarify:
ListFillRange is a range of cells (a column) with values:
200
500
650
800
1000
1200
1500
2000

When I did what you just said it showed to me that X.List(K) is a
Double-precision floating-point number, and the X.Value is a String
That's a surprise for me.
Why it doesn't breaks with "Type mismatch" error message?

Thanks anyway. I'll check the CInt or CStr (or not?).

Thank you,
m.s.w

Reply to this Message

m.s.w - 21 Mar 2006 06:24 GMT

The problem was type mismatch in a If...Then statement.

Doing:

(...)
If CInt(X.Value) = CInt(X.List(K)) Then
(...)
solved the problem.

m.s.w

Reply to this Message