 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2007Can I Restrict Characters From Being Entered?
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. 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 >> I'd like to prevent a user from entering commas in any of the cells of >> a spreadsheet. Is this possible? [quoted text clipped - 11 lines] > 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! > 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 >> 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 [quoted text clipped - 21 lines] > 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 :) > 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 [quoted text clipped - 3 lines] > 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 >> 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 [quoted text clipped - 10 lines] > 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 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. [quoted text clipped - 3 lines] >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. > On Sun, 3 Jun 2007 06:14:36 +0200, "moon" > [quoted text clipped - 9 lines] > > In this case one worksheet would suffice. 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 >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? >>> 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 [quoted text clipped - 10 lines] >> The first question, what if macros are disabled, is tougher and it's >> definitely something that I need to shine a light on. 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 >>You can prevent commas from being entered by using data validation and/or >>you can use AutoCorrect to change commas to tildes. [quoted text clipped - 21 lines] >>> The first question, what if macros are disabled, is tougher and it's >>> definitely something that I need to shine a light on. >This will work for TEXT entries only: > [quoted text clipped - 7 lines] > >Biff 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? >>>You can prevent commas from being entered by using data validation and/or >>>you can use AutoCorrect to change commas to tildes. [quoted text clipped - 21 lines] >>>> The first question, what if macros are disabled, is tougher and it's >>>> definitely something that I need to shine a light on. 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 >>This will work for TEXT entries only: >> [quoted text clipped - 44 lines] >>>>> The first question, what if macros are disabled, is tougher and it's >>>>> definitely something that I need to shine a light on. 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 > Ooops! > [quoted text clipped - 62 lines] >>>>>> The first question, what if macros are disabled, is tougher and it's >>>>>> definitely something that I need to shine a light on. >P.S. > [quoted text clipped - 5 lines] > >Biff 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. >> Ooops! >> [quoted text clipped - 62 lines] >>>>>>> The first question, what if macros are disabled, is tougher and it's >>>>>>> definitely something that I need to shine a light on. 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 >>P.S. >> [quoted text clipped - 89 lines] >>>>>>>> it's >>>>>>>> definitely something that I need to shine a light on. >Here's a good listing of books: > [quoted text clipped - 6 lines] > >Biff 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. >>>P.S. >>> [quoted text clipped - 89 lines] >>>>>>>>> it's >>>>>>>>> definitely something that I need to shine a light on. I think Biff put a ) in the wrong spot. I'd try: =AND(ISTEXT(A1),LEN(A1)<=12,ISERROR(FIND(",",A1))) Barney, Fyfe wrote: > >This will work for TEXT entries only: > > [quoted text clipped - 41 lines] > >>>> The first question, what if macros are disabled, is tougher and it's > >>>> definitely something that I need to shine a light on.  SignatureDave Peterson
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.