Autofilter and No-Result

[Hans_Excel]

People,

A question from a VBA newbee:

I've developed an automated Excel Workbook in which my co-workers ca
determine the best suitable E-motor based on criteria given in b
themselfs. After they fill out a form with the needed parameters the
run a macro in which I autofilter the best suitable motors and displa
these results on a new worksheet.

However, it is possible that they fill in certain parameters and ther
are no results possible with the combinations of these parameters. In
case like that I want to display something like: "No results possible
please fill out the form again with different parameters" on the firs
line of the filtered table.

How do I realise something like that in my Macro?

Thanks in Advanc

 

[Tom Ogilvy]

It would probably be better to put up a message box in that instance.

msg = "No results possible, please fill out the form again with different
parameters"
msgbox msg

 

[Debra Dalgleish]

It may be less frustrating to users if you allow them them to autofilter
the list themselves. Then they can see the remaining items after each
criterion is applied.

If the sheet is protected programmatically,

http://www.contextures.com/xlautofilter03.html#Protect

they can filter the list, but won't be able to change the sheet's
contents.

 

[Hans_Excel]

@Tom
That would be an idea aswell, but how do I trigger the message
Something like a lookup on no entered data in the result table?

@Debra
The users are manipulating the outcome of the AutoFilter tabl
themselves by entering the parameters. The formulaes on which th
filter is based are always the same. So there is no need for them t
alter the Filter-Settings.

Thanks for replying you both!!

PS: Earlier I misstated the filter I use. I'm not using the Autofilte
but the Extended Filter implemented in a Macro with VBA

 

[Tom Ogilvy]

If you have a fixed area of data, you can use the macro that imposes the
filter to check the results:

cnt = 0
On error Resume Next
cnt = Range("A2:A200").specialcells(xlvisible).Count
On Error goto 0
if cnt = 0 then