score:2
with ref to the query, workaround for the dv and change event is managed.
public strrange as string
public bcheck as boolean
private sub worksheet_change(byval target as range)
if bcheck then exit sub
msgbox "correct entry!"
strrange = target.address
bcheck = true
end sub
private sub worksheet_selectionchange(byval target as range)
if target.address <> strrange then bcheck = false
end sub
http://forum.chandoo.org/threads/multiple-worksheet-change-event-with-data-validation.32750
score:3
you have a very open-ended question...........not having the time to do a full whitepaper, here is a simple experiment. i use the event code:
private sub worksheet_change(byval target as range)
dim a1 as range, rint as range
set a1 = range("a1")
set rint = intersect(a1, target)
if rint is nothing then exit sub
msgbox a1.value
end sub
and in a1, i setup dv as follows:
if i use the drop-down, i get the value entered and i also get the msgbox
. however, if i click on the cell and type some junk what happens is:
- the dv alert occurs and i touch the cancel button
- i get 2
msgbox
occurrences, each with the original contents rather than the attempted junk !!
i have absolutely no idea why the event is raised since the cell is not actually changed, let alone why the event is raised twice !! it is almost as if the event is raised on junk entry, but the dv alarm has precedence, the dv reverse the entry and another event is raised, and finally both events get processed.
hopefully a person smarter than me will chime in.
Source: stackoverflow.com
Related Query
- Data Validation and Worksheet Change Event
- Worksheet Change event with data validation
- How to make Change event and data validation work when copying data in multiple cells in a column(M) at a time using Excel Macro
- Data validation on worksheet change vba
- Data validation and combo box in a cell - Workbook_SheetChange event not working
- VBA: Worksheet Change causes multiple copied and pasted data
- vba Change event when data validation selected
- Excel/VBA - worksheet change event copy/paste/clear data
- VBA Worksheet change event bypass?
- Extract Data from PDF and Add to Worksheet
- VBA Worksheet Change Event
- Worksheet change event for every sheet in the workbook
- VBA Copy Data from several Different workbook in a specific worksheet and then paste the data to a specific sheet in current Workbook
- Using VB/VBA to search Outlook messages and extract specific data into Excel worksheet
- Excel macro: Browse Excel file and use its worksheet data
- VBA Excel filter data and copy to another worksheet - Newbie alert
- vba - Handle worksheet change event before running macro
- Loop through textboxes on access form and change the data
- VBA Reset all Checkbox,ComboBox, Text box and Data validation Dropdown list
- Excel Macro: Copy data into new worksheet and sort base on date and random number
- Highlight with data validation and conditional formatting, but with optional data validation fields
- Change multiple named cells and ranges in a worksheet at once
- Macro for copying and pasting data to another worksheet
- Can a cell have both data validation and vlookup at the same time?
- How to change range in sub after copying and pasting data to new sheet
- Cell Value Change Event and Running a Continuous Macro
- Implementing a change event to check for changes to textbox values and enabling the "apply" button
- Bug on Worksheet Change event in Excel?
- Check if worksheet in workbook has specific name, and change it if it does
- Copying data from one worksheet and paste against relevant rows in another worksheet
More Query from same tag
- Alternative or more efficient method than referencing cells?
- Moving sheets with VBA codes and buttons to a new Workbook
- Refresh Internet Explorer on detection of Error 500
- Insert rows in a table based on number of rows from another worksheet
- Sort with more than 3 Key (7)
- Dynamically Generate HTML Table enbedded in VBA Email Generation
- Password protection worksheet Excel VBA
- Compile error: variable not defined (VB Adodb)
- Subscript out of range -- Unable to set open workbook as active workbook
- Excel VBA - SQL Error: ORA-00604: error occurred at recursive SQL level %s
- vba - loop through subdirectories to find and insert pic
- VBA Excel copy text file to worksheet
- Excel: How to get column value F at row-index X in other worksheet
- Program not responding -excel vba for loop
- labeling an area using 2 variables
- How to select a dropdown cell validation value
- excel - find multiple duplicated values with different arrangement
- Run-time error '1004': Method 'Range' of object '_Global' failed problem with (Range(ActiveCell).Offset(-10, 1).Select)
- Sorting multiple columns using VBA
- Excel VBA range.find doesn't return the first appearance?
- 102 files copy identical range from each to one worksheet
- Excel VBA: Array return of size 1 fills entire calling formula
- Using excel vba to copy specific file extensions to another folder using the FSO method
- Code to not allow users with greater versions of 07 MS Access
- How to loop through numerous sub folders and find if a cell value matches a folder name using excel VBA?
- Listbox Value is Null
- Excel vba code using ThisWorkbook.Sheets(1).Range not working, but Sheet1.Range works fine. Why?
- How to split a multivalue cells into multiple rows using a VBA?
- Finding a Range based on 2 criteria in Excel VBA
- Creating a backup protected with password