Limit character type and length in cell possible?

**Fidd$** · 06-30-2010, 05:58 AM

Hi Folks,

I am wondering if it's possible to limit a cell's input so that it follows a certain format, the format being 1 letter, 2 numbers, 1 letter and 4 numbers.

So, for example the cell value should look like this: 'A11A1111'.

I already know you can limit the length of the input, which is halfway there, but something to limit the user to only input the value in the above format would be excellent.
Pretty certain VBA is needed here if it can be done so just to let you know I'm not afraid!

Cheers,
Tony

**romperstomper** · 06-30-2010, 06:14 AM

You could use a custom data validation formula:
=AND(LEN(A1)=8,ISERROR(-LEFT(A1)),ISNUMBER(-MID(A1,2,2)),ISERROR(-MID(A1,4,1)),ISNUMBER(-MID(A1,5,4)))
for example.

**StephenR** · 06-30-2010, 06:17 AM

One approach, in the sheet module, will activate when any cell is changed.

Please Login or Register  to view this content.

**Fidd$** · 06-30-2010, 06:24 AM

Hi Romper,

That's excellent thanks for the quick response!
What would be the VBA equivalent?

Just thinking ahead here, the workbook could potentially hold up to 40 odd thousand rows off data and there will be other formulas and formatting in the book. Just trying to keep the file size as low as possible.

Cheers,
Tony

(straight after posting this, StephenR came up with the above response!)

STephenR,

Thanks! How would I set the range? As in I only want the code to fire in certain columns and not in others?

Cheers for the help guys!

**venkat1926** · 06-30-2010, 07:03 AM

open a new workbook and expriment this

rightclick sheet tab and click view code there you copy this evnet code

Please Login or Register  to view this content.

the only problem when you enter something in any cell this event code runs. to restrict this only some range or column you can add a condition in the beginning.

your comments please

**StephenR** · 06-30-2010, 07:07 AM

Tony - if you add this line before the current If, it will only trigger when a cell in A1:A10 is changed.

Please Login or Register  to view this content.

**Fidd$** · 06-30-2010, 07:27 AM

Thanks for the amazing help all of you.

Venkat, your code does exactly as what StephenR's does but added 'valid' messagebox (which isn't needed really, it's either going to be not valid or valid) and Stpehen's code is a bit more compact. Thanks a lot for the help though, it should come in handy some other time!

StephenR, cheers for the extra line there, I was looking at previous workbooks I've created that had that exact line in it but again, you're quicker! I knew it had something to do with the 'Intersect' bit but wasn't sure what the rest of it should look like, I am still very much a VBA nubbin

Thanks a lot for all the help and hints everyone!

Tony

**Fidd$** · 06-30-2010, 09:12 AM

I think I may have been a tad hasty in tagging this 'Solved'

I'm trying to have the above code accommodate another column with a different format e.g: '12A/12345678' but I can't seem to get it to work.

I thought about setting the ranges with names and the respective format they should be in but then it stops everything from working

As mentioned I am VBA nubbin but not scared to learn!

Cheers,
Tony

**Fidd$** · 06-30-2010, 09:57 AM

Just checking to see if anyone has an answer to the above

Thanks,

**romperstomper** · 06-30-2010, 09:59 AM

Which columns are you monitoring for which criteria?

**Fidd$** · 06-30-2010, 10:14 AM

Hi Romper,

Ok, I probably didn't think about this enough before I posted the original question - too eager!
Full explanation below:

I've got 7 columns, A:I.

Columns B and C are freeform, anything goes into these babies
Column F is a date field (simple enough)
Columns A,C,E and G are where all the action's taking place

