The "=" character ban

[cgi_pro]

Hello

How do I NOT allow the user to type the equal sign as being the first character of his input in a certain cell?

For example:

How can I forbit the user to input such as: "=A1+A2"

I want him to put plain text or number, and not formula to avoid screwing my VBA code

[VBA Noob]

Try

=NOT(A1="=")

PS How many more have you got

VBA Noob

[cgi_pro]

what you're suggesting here is that if the cell only contained the "=" char, but what I'm saying here is that I don't want the user to put any formula such as:

=SUM()
=AND()
=OR()
.
.
.

So, how do I go about this matter?

Is there a function that checks the first char in a cell?

[VBA Noob]

Hi,

Don't think you can stop them entering a formula. You could maybe use a event procedure to convert the User enter to a value.

VBA Noob

[LittleEm]

You could use Data Validation to allow them to only enter a number, not sure how you can allow text as well though.

[vbace2]

Try this in data validation...

=NOT(LEFT(A1,1)="=")

[VBA Noob]

vbace2,

Doesn't stop formula's

Left function doesn't work until you hit ok and then it doesn't see the = when it's in a formula

VBA Noob

[vbace2]

You said you want the user to be able to add numbers or text, but not the equal sign. If numbers are entered, are the numbers going to be used in calculations?

You could format the cells as text, and then use validation and say =NOT(LEFT(A1,1)="=")

[cgi_pro]

VBA Noob is right, I tried your way vbace2 by entering in the validation of A1 what you suggested, then I tested it as follows:

I entered the "=" as the first char and it successfully restricted it

Then...

I entered the "=" as the first char and followed it with some testing string that doesn't refer to any function name such as "=testing" and it restricted it successfully too.


So far so good

However, when I entered the following in A1:

Please Login or Register  to view this content.

it didn't restrict it and it calculated the sum of both A3 & B3 and placed the result in A1

[protonLeah]

The following code seems to work; however, it gives the error message twice...

Option Explicit 
Public previouscell As String 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
        If previouscell > "" Then 
                If Range(previouscell).HasFormula = True Then 
                        Range(previouscell).Select 
                        MsgBox "formulas not allowed" 
                End If 
        End If 
        previouscell = Target.Address 
End Sub  

[cgi_pro]

protonLeah you're the man, thanks, your code worked just fine

thanks again and thans to all who put their effort to help me out finding the solution around this matter

[Maistrye]

You can probably get rid of the duplicate message by adding the following:

Please Login or Register  to view this content.

Scott