Can I Restrict Characters From Being Entered?

  • Thread starter Thread starter Barney Fyfe
  • Start date Start date
B

Barney Fyfe

I'd like to prevent a user from entering commas in any of the cells of
a spreadsheet. Is this possible?

Thanks.
 
I'd like to prevent a user from entering commas in any of the cells of
a spreadsheet. Is this possible?

Thanks.
Click to expand...

Yes, you can trigger a comma with a Worksheet_Change-event.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim enteredValue As String
enteredValue = Target.Value
If InStr(1, enteredValue, Chr(44), vbTextCompare) > 0 Then
MsgBox "No comma's allowed!"
Target.Value = vbNullString
End If
End Sub
 
Yes, you can trigger a comma with a Worksheet_Change-event.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim enteredValue As String
enteredValue = Target.Value
If InStr(1, enteredValue, Chr(44), vbTextCompare) > 0 Then
MsgBox "No comma's allowed!"
Target.Value = vbNullString
End If
End Sub
Click to expand...

Moon thank you that worked like a champ. I'm VB illiterate but if I
wanted to perform a replace, how would I do that? Example, say every
time a comma is typed, it automatically gets changed to a tilde (~).

Thanks!
 
Moon thank you that worked like a champ. I'm VB illiterate but if I
wanted to perform a replace, how would I do that? Example, say every
time a comma is typed, it automatically gets changed to a tilde (~).

Thanks!
Click to expand...


Things change a little bit then. Let's see if the next one also will work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim enteredValue As String
Dim newValue As String
Dim c, l As Integer
enteredValue = Target.Value
If InStr(1, enteredValue, Chr(44), vbTextCompare) > 0 Then
l = Len(enteredValue)
For c = 1 To l Step 1
If Mid(enteredValue, c, 1) = Chr(44) Then
newValue = newValue & Chr(126)
Else
newValue = newValue & Mid(enteredValue, c, 1)
End If
Next c
Target.Value = newValue
End If
End Sub
 
Things change a little bit then. Let's see if the next one also will work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim enteredValue As String
Dim newValue As String
Dim c, l As Integer
enteredValue = Target.Value
If InStr(1, enteredValue, Chr(44), vbTextCompare) > 0 Then
l = Len(enteredValue)
For c = 1 To l Step 1
If Mid(enteredValue, c, 1) = Chr(44) Then
newValue = newValue & Chr(126)
Else
newValue = newValue & Mid(enteredValue, c, 1)
End If
Next c
Target.Value = newValue
End If
End Sub
Click to expand...

Worked exactly as desired. The last question I have is about security.
If the user opens the file and the macros are disabled, what options
do I have? Can I self sign it? If so, how do I lock down your code so
that it cannot be changed?

I appologize for taxing you with all of this but this is scope creep
for me :)
 
On Sun, 3 Jun 2007 02:24:25 +0200, "moon"

Worked exactly as desired. The last question I have is about security.
If the user opens the file and the macros are disabled, what options
do I have? Can I self sign it? If so, how do I lock down your code so
that it cannot be changed?

I appologize for taxing you with all of this but this is scope creep
for me :)
Click to expand...

No need to apologize, that's all okay.

By right clicking on the VBAProject, you can assign a password to the code,
so nobody can see it (where nobody = the average user).
The first question, what if macros are disabled, is tougher and it's
definitely something that I need to shine a light on.
 
You can prevent commas from being entered by using data validation and/or
you can use AutoCorrect to change commas to tildes.

Biff
 
T. Valko said:
You can prevent commas from being entered by using data validation and/or
you can use AutoCorrect to change commas to tildes.

Biff
Click to expand...
Yes, but now we're talking about one worksheet - in another worksheet
AutoCorrect might be a pain in the butt, because it's a global setting.
 
You can prevent commas from being entered by using data validation and/or
you can use AutoCorrect to change commas to tildes.

Biff
Click to expand...

I am using data validation to limit the number of characters in a cell
(for example text is less than or equal to 12). This is a canned
option. I guess I'd have to use a custom to not allow commas, and
field character limits if it is possible to have two conditions at
all. How do I write the custom formula for this?
 
Yes, but now we're talking about one worksheet - in another worksheet
AutoCorrect might be a pain in the butt, because it's a global setting.
Click to expand...

In this case one worksheet would suffice.
 
In this case one worksheet would suffice.
Click to expand...

I have a similar issue. I have a spreadsheet where the user enters
numbers (cells are formatted as , 00). I want to prevent the user
from using either a $ or a , as part of the number being entered.

I tried the event macro, but it works only if the input is formatted
as text. Is there a similar macro that can address my issue?

Thanks for any advice.

Crownman
 
This will work for TEXT entries only:

=AND(ISTEXT(A1),LEN(A1<=12),ISERROR(FIND(",",A1)))

The comma separator in a number is not recognized as a character, it's a
format thing.

1,000,000 will display in the cell but if you look at the formula bar you'll
see 1000000 which is the true underlying value of the cell.

Biff
 
This will work for TEXT entries only:

=AND(ISTEXT(A1),LEN(A1<=12),ISERROR(FIND(",",A1)))

The comma separator in a number is not recognized as a character, it's a
format thing.

1,000,000 will display in the cell but if you look at the formula bar you'll
see 1000000 which is the true underlying value of the cell.

Biff
Click to expand...

Biff,

This is working to an extent. I select column A and format column A as
text. I then select column A and go to data validation and enter the
formula you wrote above. It pops up an error when I type a comma in
any cell in column A, *but* it allows more than 12 valid characters.
Am I doing something wrong here?
 
Ooops!

My fault. The test for the LEN syntax is wrong.

Should be:

=AND(ISTEXT(A1),LEN(A1)<=12,ISERROR(FIND(",",A1)))

Also, if you preformat the cells as TEXT then this will now work on TEXT
numbers:

1,000

Will pop the invalid entry message.

Biff
 
I think Biff put a ) in the wrong spot.

I'd try:

=AND(ISTEXT(A1),LEN(A1)<=12,ISERROR(FIND(",",A1)))
 
P.S.

If you preformat the cells as TEXT then you don't need the ISTEXT test:

=AND(LEN(A1)<=12,ISERROR(FIND(",",A1)))

But, it might be a good idea to leave it in.............just in case!

Biff
 
P.S.

If you preformat the cells as TEXT then you don't need the ISTEXT test:

=AND(LEN(A1)<=12,ISERROR(FIND(",",A1)))

But, it might be a good idea to leave it in.............just in case!

Biff
Click to expand...

That did id Biff, thank you very much for the assistance with this.
Btw, is there a book (like a SAMS 10 minute deal) that would give me
guidance on how to write these formulas for Excel. I'd really like to
pick this up.

Thanks again to all who responded and offered insight.
 
Here's a good listing of books:

http://contextures.com/xlbooks.html

I think the best way to find a book is to actually go to a book store and
thumb through the books. Pick the one that covers the things you are most
interested in. One of these days I'd like to write my own book on advanced
formulas.

Biff
 
Here's a good listing of books:

http://contextures.com/xlbooks.html

I think the best way to find a book is to actually go to a book store and
thumb through the books. Pick the one that covers the things you are most
interested in. One of these days I'd like to write my own book on advanced
formulas.

Biff
Click to expand...

Thanks for that link. Unfortunately the brick and mortar book stores
around me aren't great at selection and I live in suburban NYC, so I
will rely on the website to pick one.
 
Back
Top