Columns A and G needs to be like 'THSF12345' where the 'THSF' part never changes (only difference in the 2 columns is that in G 'it's 'THFB12345'
Column C needs to be like 'A11B1234' where both letter and numbers will change (answered originally in this thread)
Column E needs to be like '11A/12345678' where both numbers and the letter will change and the important part is the '/', this doesn't change.

I tried to do it on my own and of course getting nowhere!

I appreciate that my original post did not encompass everything I wanted so apologies.....I wanted to give it a try on my own first before asking for the rest.

Thanks again

**snb** · 06-30-2010, 10:19 AM

Please Login or Register  to view this content.

**StephenR** · 06-30-2010, 10:26 AM

Scratch this. I can't read.

Please Login or Register  to view this content.

I think this covers everything.

**romperstomper** · 06-30-2010, 10:27 AM

See if this does what you need:

Please Login or Register  to view this content.

**Fidd$** · 06-30-2010, 10:30 AM

Originally Posted by StephenR

Scratch this. I can't read.

Or is it I who cannot explain in plain English???

SNB, what's the point in posting the exact same code as in previous posts? Thanks for the help anyway

**romperstomper** · 06-30-2010, 10:36 AM

Originally Posted by Fidd$

SNB, what's the point in posting the exact same code as in previous posts?

it's not actually the same...

**StephenR** · 06-30-2010, 10:38 AM

Or is it I who cannot explain in plain English???

No, it's me who can't read English (let alone write French).

**Fidd$** · 06-30-2010, 10:53 AM

Hi Romper,

I pasted the code in and it works but it fires in columns B,D and F as well.
I only want it to look at columns A, C, E, G with the criteria mentioned in my earlier post.

Any suggestions?

Thanks,
Tony

**Fidd$** · 06-30-2010, 10:56 AM

HAH! Nice spot Romper, ok so it's not EXACTLY the same.....my mistake....missed out the messagebox bit
But, otherwise....c'mon?

StephenR, I only know "Stewardess!!! Quest qui ces't??!!" (sorry, a Lee Evans thing lol)

**romperstomper** · 06-30-2010, 11:01 AM

Originally Posted by Fidd$

HAH! Nice spot Romper, ok so it's not EXACTLY the same.....my mistake....missed out the messagebox bit
But, otherwise....c'mon?

the pattern is different.

Had a numpty moment with earlier code (always a risk with hair code). Change the Intersect line to:

Please Login or Register  to view this content.

**Fidd$** · 06-30-2010, 11:11 AM

YES!!!!!!!!!!!!!!!!!!!!!!
Cool, thanks for all the help guys.

Just a few points:
Romper, what the hell is "hair code"? (some VBA term I'm not familiar with?)
SNB, profound apologies

Cheers,
Tony

**romperstomper** · 06-30-2010, 11:14 AM

"Hair code" is code that I make up off the top of my head.

It's closely related to "air code" which I pull out of thin air.
Both just mean that I have simply typed the code into a post without testing (which I do too often, I'm afraid).

**snb** · 06-30-2010, 11:23 AM

Just for polishing:

Please Login or Register  to view this content.

PS. This is definitely not exactly the same code (although the performance is)

**Fidd$** · 06-30-2010, 11:25 AM

Well, the fact you can pull any code from hair or air and it works without testing is just showing off really

Seriously though, appreciate the time spent on this and hope to one day be able to 'air code' as well as 'air guitar'!

SNB, cheers for the 'polished' version. Will try that too. And yes, it's definitely not exactly the same

Tony

**StephenR** · 06-30-2010, 11:26 AM

snb: very neat.

**romperstomper** · 06-30-2010, 11:29 AM

I think we'll agree to disagree on what 'polishing' is...

**Fidd$** · 06-30-2010, 11:38 AM

SNB, where in your code does it state on what columns to run the code?
I could see where it was going wrong in Romper's version before the change in the range was put in but not too sure where in yours it differentiates what columns to look at?

Please excuse the questions, I just want to understand what each part does so that it will help me get a grip on VBA.

**snb** · 06-30-2010, 11:50 AM

It takes two steps to tango...

First is the target in columns A...G ?

Please Login or Register  to view this content.

Second: is the columnumber odd or even ?
If it's even then skip the remaining code.

Please Login or Register  to view this content.

**Fidd$** · 06-30-2010, 11:58 AM

How clever! Didn't even think for a second there that the columns I needed to look at coincided with odd numbers!

Well done m8 and cheers for the help.

I think that it's time now to mark this thread solved lol

Tony

**snb** · 07-01-2010, 04:31 AM

Please Login or Register  to view this content.

Limit character type and length in cell possible?

LinkBack

Thread Tools

Rate This Thread

Display

Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Re: Limit character type and length in cell possible?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions