Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
limit character length when copying or typing
I am creating a template with multiple columns that will be populated by
others. Some of these columns have specific character limitations--in these cases I'd like to prevent them from going over the character limit. I've considered using validation, but there is an excellent chance that some of the columns will be populated by copying/pasting, in which case validation doesn't help. I thought vba might be the best bet. What I'd love to have is this: If the person types or copies a value that exceeds the character limit, I would like a general message to display saying something like, "You have exceeded the character limit. Entries that exceed the limit will be highlighted and truncated." Then I would like that to happen--that is, to have their entry truncated to the max character limit, and that cell highlighted. I would like to have this work whether they pasted in one cell or multiple cells at a time. Does anyone have any ideas how I could accomplish this? Any guidance would be appreciated. Thanks, Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
limit character length when copying or typing
Hi,
See if this gets you started. Checks for data entered into column A HTH Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo wsexit Application.EnableEvents = False If Not Intersect(Target, Range("A:A")) Is Nothing Then Set rng = Target For Each cell In rng If Len(cell) 10 Then MsgBox "You have exceeded the character length. Cell will be truncated" cell.Value = Left(cell.Value, 10) Target.Interior.ColorIndex = 3 End If Next End If wsexit: Application.EnableEvents = True End Sub "goofy11" wrote: I am creating a template with multiple columns that will be populated by others. Some of these columns have specific character limitations--in these cases I'd like to prevent them from going over the character limit. I've considered using validation, but there is an excellent chance that some of the columns will be populated by copying/pasting, in which case validation doesn't help. I thought vba might be the best bet. What I'd love to have is this: If the person types or copies a value that exceeds the character limit, I would like a general message to display saying something like, "You have exceeded the character limit. Entries that exceed the limit will be highlighted and truncated." Then I would like that to happen--that is, to have their entry truncated to the max character limit, and that cell highlighted. I would like to have this work whether they pasted in one cell or multiple cells at a time. Does anyone have any ideas how I could accomplish this? Any guidance would be appreciated. Thanks, Jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
limit character length when copying or typing
Just a heads up,
Unless it was Toppers intent to color the entire target if any cell in the target exceeded the length limitation, then the following line Target.Interior.ColorIndex = 3 should be changed to Cell.Interior.ColorIndex = 3 to color only the offending cells within the cells changed But perhaps "Target" was his intent. also, if the only restriction was to column A, you could use set rng = Intersect(Target, Range("A:A")) rather than set rng = Target No intention to criticize Topper's Excellent suggestion, just some additional refinement suggestions. -- Regards, Tom Ogilvy "Toppers" wrote in message ... Hi, See if this gets you started. Checks for data entered into column A HTH Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo wsexit Application.EnableEvents = False If Not Intersect(Target, Range("A:A")) Is Nothing Then Set rng = Target For Each cell In rng If Len(cell) 10 Then MsgBox "You have exceeded the character length. Cell will be truncated" cell.Value = Left(cell.Value, 10) Target.Interior.ColorIndex = 3 End If Next End If wsexit: Application.EnableEvents = True End Sub "goofy11" wrote: I am creating a template with multiple columns that will be populated by others. Some of these columns have specific character limitations--in these cases I'd like to prevent them from going over the character limit. I've considered using validation, but there is an excellent chance that some of the columns will be populated by copying/pasting, in which case validation doesn't help. I thought vba might be the best bet. What I'd love to have is this: If the person types or copies a value that exceeds the character limit, I would like a general message to display saying something like, "You have exceeded the character limit. Entries that exceed the limit will be highlighted and truncated." Then I would like that to happen--that is, to have their entry truncated to the max character limit, and that cell highlighted. I would like to have this work whether they pasted in one cell or multiple cells at a time. Does anyone have any ideas how I could accomplish this? Any guidance would be appreciated. Thanks, Jeff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
limit character length when copying or typing
Thanks Tom, it should be Cell not Target. Your comments are always welcome
.... that's how I learn! "Tom Ogilvy" wrote: Just a heads up, Unless it was Toppers intent to color the entire target if any cell in the target exceeded the length limitation, then the following line Target.Interior.ColorIndex = 3 should be changed to Cell.Interior.ColorIndex = 3 to color only the offending cells within the cells changed But perhaps "Target" was his intent. also, if the only restriction was to column A, you could use set rng = Intersect(Target, Range("A:A")) rather than set rng = Target No intention to criticize Topper's Excellent suggestion, just some additional refinement suggestions. -- Regards, Tom Ogilvy "Toppers" wrote in message ... Hi, See if this gets you started. Checks for data entered into column A HTH Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo wsexit Application.EnableEvents = False If Not Intersect(Target, Range("A:A")) Is Nothing Then Set rng = Target For Each cell In rng If Len(cell) 10 Then MsgBox "You have exceeded the character length. Cell will be truncated" cell.Value = Left(cell.Value, 10) Target.Interior.ColorIndex = 3 End If Next End If wsexit: Application.EnableEvents = True End Sub "goofy11" wrote: I am creating a template with multiple columns that will be populated by others. Some of these columns have specific character limitations--in these cases I'd like to prevent them from going over the character limit. I've considered using validation, but there is an excellent chance that some of the columns will be populated by copying/pasting, in which case validation doesn't help. I thought vba might be the best bet. What I'd love to have is this: If the person types or copies a value that exceeds the character limit, I would like a general message to display saying something like, "You have exceeded the character limit. Entries that exceed the limit will be highlighted and truncated." Then I would like that to happen--that is, to have their entry truncated to the max character limit, and that cell highlighted. I would like to have this work whether they pasted in one cell or multiple cells at a time. Does anyone have any ideas how I could accomplish this? Any guidance would be appreciated. Thanks, Jeff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
limit character length when copying or typing
Right click on the sheet where you want the behavior. Select View Code.
This takes you to the sheet module. At the top of the resulting module In the left dropdown, select Worksheet and in the right dropdown select Change (not selection change) this will add the declaration for the change event. Private Sub Worksheet_Change(ByVal Target As Excel.Range) End Sub As you can see, this is the event that Topper used. It fires whenever a cell is edited or data is pasted. You can now delete the Change declaration that was entered by excel and paste in Topper's code. You don't have to do the dropdowns to paste in the code - I just when through that description as a form of instruction. For general information on events, see Chip Pearson's page on them http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "goofy11" wrote in message ... Toppers, thanks a bunch! This code looks to be exactly what I need. I tried setting this up but have run into a problem. I am a newbie to vba and I'm having trouble running this code. First of all, does this code automatically run each time changes are made to the worksheet, or does the user need to run the macro after they are done populating it? Second, I opened the Visual Basic Editor and copied your code (with the change made by Tom) into a new module in the workbook. When I attempt to run the code by going to ToolsMacrosMacros, it does not appear in "This Workbook" or "All Open Workbooks". When I reopen the VB Editor, it shows up in my module. With the VB Editor open with the module active, I can't even run it. When I press run, it asks me to create a new macro. This seems really weird, but I havn't even been able to test the code. Do you have any ideas? Jeff "Toppers" wrote: Thanks Tom, it should be Cell not Target. Your comments are always welcome ... that's how I learn! "Tom Ogilvy" wrote: Just a heads up, Unless it was Toppers intent to color the entire target if any cell in the target exceeded the length limitation, then the following line Target.Interior.ColorIndex = 3 should be changed to Cell.Interior.ColorIndex = 3 to color only the offending cells within the cells changed But perhaps "Target" was his intent. also, if the only restriction was to column A, you could use set rng = Intersect(Target, Range("A:A")) rather than set rng = Target No intention to criticize Topper's Excellent suggestion, just some additional refinement suggestions. -- Regards, Tom Ogilvy "Toppers" wrote in message ... Hi, See if this gets you started. Checks for data entered into column A HTH Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo wsexit Application.EnableEvents = False If Not Intersect(Target, Range("A:A")) Is Nothing Then Set rng = Target For Each cell In rng If Len(cell) 10 Then MsgBox "You have exceeded the character length. Cell will be truncated" cell.Value = Left(cell.Value, 10) Target.Interior.ColorIndex = 3 End If Next End If wsexit: Application.EnableEvents = True End Sub "goofy11" wrote: I am creating a template with multiple columns that will be populated by others. Some of these columns have specific character limitations--in these cases I'd like to prevent them from going over the character limit. I've considered using validation, but there is an excellent chance that some of the columns will be populated by copying/pasting, in which case validation doesn't help. I thought vba might be the best bet. What I'd love to have is this: If the person types or copies a value that exceeds the character limit, I would like a general message to display saying something like, "You have exceeded the character limit. Entries that exceed the limit will be highlighted and truncated." Then I would like that to happen--that is, to have their entry truncated to the max character limit, and that cell highlighted. I would like to have this work whether they pasted in one cell or multiple cells at a time. Does anyone have any ideas how I could accomplish this? Any guidance would be appreciated. Thanks, Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SELECTING FROM A DROP DOWN LIST BY TYPING FIRST CHARACTER | New Users to Excel | |||
typing a complement (i.e. notA) character in Excel | Excel Discussion (Misc queries) | |||
How do I limit the character length of a cell when typing | Excel Programming | |||
Typing a quote character Vs pasting a quote character= what happens to the resulting CSV? Interesting!! | Excel Programming | |||
Typing a quote character Vs pasting a quote character= whathappens to the resulting CSV? Interesting!! | Excel Programming |