Type Mismatch on some PCs

[Guest]

I have an Excel workbook which has some code on one of the sheets. All this
does is hide or unhide rows depending on whether there word true is found in
column C (in the row thath will be hidden or revealed). The true/false is
created from a tick box which the user selects, there are a number of them.
So far, its all straight forward and this works on 98% of peoples PCs, but
some people get a Run time error, type mismatch when they click a tick box.
They are all running the same system Win XP and Excel 2002. Has anybody got
any ideas on how to fix this? The code is as follows:

Range("C12:C514").Select
For Each cell In Selection
If cell.Interior.ColorIndex = 45 Then
cell.Value = False
End If
Next cell


Range("C12:C514").Select
For Each cell In Selection
If cell = True Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell

 

[Guest]

Try adding two lines to the first section of code:

Range("C12:C514").Select
For Each cell In Selection
If cell.Interior.ColorIndex = 45 Then
cell.Value = False
Else
cell.Value = True
End If
Next cell

 

[Tom Ogilvy]

If you don't want to change the value in the cell you could do

Range("C12:C514").Select
For Each cell In Selection
If cell.Interior.ColorIndex = 45 Then
cell.Value = False
End If
Next cell


Range("C12:C514").Select
For Each cell In Selection
If cell.Text = "True" Then ' <== change
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell

 

[Guest]

What if ithe cell color index = say, 35 and the cell value = "Hello" or 10
or blank
Then the 1st half of the code doesn't change it to TRUE or "True"

I think that if this could be the case then either the first section of code
must change the cell to True or "True" Or, in the 2nd half of the code make
the condition the same as in the 1st half:

Range("C12:C514").Select
For Each cell In Selection
If Not cell.Interior.ColorIndex = 45 Then '<=== Use Not or change = to
<>
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell

 

[Guest]

Thanks for your help so far, the suggestions are not working, but I think
thats because I haven't explained the situation well enough, so here
goes....The user chooses options (on another sheet - sheet A), when they get
to the sheet that this code deals with (sheet B), they will have further
options depending on what they originally selected (on sheet A). If they
didn't select a specific option on sheet A, then the further options will be
hidden on sheet B. Column C is also always hidden and always contains True
or False. The ColorIndex 45 bit of the code refers to the tick box linked
Cells. All the user originally sees is several further options headings
(depending on what they selected on Sheet A), they tick the tick box to
reveale more underlying options. If they move away from Sheet B, when they
come back to it they see just the headings and the tickboxs again, even if
they ticked it previously (as they are now hidden again).

Column C: If for example an option selection on Sheet B is 50 Rows, the
first row will be the tick box linked cell (colorindex 45 etc) and the other
49 rows will refer to this cell eg = C1 etc. So they idea is that when a
tickbox is selected all the relevant cells in column C say true and so they
are then revealed.

 

[Guest]

I'm viewing your posts thru msdn's Discussions in excel.programming.
Apparently, this forum does not allow a user to post an attachement of a
file. Since I am unable to see all the conditions from your verbal
description, you might want to make a copy of your file, strip it down to the
bare essentials, compress it with WinZip and post it on Woody's lounge at
http://www.wopr.com/cgi-bin/w3t/postlist.pl?Cat=&Board=xl
with a description of what you want the file to do that is not happening.

Then post back to this thread that you have done the above and what is the
title of you Woody's post or the post number. I will get this message from
this forum and then go to Woody's to download your file.

Hope this helps

 

[Tom Ogilvy]

My Suggestion only acts on cells that are True and avoids a type mismatch
error caused by the value in the cell. How the cell in question receives
its value is outside the context of the code, but arbitrarily changing it
certainly isn't something I would recommend. The later post by delboy
implies that these cells reflect choices made by the user, so this would be
even more reason not to arbitrarily change them.

Since delboy says he can't get the solutions to work I can only conclude he
doesn't understand the suggestions (or hasn't implemented them as intended),
he has not provided essential details concerning the error, or the cause of
the error extends beyond the information presented.

 

[Guest]

I have done as you suggested. The title of the post is: Excel Type Mismatch
on some PC (Excel 2002), the post number is 443817. I have created a simple
dummy file which does what I tried to explain (badly it seems). The point of
all this is, this process works for 98% of users, but some users get a type
mismatch error.

 

[Guest]

I understand perfectly what your suggestion is doing and when I said it
doesn't work, I meant it doesn't give me the required result. I agree that
it is difficult without seeing a file. I have followed gocush advice and
posted a dummy file on Woodys Lounch (post number 443817) if you wish to have
a look. The main point here is the code, as written in the file works on 98%
of PCs, just not on all, and these are the users who get Type Mismatch errors.