Limit text input in Excel 2003

I have seen a lot of posts talking about how to "limit" the text input
of a cell to a certain number of characters, but using the data
validation option does not stop the user from entering more than a
defined number, it just warns that it is invalid.

What can I do to make typing more than 36 characters impossible? I
just want the input to stop after 36, and if data is pasted into the
cell, I would want it cut off at 36.

Thanks for any help you can give.

-Erich
0
agent37 (1)
11/15/2004 8:38:11 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
674 Views

Similar Articles

[PageSpeed] 10

Hi
AFAIK this is not possible in a cell direct as a macro can't run while
you're in Edit mode

--
Regards
Frank Kabel
Frankfurt, Germany

"Agent37" <agent37@gmail.com> schrieb im Newsbeitrag
news:2506eada.0411151238.5197ba19@posting.google.com...
> I have seen a lot of posts talking about how to "limit" the text
input
> of a cell to a certain number of characters, but using the data
> validation option does not stop the user from entering more than a
> defined number, it just warns that it is invalid.
>
> What can I do to make typing more than 36 characters impossible? I
> just want the input to stop after 36, and if data is pasted into the
> cell, I would want it cut off at 36.
>
> Thanks for any help you can give.
>
> -Erich

0
frank.kabel (11126)
11/15/2004 8:49:07 PM
Data validation option DOES stop the user from entering more than a defined 
number and it DOESN'T.

It DOES if two conditions concur:
1. within the Validation dialog window you go to tab 'Error Alert', check 
'Show error alert after...' box and select Stop from the 'Style' dropdown.
2. the user manually enters the value or a formula returning that value into 
each individual cell.

It DOESN'T if the user copy/paste the value into the cell, copies values or 
formulas by dragging the little + in the bottom-right corner of the cell, or 
enters the value via VBA.

To ensure there are not more than 36 characters in a cell you could use a 
combination of the Validation option and the below code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRng As Range
    Set MyRng = Range("A1")
    If MyRng.Characters.Count > 36 Then
        MyRng = Left(MyRng, 36)
        MsgBox "Your text is too long and" & Chr(13) _
            & "will be cut to 36 charachters."
    End If
End Sub


KL

"Agent37" <agent37@gmail.com> wrote in message 
news:2506eada.0411151238.5197ba19@posting.google.com...
>I have seen a lot of posts talking about how to "limit" the text input
> of a cell to a certain number of characters, but using the data
> validation option does not stop the user from entering more than a
> defined number, it just warns that it is invalid.
>
> What can I do to make typing more than 36 characters impossible? I
> just want the input to stop after 36, and if data is pasted into the
> cell, I would want it cut off at 36.
>
> Thanks for any help you can give.
>
> -Erich 


0
dropspam (2)
11/15/2004 9:12:01 PM
Reply:

Similar Artilces:

File Size Limit??
Hi, Can anyone tell me if there is a limit to the file size of an Excel 2000 document. We've got a guy here who has a 63Mb spreadsheet, and keeps wondering why he's getting "Out of memory" messages every time he opens it. I've put an extra 512Mb in his PC, but he's still getting lock-ups. He's convinced that there are no limits to the size of the file, but I'm pretty certain that there is... Anyone help? Thanks, Jon. The limit is memory. -- Don Guillett SalesAid Software donaldb@281.com "Jon" <anonymous@discussions.microsoft.com> ...

Dollar text
Hi I do my proposal in Excel and I'm wondering if I can format a cell to convert dollar amounts $100.00 to One Hundred Dollars and 00/100 like when I write a Check? You might look at this article: http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 -- Jim "Plumb34" <Plumb34@discussions.microsoft.com> wrote in message news:DE4A7F72-B1BE-4AA1-95BE-630B662951A7@microsoft.com... | Hi | I do my proposal in Excel and I'm wondering if I can format a cell to | convert dollar amounts $100.00 to One Hundred Dollars and 00/100 like when I | write a Check? | |...

