score:2
Accepted answer
when a user fills in a cell that sends the range over the 1000 character limit, it will call application.undo
which should just remove the last text that they added.
option explicit
private sub worksheet_change(byval target as range)
if not intersect(target, range("a5:a30")) is nothing then
dim charcount as long
dim arrvalues as variant
arrvalues = range("a5:a30").value2
dim i as long
dim tempsplit as variant
dim j as long
for i = lbound(arrvalues) to ubound(arrvalues)
tempsplit = split(arrvalues(i, 1), " ")
for j = lbound(tempsplit) to ubound(tempsplit)
charcount = charcount + len(tempsplit(j))
next j
next i
if charcount > 1000 then
with application
.enableevents = false
.undo
.enableevents = true
end with
msgbox "adding this exceeds the 1000 character limit"
end if
end if
end sub
Source: stackoverflow.com
Related Query
- In Excel, limit the total number characters (like 1000) in a range of cells
- Using Microsoft Excel, How to check the total number of occurrences of a string in an excel column with many cells having comma separated strings?
- Count number of cells with specifc text and specific colour in excel range where the text is derived from another column
- How to write an Excel function to count the number of unique records in a specific range of cells
- Count the same number of characters between two cells in Excel
- In VBA find the max number of times a character appears in a single cell out of a range of cells
- How do I get the current cell number whilst looping through a range of cells (Excel VBA)
- Is there a limit to the amount of cells a range object can hold?
- Loop through a range of cells on cell change to show next number in the sequence as cell's new value
- How to create an excel function that outputs the content of a range of cells
- Selecting a range of cells and inserting a blank row for every number that the sequence of those cells is off by
- How to count the total number of specific words in a cell and do the same for other cells as well using VBA?
- count the selected range of cells after applying filter in excel vba
- Simply trying to paste the same string into a range of cells with Excel VBA
- How can I count the number of cells that are NOT formatted by conditional formatting using Excel VBA?
- Create a range based on the values of two non-adjacent cells in Excel
- How to Count Number of Unique Cells in Excel That Don't Contain Certain Characters
- Excel VBA, How to use the selection in a dropdown list on one sheet format(color) a range of cells on another sheet
- I have 3 excel formulas that I would like to fill ranges in a spreadsheet with, how can I make sure the cells change with the rows?
- Is there a limit to the number of cells called on in a source range?
- how to count of the number of filled cells in a column in an excel sheet
- To create a dynamic interactive range of cells that give the desired output of number of hours
- Counting the number of numbers in a range of cells
- Excel 2007 VBA Get the row number of an active cell relative to its position in a range
- How do I get a range of cells with an Excel VBA function to calculate individually and not all display the same value?
- Excel macro for selecting only half of the total number of rows in a worksheet
- Excel VBA code to copy a range of cells in a row and paste in a different sheet but same row/range, when the value on 1st cell changes
- Copy range of cells from Excel as picture and add text in the email body
- Excel VB - Copy/Paste cells: How can I increment the range of to/from cells
- I need to delete the last 7 characters of a specific range of cells that are already selected
More Query from same tag
- Replace "<=" with a drop down list value
- Userform VBA InkPicture input into sheet (specific cell) as image user signature
- Excel 2013 After Insert Data in 1 column another column from same sheet needs updated using macro
- Adding 1 to existing number in a cell based on value of another cell
- Excel/VBA - Detect if server is reachable
- Combine multiple workbooks to one workbook
- Overwrite data being sent to table if it exists already?
- VBA Exce IF statement for file save under custom name
- Return an Array of Only Numbers From a Range With Mixed Datatypes
- Error handling - convert #NUM to zero
- Trouble with syntax in VBA for SQL query
- Creating Primary Key across Multiple Access Tables
- Excel VBA - my VBA Code got messed up: why the spanish characters (á,é,í,ó,ú,ñ,¡,¿) mutated?
- Update hyperlinks with an excel Macro
- How to Copy/Paste Sheet Data Without Including ActiveXButtons
- calculate count of certain cells using vba in excel
- Excel run-time error when copying all worksheets into a new workbook
- VBA editor auto-deletes spaces at the ends of lines
- How to add a comment using comment boxes in Access?
- How to get filtered data as rowsource of multicolumn listbox?
- Don't let error occur when trying to open a file using vba
- Excel VBA for pivot Value using xlValueIsBetween
- Excel v.16 VBA SeleniumBasic array.length equivalent in Selenium
- vba access - Check if excel file is open
- Loop in an update SQL Server query sent by MS Access vba
- VBA actions based on cell content
- In VBA, how can I set an Array to Empty?
- Replace in SQL in VBA
- How to fix a cell that is not automatically updating due to a macro
- Macro to Run Custom Sql Query in Access off Data in Table?