Select case on range name

**mcinnes01** · 01-07-2011, 12:49 PM

Hi

I think I am being a bit daft probably because I've been staring at vba for too long today...
But I cant get my select case to work. basicially it just validates the data. When I run it nothing errors, but nothing happens either?

Please Login or Register  to view this content.

**royUK** · 01-07-2011, 12:55 PM

Try amending along these lines

Please Login or Register  to view this content.

**mcinnes01** · 01-07-2011, 01:03 PM

Ammended as suggested and still no joy

Please Login or Register  to view this content.

**royUK** · 01-07-2011, 01:13 PM

An example workbook would help. Are they all Named Ranges referring to single cells, if so you need to loop through the Names not the cells

Maybe

Please Login or Register  to view this content.

**mcinnes01** · 01-07-2011, 01:23 PM

Here is a very basic example of what I am trying to do. Essentially if the various criteria for each cell aren't met then the relevent msgbox should display

**royUK** · 01-07-2011, 01:30 PM

Slight variation on above

Please Login or Register  to view this content.

**mcinnes01** · 01-10-2011, 05:48 AM

Hi Roy thats great it worked a treat, I'm just having one problem in that I don't want the code below to run until non of the case match. Is there a way of saying "IF NO CASES MATCH THEN ..."

Please Login or Register  to view this content.

**mcinnes01** · 01-10-2011, 07:30 AM

Ok so I have found 2 problems and more or less solved 1 with the select case.

The problems are that it will go through every case and display every validation prompt that has a matching case.

The other problem is that I want it to only run the last validation step when all the others pass (ie don't match a case)

I had a play at just using a more simple approach of nested ifs, this is working how I want it other than it doesn't go all the way through validation if you pass the validation for RES then it stops and wont go on to validation REQ etc

This is my code

Please Login or Register  to view this content.

**mcinnes01** · 01-10-2011, 10:50 AM

Ok here is my problem...

Basically some cell entry variations have multiple validation reasons.

For example Cells PSC (salary scale) and PPT (salary point)

If both PSC and PPT are blank then they pass

If both PSC and PPT have a value they pass

If PSC has a value and PPT is blank msgbox (you must select a scale point)

If PSC is blank and PPT has a value msgbox (you must select a salary scale first)

This is my code for this particular item in the validation, the problem is, is that it will work if they are both blank and if they both have a value and if PPT has a value and PSC is blank

BUT*** it doesn't work is PSC has a value and PPT is blank.

How can I over come this, I have about 4 instances where this happens

Please Login or Register  to view this content.

**royUK** · 01-10-2011, 10:53 AM

Use Application.WorksheetFunction to check the number of empty cells.

**mcinnes01** · 01-10-2011, 11:26 AM

Hi Roy,

Thanks for the suggestion, I managed to use the worksheetfunction "AND" to allow the elseif to continue.

Please Login or Register  to view this content.

Select case on range name

LinkBack

Thread Tools

Rate This Thread

Display

Select case on range name

Re: Select case on range name

Re: Select case on range name

Re: Select case on range name

Re: Select case on range name

Re: Select case on range name

Re: Select case on range name

Re: Select case on range name

Re: Select case on range name

Re: Select case on range name

Re: Select case on range name

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions