Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation
Hi,
I'm looking for a bit of help, i would like to limit the max number of caracters a cell can contain to 15, yes you can do this in data validation but you can just paste something with greater than 15 caracters into the cell and it will accept it, it only seems to block typing, thus is there a way to maybe in an adjacent cell use the formula =len() and then using some automatic macro say if there is a cell in the adjacent column with a number over 15 bring up an error message? and get the user to change whats in the cell, I'm not very good at coding hence i'm sure theres a way but dont know it, thanks, Andy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation
Hi Momo,
If you are satisfied with an optical warning, you might want to define a conditional format: Select a cell, a range of cells, an entire row or column and then goto Format / Conditional Formatting, select under condition 1 "Formula Is", type in =LEN(INDEX($1:$65536,ROW(),COLUMN()))15 Push Format button then Pattern and select red, for example, then hit ok. HTH, Bernd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation
Maybe something like the next code can help:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$3" Then If Len(Target.Value) 15 Then Target.Value = Mid(Target.Value, 1, 15) MsgBox "The length of this cell is limited to 15 characters" End If End If End Sub The code is placed in the Worksheet_Change event and (automatically) executed when cell values are changed. In this example only the value of cell C3 is validated, you can modify the range (i.e. by the row and column of the Target object) as you like. If the cell content exceeds 15 characters, the value is limited to 15 positions and a message is shown. regards, Tieme "Momo" wrote: Hi, I'm looking for a bit of help, i would like to limit the max number of caracters a cell can contain to 15, yes you can do this in data validation but you can just paste something with greater than 15 caracters into the cell and it will accept it, it only seems to block typing, thus is there a way to maybe in an adjacent cell use the formula =len() and then using some automatic macro say if there is a cell in the adjacent column with a number over 15 bring up an error message? and get the user to change whats in the cell, I'm not very good at coding hence i'm sure theres a way but dont know it, thanks, Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |