Event handling...

Alex · 01-29-2005, 02:06 PM

Excel Helper

I have a list of names in column A.

What i want is program whereby if I enter a new name at the bottom of the
column, if it is a duplicate (i.e. name appears elsehwere in list) then a
msgbox appears telling em it is a duplicate.

Now I can actauly ahcieve this in VBA but...

I have to actaully execute the macro maually.

How can i get the macro to run automatically i.e. I enter a new name, press
enter, and the macro runs without me clicking a separate button to run it of
pressing F5 etc.

I have tried...

Application.Volatile
Application. OnKey
Application. Onaction

How do I get Excel to run a macro once an event has occurred.

Any insights appreciated...

Alex

**MSP77079** · 01-29-2005, 02:15 PM

In VB Editor, double-click on the sheet you want so that you can view its code window.

Change selection in box on upper left of code window from (General) to Worksheet.

In the box at upper right of code window choose Selection Change.

In the subroutine, call your macro.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call myMacro
End Sub

Tom Ogilvy · 01-29-2005, 03:06 PM

Use the WorkSheet Change Event

see Chip Pearson's page on Events

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"Alex" <Alex@discussions.microsoft.com> wrote in message
news:F7AE225D-E3D1-4896-918F-523EA22BB4E4@microsoft.com...
> Excel Helper
>
> I have a list of names in column A.
>
> What i want is program whereby if I enter a new name at the bottom of the
> column, if it is a duplicate (i.e. name appears elsehwere in list) then a
> msgbox appears telling em it is a duplicate.
>
> Now I can actauly ahcieve this in VBA but...
>
> I have to actaully execute the macro maually.
>
> How can i get the macro to run automatically i.e. I enter a new name,
press
> enter, and the macro runs without me clicking a separate button to run it
of
> pressing F5 etc.
>
> I have tried...
>
> Application.Volatile
> Application. OnKey
> Application. Onaction
>
> How do I get Excel to run a macro once an event has occurred.
>
> Any insights appreciated...
>
> Alex

**firefytr** · 01-29-2005, 03:14 PM

An event such as this might look like this ...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub 'Checking for col A only
    If Application.WorksheetFunction.CountIf(Range("A:A"), Target) > 1 Then
        MsgBox "That is a duplicate value!" & vbNewLine & vbNewLine & _
            "Please try again.", vbInformation, "DUPLICATE!"
        Target.ClearContents
        Target.Activate
    End If
End Sub

.. depending on your desires. Hope this helps.