Publisher 2003 ....
Hello I just upgraded from Pub 2002 to Pub 2003 running Win XP. When I try to insert a clip art into my publications I cannot see the preview of the clip like I used to in Pub 2002, it just shows a file name (doglx...). Can this be modified to show the preview ? Thank you tony.vescio@bellnet.ca Tony Vescio wrote: > Hello I just upgraded from Pub 2002 to Pub 2003 running Win XP. When > I try to insert a clip art into my publications I cannot see the > preview of the clip like I used to in Pub 2002, it just shows a file > name (doglx...). Can this be modified to show the preview...

Excel 2007 unrecognized file format in Excel 2003
2007 & 2003 Note to the file - so to speak: Even if one changes Excel 2007's default file SaveAs format, when 2007 closes, it saves files like Personal.xls as a 2007 file format without the new extension .xlsx My point, I still must save files in 2003 for other's who do not have 2007. If I copy Personal.xls in the Excel StartUp Directory to a 2003 platform, Excel 2003 does not recognize the file format. (The file size is about 50% of the original 2003 file size - which is a strong hint that the file is in the new XML format) Therefore, I created a macro to save Personal.xls i...

Displaying MS Excel Chart control in ASP.NET Application
Hi, We are working on an ASP.NET (VB.NET) application. In one of th screens of our application, we need to show a graph and for this we ar using MS Excel Graph control. We have the graph prepared in Excel. Th application would feed in certain values into certain cells of th excel file and the graph would be drawn by the graph control based o these cells. We need some help on how exactly to display the excel graph contro directly on the screen of ASP.NET application. Thanks for the help oursm -- oursmp ...

Limits
I am doing a fairly easy programme on excel. It is a catchment runoff, and evaporation calculator. But i would like to place a limit on a cell, and have that exess go into another column, is this possible? EG 75 rainfall, soil can hold only 10mm more. SO 10mm INFILTRATES, ie that cell should have a limit of the max soil moisutre, the other 65mm RUNOFF's and thats a different column. ANYBODY PLEASEEEEEEEEEE!!!! lol You need a Workbook_Change event macro for that. The code you would write in that macro should first check that the change occurred in a cell within the range you want. ...

access2007 option group dotted line box around text
I have an access 2007 application with a form with a unbound option box with three option buttons and labels. When I select them sometimes the dotted box shows around 2 of the labels. The options have values 1, 2 and 3. Not sure why it is not just showing the dotted line box around the selected option? Any tips to fix it? Mark see my previous post for suggestions and resolution. I accidentally thought a deleted this one and it didn't post. Mark "Mark Andrews" <mandrewsNOSPAM@rptsoftware.com> wrote in message news:Okuv5ohaKHA.5608@TK2MSFTNGP05....

Excel #68
Can anybody please advise, how would I show a popup warning box in an Excel 2000 spreadsheet, if the totals cell reaches a particular figure, this box would also show a message. I would appreciate any advice. Regards, Belinda One way: Assume the total cell is A21, and your "particular figure" is 100. Select your input cells to the total cell (e.g., A1:A20). Choose Data/Validation, select Custom from the dropdown, and enter =$A$21<=100 Select the Error Message tab and enter your message. In article <ccobbp$9hn$1@hercules.btinternet.com>, "Belind...

Text box format
Hello, today I ask my first question in this forum *flourish* Can I "paste" the format of a text box to another in a similiar way as I can do with text formats ? I only found out, that I can set a standard for new text boxes. But I need more. *g* -- Andrea Dauwen-Ritter Marketing Support Germany How about you tell us which version of Publisher you are using!!! A baptism in fire!!! "°°MS-Publisher°°" wrote: > How about you tell us which version of Publisher you are using!!! > > A baptism in fire!!! *crawling out of the smoke with brai...

