Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Finding duplicates in Change Event
I am trying to use VBA to check a new value in a cell against other
values in a column to alert the user as to whether it is a duplicate. I would like to do this using a Message Box. I already have code to check the length of the new value and would like code that does something similar. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim LRange As Range Set LRange = Range("B:B") If Len(Target) 27 Then MsgBox "The value you entered is greater than 27 characters" End If End Sub I am using Excel 97. Thanx, Alice. --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding duplicates in Change Event
Hi
one way: change your code as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim LRange As Range Set LRange = Range("B:B") If Len(Target) 27 Then MsgBox "The value you entered is greater than 27 characters" End If If Application.worksheetfunction.countif(LRange,targe t.value)1 then msgbox "Duplicate" end if End Sub Some note: - I would add some more error checking to your code (checking if column B is affected, that only one cell is changed, etc.) - You don't need VBA for this checking for duplicates and lenght. Why don't you use data validation. e.g. for duplicates enter the following validation formula in 'Data - Validation' for cell B1: =COUNTIF($B:$B,B1)=1 oor for testing the length: =len(B1)<=27 HTH Frank I am trying to use VBA to check a new value in a cell against other values in a column to alert the user as to whether it is a duplicate. I would like to do this using a Message Box. I already have code to check the length of the new value and would like code that does something similar. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim LRange As Range Set LRange = Range("B:B") If Len(Target) 27 Then MsgBox "The value you entered is greater than 27 characters" End If End Sub I am using Excel 97. Thanx, Alice. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Finding duplicates in Change Event
Thanx for that it worked a treat!
Although I could use the Validation tool within Excel I am happier having this code running in the background - I am supposed to be demonstrating the amazing capabilities of VBA (mainly so I can push an upgrade from 97!!) Thanx again. --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Finding duplicates in Change Event
Thanx for that it worked a treat!
Although I could use the Validation tool within Excel I am happie having this code running in the background - I am supposed to b demonstrating the amazing capabilities of VBA (mainly so I can push a upgrade from 97!!) Thanx again -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Finding duplicates in Change Event
Thanx for that it worked a treat!
Although I could use the Validation tool within Excel I am happier having this code running in the background - I am supposed to be demonstrating the amazing capabilities of VBA (mainly so I can push an upgrade from 97!!) Thanx again. o.k. thats a good reason :-) and thanks for the thanks Frank |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Finding duplicates in Change Event
Alice,
The amazing capabilities of VBA hardly warrant an upgrade from 97 as the improvements(!) are not exactly many or significant. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Alice " wrote in message ... Thanx for that it worked a treat! Although I could use the Validation tool within Excel I am happier having this code running in the background - I am supposed to be demonstrating the amazing capabilities of VBA (mainly so I can push an upgrade from 97!!) Thanx again. --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Finding duplicates in Change Event
Well, for VBA, the ability to use COM addins for 2000+ is a good reason
to upgrade (I just need to learn how to make use of this). In the workbook side, the major upgrade in 2000+ is pivot charts, but once I had that capability, I decided I didn't need it. Other than these, I can't think of a reason to upgrade, but then, it's still early in the day. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Bob Phillips wrote: Alice, The amazing capabilities of VBA hardly warrant an upgrade from 97 as the improvements(!) are not exactly many or significant. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding duplicates | Excel Discussion (Misc queries) | |||
Finding duplicates | Excel Discussion (Misc queries) | |||
Finding duplicates | Excel Worksheet Functions | |||
change event/after update event?? | Excel Programming | |||
Finding Duplicates | Excel Programming |