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

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

ItFits = "False"

--


Gary


"m.s.w" wrote in message
...
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

Gary Keramidas wrote:
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

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.
--
Regards,
Tom Ogilvy


"m.s.w" wrote:

Gary Keramidas wrote:
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

that's true, i missed that part

--


Gary


"Tom Ogilvy" wrote in message
...
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.
--
Regards,
Tom Ogilvy


"m.s.w" wrote:

Gary Keramidas wrote:
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

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
--
- K Dales


"m.s.w" wrote:

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

K Dales wrote:
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

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

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