Run time error '13' - type mismatch

Hello

I've posted this same question in another forum, but got no responses, I'm hoping someone here can help me!

I have the run time error '13' type mismatch problem on the line that I have bolded. I think I understand why the problem occurs, I just don't know how to solve it.

What is really annoying about it all is that we don't get the error in Excel 2007 at all until the file is sent in an email, used in Excel 2010, and when it's sent back to be used in Excel 2007 again, the problem arises. I have to then revert to the backed up original '07 file before it was sent in the email.

Basically, this spreadsheet is used to send text messages to numbers in certain columns. If the relevant cell is empty, this error handler kicks in to collect a number from the user.

Code:

Please Login or Register  to view this content.

Is there a code solution with the whole Excel 2007/2010 problem?

Any help you could give would be amazing.
thanks!

Have you tried with the simple Inputbox alone instead of the Application.Inputbox?

You are using:

Please Login or Register  to view this content.

and then

Please Login or Register  to view this content.

I think you meant to use a worksheet not a button since buttons don't have ranges.

On the contrary, you need another With... End with and tell it which sheet to look into for this range.

Or you can hard code the sheet name before every .Range.

Buttons don't have a range property and that's why when you ask vba to look at

ActiveSheet.Buttons(MyButton).Range it will give you an error.

If you could post a sample workbook, I can have a look.

Hi,

OK. Got it working now (on my system at least ). I also made some adjustments to streamline things a bit. Let me know if you need anything else.

abousetta

Please Login or Register  to view this content.

I reckon your problem is down to the button moving slightly so that the topleftcell and bottomrightcell are no longer the same. when that happens you are trying to compare an array of values to 0 which won't work. do you really need to refer to bottomrightcell as well? I'd rather replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

or use the bottomrightcell if you prefer. better yet would be to not use buttons at all and use a worksheet event such as selectionchange or a double-/right-click

Try this:

Please Login or Register  to view this content.

Personally I like using Select Case instead of multiple If statements but you can do it either way. In all cases, should be working now. My other advice to you is to change your programming style by removing "On Error GoTo" and rather specify what the error would look like and quantify it like I did in this example. That way other errors can be captured. Right now all errors will assume that the number is #NA, but it could be something completely different (e.g. different sheet names, etc.).

Let me know if you have any questions.

I would suggest something like

Please Login or Register  to view this content.

this is not tested though!

abousetta,
your error handler will not work if an error occurs within it or occurs after the goto statement. you can only reset an error handler with a resume statement or by exiting the routine.

HI Joseph,

My point exactly. I was attempting to provide a well-rounded solution to error trapping but rather trap only errors in column with #NAs. I agree that this is not optimal and needs a more rounded approach.

abousetta

P.S. Error type 13 with your posted code

how is that your point? if you have two errors of any kind, your error handler won't work because of the goto statement. it needs to be a resume statement.

ok commented out the Outlook bit as can't test that on Mac and see problem. small tweak

Please Login or Register  to view this content.

no point at all

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

you can use iserror to test for an error in the same row in column O or use Application.Worksheetfunction.Countif(Range("O:O"), "#REF") to test for a ref error anywhere in the column

@abousetta
I must apologize to you - just reread my last post and I can see that it would seem very rude. that was not my intention in any way and I'm very sorry for that. I just meant that I didn't understand what you were saying. Sorry again for any offense.

No worries... I will have a look again when my mind clears...