2008 R2 dpm 2007 setup: SIS-Limited not found?
I'm trying to run the command line to install the SIS component via ocsetup.exe SIS-Limited /quiet /norestart but it tells me it cant find this SIS-Limited component (on 2008 r2 x64).. Any ideas what i need to do to get past this? Thanks It turns out you just need to add the file services role in r2, dont need the manual command.. "markm75g" wrote: > I'm trying to run the command line to install the SIS component via > ocsetup.exe SIS-Limited /quiet /norestart > > but it tells me it cant find this SIS-Limited component (on 2008 r2 x64).. ...

Problem using Match on columns of numbers with leading zeroes formatted as text ???
In my test run, I was using a column of first names in workbook A to check and flag their presence in workbook B, The code given to me by Marcus here worked fine. No problem at all. .. When I switched to the real problem by replacing my columns of data with numbers with leading zeroes, the columns being already formatted as text, the program did not find Matches. .. In both workbooks columns of data, cells have an automatic Excel comment that says : "The number in this cell is formatted as text or preceded by an apostrophe". .. What am I missing here ? Is there a spe...

Help! IF function is too limited
Hi! I've created a drop-down list of cities in a cell, under which there are two more cells to be filled out with the address and zip codes corresponding to each of the cities. How can I do this, knowing that the original list is a three-column list made of city-address-zip? -- Ringo ------------------------------------------------------------------------ Ringo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27502 View this thread: http://www.excelforum.com/showthread.php?threadid=470178 Use VLOOKUP into the original; list using the DV value =VLOOKUP(B...

Q: Rules limit
There is a limit for Exchange Rules on a 32K-size (KB147298) Whether there is a way to change this limit for single users? For me already three users have addressed with such requests :- Thanks Alexander Kenin This is not a configurable limit. It's limited by the size of one RPC packet. "Kenin Alexander" <anonymous@discussions.microsoft.com> wrote in message news:80981636-20EB-4C64-B514-F9BCA635AFE7@microsoft.com... > There is a limit for Exchange Rules on a 32K-size (KB147298). > Whether there is a way to change this limit for single users? > For me already thre...

Hyperlinks in Outlook 2003
I have an ongoing problem with Outlook. Whenever I click on a hyperlink in the preview pane of Outlook, a browser opens as well as a link browser locate window. I have adjusted the settings in the File Type options, but they always revert back to the previous settings. I have also tried the "regsvr32 Urlmon.dll" command and that doesn't work after a reboot. I do have SP2 installed, but I don't know if that would be a factor in this. Thanks. ...

Excel to word.
Can anyone help.....I want to create a Invioce within word that would calculate above fields and and add Tax and finally give the gross figure. Same as you would achieve withon Excel. I do not want toinsert a table as I feel this would be confusing to the user. Is there a field that I can insert that would do the calculation for me in the same way I would do in Excel. Regards Cheryl Hi MS Word does support some calculated fiels but I'd suggest you post this question to the MS Word newsgroup :-) -- Regards Frank Kabel Frankfurt, Germany <anonymous@discussions.microsoft.com&g...

Excel 2007 encryption type?
What is the default encruption type for Excel 2007? Excel 2003 and before had and advanced option that would allow the user to specify an encryption type. How do you get to this in Excel 2007 when you are saving files in the legacy 2003 file format. Thanks, Will I don't know but I'd love to know. Can't believe they took away the menu's in Office 2007. > On Tuesday, April 10, 2007 12:19 AM Will Fleenor wrote: > What is the default encruption type for Excel 2007? > > Excel 2003 and before had and advanced option that would allow the user to > specify an...

Publisher 2003. gradient fills
I have a text box and an object shape that I would like to fill with a gradient blend from a solid color to completely transparent. It looks great on the screen but the transparent part is black. Marshams wrote: > I have a text box and an object shape that I would like to fill with a > gradient blend from a solid color to completely transparent. It > looks great on the screen but the transparent part is black. If you're thinking of printing this, don't bother - you cannot print gradients correctly with 2003, it's a known bug and, whether it will be fixed with the a...

