Limit numbers and letters in a cell

**roma83** · 02-15-2018, 03:24 AM

Dear all

I would like a vba code that sets a rule in a specified column/rows. Example from A300 to A500

The column must have 7 digits and a letter from A, G, F, L and M at end. Total 8. Example 1234567M or 7845123G

Could you please help.

Thanking you in anticipation

**Kaper** · 02-15-2018, 04:59 AM

In sheet code write event handler for Change event - for instance:

Please Login or Register  to view this content.

and in standard module function which will test if given string follows your pattern (exactly 7 digits and selected uppercase letters), for instance using Regular Expressions:

Please Login or Register  to view this content.

See sample file

**roma83** · 02-15-2018, 07:34 AM

Originally Posted by Kaper

In sheet code write event handler for Change event - for instance:

Please Login or Register  to view this content.

and in standard module function which will test if given string follows your pattern (exactly 7 digits and selected uppercase letters), for instance using Regular Expressions:

Please Login or Register  to view this content.

See sample file

Thank you for your quick reply. I am getting error - sub or function not defined and line Private Sub Worksheet_Change(ByVal Target As Range) is being highlighted in yellow

Please Login or Register  to view this content.

**Kaper** · 02-15-2018, 07:51 AM

This is Change event ahndler and shall be defined in worksheet code (one you access by right-click on worksheet tab and Display Code).

Have you downloaded my sample attachment?

PS. See also our Rule 12 https://www.excelforum.com/forum-rul...rum-rules.html - it is really not necessary to cite post directly above :-)

**jindon** · 02-15-2018, 07:59 AM

Paste the code onto the sheet code module

Please Login or Register  to view this content.

**roma83** · 02-15-2018, 08:06 AM

Originally Posted by Kaper

This is Change event ahndler and shall be defined in worksheet code (one you access by right-click on worksheet tab and Display Code).

Have you downloaded my sample attachment?

PS. See also our Rule 12 https://www.excelforum.com/forum-rul...rum-rules.html - it is really not necessary to cite post directly above :-)

Yes I copied it and posted it in my file.

I will check the link provided

**roma83** · 02-15-2018, 08:07 AM

Originally Posted by jindon

Paste the code onto the sheet code module

Please Login or Register  to view this content.

This vba code is working great.

....Is there a way to get an error message instead of it removing cell contents please?

**jindon** · 02-15-2018, 08:14 AM

MsgBox and then what do you want to do?

Please Login or Register  to view this content.

**Kaper** · 02-15-2018, 08:31 AM

Originally Posted by roma83

Yes I copied it and posted it in my file.

Woulkd be nice if you post on the forum as attachment:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**roma83** · 02-15-2018, 08:37 AM

Originally Posted by jindon

MsgBox and then what do you want to do?

Please Login or Register  to view this content.

This is working as well. Many Many thanks.

Sorry for this...I did not explain myself clearly as i am totally new to vba.

Apart from the error I would also want it to remove the content after I click ok

Again ...appolgies

**jindon** · 02-15-2018, 08:41 AM

Then Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**roma83** · 02-15-2018, 09:04 AM

Originally Posted by Kaper

Woulkd be nice if you post on the forum as attachment:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I am attaching an example

Limit numbers and letters in a cell

LinkBack

Thread Tools

Rate This Thread

Display

Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Re: Limit numbers and letters in a cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] count if x numbers present in cell with letters and numbers

Extracting Key words out of a cell with numbers, letters and letter and numbers together

[SOLVED] i have a data with numbers with letters in single cell , i need to split numbers and names

[SOLVED] Cell containing letters and numbers.

Excel 2007 : pull letters out of cell with letters & numbers

extracting numbers from a cell with letters and numbers

HOW CAN I LIMIT QUTITY OF LETTERS IN A CELL?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions