event handling 
Author Message
 event handling

Hi.

I'm trying to get my head around the idea that if I type
the number '1' into a cell, this will trigger an input box
to take data into another cell two cells to the right
which are otherwise protected and blank...and I just don't
know where to start. Anyone got any code for this or
similar...

Thanks in advance

Gordon



Thu, 24 Nov 2005 17:53:14 GMT  
 event handling
Gordon,

If you want to do it in VBA, you are talking about worksheet events.

The 2 primary worksheet events are
- SelectionChange which gets triggered when you you select a cell, and
- Change which gets triggered when you change a cell.

Each of these events is passed a Target argument, which is the cell(s)
selected. You test Target to see whether these are cells that you are
interested in (rarely would you want the event to apply to all cells on the
worksheet). As en example, this codes tests if any cell in column A is
selected
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Column = 1 Then
        MsgBox "Column A"
    End If
End Sub
If it's not column A, the test fails.You could also test for a specific
cell, such as If Target = Range("A10").

Sometimes you may want to test to see if the selection is within a group of
cells, so you use the Intersect test, like this
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("B10:M25")) Is Nothing Then
        MsgBox Target.Address
    End If
End Sub
which will test if the selected cell is in that given range.

The Change event works very similarly, it just gets triggered on the cell
change.

The other thing I would add is that often you change something on  the
worksheet when  you trap these events, so it is best to disable events
before changing things, to avoid a cascade of events, and unnecessary
processing. This is an example
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo sub_exit
    If Not Intersect(Target, Range("data")) Is Nothing Then
        If Target.Count = 1 Then
            MsgBox "database has changed"
        End If
    End If
sub_exit:
    Application.EnableEvents = True
End Sub

Finally, to answer the particular question, this bit of code do what you
asked

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo sub_exit
    If Target.Value = 1 Then
        ActiveSheet.Unprotect
        With Target.Offset(0, 2)
            .Value = Target.Value
        End With
        ActiveSheet.Protect = True
    End If
sub_exit:
    Application.EnableEvents = True
End Sub

--
    HTH

    -------

    Bob Phillips
    ... looking out across Poole Harbour to the Purbecks


Quote:
> Hi.

> I'm trying to get my head around the idea that if I type
> the number '1' into a cell, this will trigger an input box
> to take data into another cell two cells to the right
> which are otherwise protected and blank...and I just don't
> know where to start. Anyone got any code for this or
> similar...

> Thanks in advance

> Gordon



Thu, 24 Nov 2005 18:25:09 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Deletion: Event Handling in VB Script

2. Outlook Event handling in Visual C++

3. event handling for autofilter and subtotaling

4. Event handling for a array of controls

5. Excel Event handling

6. Application level event handling - who get what first?

7. Event Handling

8. event handling activate

9. Event handling File Save

10. Class Modules and Event Handling


 
Powered by phpBB® Forum Software © phpBB Group