Modify Callout Written in VB using VS 2003 after upgrade to CRM 4.
Hi, We've recently upgraded to CRM 4.0 with no issues. After ensuring the Microsoft.Crm.Platform.Callout.Base.dll was available in the GAC of the CRM server the callouts functioned as they should. However, I found a bug in one of the callouts so want to alter the assembly to rectify the bug. I've already recreated the callouts using plugins which work fine but I want to alter the callout as well because we have clients that will soon upgrade to CRM 4.0 who have callouts and don't want the added expense of migrating these to plugins. They will want changes made to the ca...

Hide a text box
On sheet 3 of my workbook I have created a button (button 62) and a text box (textbox 63). What I want to do is use the button to toggle the text box on and off. By that I want to be able to show the box or hide the box. I have tried to copy code from the MS Visual Basic Help but it did not work, basically because I have no idea what I am doing. Can someone lease help? Orf Bartrop Under my Excel - 2000 I don't think you can hide a text box But I hope Im wrong it would be a good idea Steve On Wed, 09 Aug 2006 05:45:55 +0100, Orf Bartrop <orf@southcom.com.au> = wrote: > On...

Limiting Internet Email for Users
I want to stop users from sending Internet email from my Exchange 2000 server. I use it for a company wide mail server. Can anyone help me stop Internet Email. This might help: http://www.msexchange.org/tutorials/MF009.html -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Bob" <anonymous@discussions.microsoft.com> wrote in message news:14B7AF2C-73E3-4601-84F9-3F7B95043B72@microsoft.com... > I want to stop users from sending Internet email from my Exchange 2000 server. I use it for a company wide mail server. Can anyone help me stop...

Text Form Fields
Not sure if I have posted this in the right area, but here goes. I am more familiar with excel so working with forms in word is new to me. I have been tasked with developing a number of forms at work in Word 2003 for posting on our website. A guy who left, and is now not contactable, developed a whole range of forms before he left. He made sure the forms could be filled in online by inserting text editable fields for client use (both the 'greyed out' section and dotted lines can be seen when viewing and completing online and the dotted lines auto-delete as text is inserte...

How can I download a PDF file to Excel
I want to add my lease agreements from a pdf file to Excel data files? How can this be done. Does anyone know. Please Help Thank you In Acrobat, have you tried selecting, cooying, and pasting into Excel? On Thu, 13 Jan 2005 14:13:05 -0800, ssp <ssp@discussions.microsoft.com> wrote: >I want to add my lease agreements from a pdf file to Excel data files? How >can this be done. Does anyone know. Please Help > >Thank you > Thank you - I will try to down load a lease agreement in that manner againb to see if it work - thank you "Myrna Larson" wrote: ...

Problem with the default text-framework
Hi, first sorry for my bad english. I have a problem with Publisher 2003: I have a text framework on the first side of my "empty" publication provided and in accordance with this text field specified the "default attitudes for new text fields". The new text field produced by application looks also completely good at first sight, but the text framework is diffrent. There is a border and the distance between the columns a wrong. Where ist the problem? Is this a bug or is the problem at me. Many thanks Oliver ...

2 Questions about Excel Charts
1) I have a list of 50 points (X, Y) that I want to plot. In a third column, I have the state corresponding to each point. How do I display these states on the chart (without manually entering each)? 2) I want to create a Chart where the user can input a real number (without switching to another sheet) that changes the chart (e.g., entering ALPHA for a chart plotting "SIN(ALPHA*X)"). Listboxes, spinners only work for discrete numbers, and I do not want to put the chart on an existing worksheet. How do I create a link from a sheet to an editable value on a Chart? Thank you...

Converting Excel to PDF
Hi there, I was hoping someone could help me...I have adobe & Microsoft office installed on my computer and I'm trying to set up adobe so that when I go and select print from the file menu in excel/word I can select adobe and then the document is converted to a pdf. However I do not know how to set up adobe so that it is one of my priting options - can anyone help me with this??! I look forward to hearing from someone who can help!! Cheers, Mel Do you have Acrobat or Acrobat Reader? Acrobat Reader only enables you to read pdf files. AFAICR when you install the full Acrobat produ...