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

Thanks.

<Barney Fyfe> schreef in bericht
news:(E-Mail Removed)...
> I'd like to prevent a user from entering commas in any of the cells of
> a spreadsheet. Is this possible?
>
> Thanks.
>

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

On Sun, 3 Jun 2007 00:02:25 +0200, "moon"
<(E-Mail Removed)> wrote:

>
><Barney Fyfe> schreef in bericht
>news:(E-Mail Removed)...
>> I'd like to prevent a user from entering commas in any of the cells of
>> a spreadsheet. Is this possible?
>>
>> Thanks.
>>
>
>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
>
>
>

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!

<Barney Fyfe> schreef in bericht
news:(E-Mail Removed)...
> On Sun, 3 Jun 2007 00:02:25 +0200, "moon"
> <(E-Mail Removed)> wrote:

> 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!


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

On Sun, 3 Jun 2007 02:24:25 +0200, "moon"
<(E-Mail Removed)> wrote:

>
><Barney Fyfe> schreef in bericht
>news:(E-Mail Removed)...
>> On Sun, 3 Jun 2007 00:02:25 +0200, "moon"
>> <(E-Mail Removed)> wrote:
>
>> 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!
>
>
>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
>
>

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

<Barney Fife> schreef in bericht
news:(E-Mail Removed)...
> On Sun, 3 Jun 2007 02:24:25 +0200, "moon"
> <(E-Mail Removed)> wrote:
>
> 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

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

"moon" <(E-Mail Removed)> wrote in message
news:46620f7d$0$25480$(E-Mail Removed)...
>
> <Barney Fife> schreef in bericht
> news:(E-Mail Removed)...
>> On Sun, 3 Jun 2007 02:24:25 +0200, "moon"
>> <(E-Mail Removed)> wrote:
>>
>> 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
>
> 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.
>
>
>
>

"T. Valko" <(E-Mail Removed)> schreef in bericht
news:%(E-Mail Removed)...
> You can prevent commas from being entered by using data validation and/or
> you can use AutoCorrect to change commas to tildes.
>
> Biff
>
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.

On Sat, 2 Jun 2007 23:22:29 -0400, "T. Valko" <(E-Mail Removed)>
wrote:

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

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?



>"moon" <(E-Mail Removed)> wrote in message
>news:46620f7d$0$25480$(E-Mail Removed)...
>>
>> <Barney Fife> schreef in bericht
>> news:(E-Mail Removed)...
>>> On Sun, 3 Jun 2007 02:24:25 +0200, "moon"
>>> <(E-Mail Removed)> wrote:
>>>
>>> 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
>>
>> 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.
>>
>>
>>
>>
>

On Sun, 3 Jun 2007 06:14:36 +0200, "moon"
<(E-Mail Removed)> wrote:

>
>"T. Valko" <(E-Mail Removed)> schreef in bericht
>news:%(E-Mail Removed)...
>> You can prevent commas from being entered by using data validation and/or
>> you can use AutoCorrect to change commas to tildes.
>>
>> Biff
>>
>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.
>

In this case one worksheet would suffice.