Show Userform when cells value is #N/A

**adam2308** · 07-07-2011, 06:34 AM

I am looking to add some code to a module where is runs down column F (from F2 TO F100) and then down column G (G2 to G100) in Sheet2.

If it comes a cross a cell with the formula error #N/A then I want it to stop and display a userform (Userform1).

Can anyone please help?

Thanks,
Adam.

**mohd9876** · 07-07-2011, 06:44 AM

try this:

Please Login or Register  to view this content.

**adam2308** · 07-07-2011, 07:09 AM

Thanks mohd9876. I have added this code

Please Login or Register  to view this content.

to the worksheet that I want checking which brings up the userform when I select a cell with #N/A in but now how do I add some code to a module which loops through cells F2:F100 and G2:G100?

Thanks.

**mohd9876** · 07-07-2011, 07:29 AM

you can do something like this:

Please Login or Register  to view this content.

**royUK** · 07-07-2011, 07:32 AM

What's the purpose of the userform?

**adam2308** · 07-07-2011, 08:41 AM

Attached is an example of what I am trying to achieve.

A set of football fixtures are pasted in to the worksheet 'Tissue' (using the Sub Paste_Tissue) and then the worksheet 'Coupon' looks up the 'Tissue Name' in the 'Lookups' worksheet and returns 'Our Name'.

When the lookup formula returns #N/A then I want 'Userform1' to appear which the user will either select an existing 'Tissue Name' which will replace the unknown team in the 'Tissue' worksheet or the user could add the unknown team name as a new 'Tissue Name' in to the lookup table.

Hopefully this makes it more clear. Please ask if there is anything I am still unclear about.

**mohd9876** · 07-07-2011, 10:53 AM

try this:

Please Login or Register  to view this content.

**adam2308** · 07-08-2011, 10:05 AM

Thanks again, that seems to work better now.

Now, we have the form appearing when I want it to, I now need the form to wok as intended.

So, when the form apears I want the user to have two choices...
1) Change the unknown team on the 'Tissue' to one already in the database, i.e. the first unknown team it comes across in this example is "Stoke ITV & ESPN". The user could then choose the correct team name 'Stoke' from the Listbox. When the user presses the command button 'Select' then it should replace "Stoke ITV & ESPN" with "Stoke" on the 'Tissue' worksheet.

2) The other option is for the user to add a new team to the database on the 'Lookups' worksheet. The 'Tissue Name' textbox should be populated with team name as written on the tissue and the user then selects a team the 'Our Name' ComboBox. When the user clicks 'Add' then this should add the values of the textbox and Combox to the next available row at the bottom of column B on the 'Lookups' worksheet and then sort Column A by 'A-Z'.

Then the 'Paste Tissue' module carries on until all teams are valid with the database.

Hope all this makes sense, it does to me but then again I know what I want to do (just don't know how to!)

I have attached a better example of where I am upto. The worksheet 'Tissue Example' can be used to trial the 'Paste Tissue' macro with the Userform1. Because of the file size of the excel file I have had to zip the file, please lt me know if there is a problem accessing this as I have never zipped a file before so not sure if I have done anything wrong.

Thanks again.