Worksheet_SelectionChange query

[rogerte]

I don't know if I am even using the correct event, but what I am trying to achieve is that when a user enters a value in one particular cell, other cells are filled in, based on that value.

The following code, which is attached to the relevent sheet, sort of works, in that the writes to the cells only happen if the user clicks on the cell again after they have changed the value (e.g. change value in O2, press enter or click on another cell and nothing happens. Click on O2 again and the cells are filled-in correctly)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long
Dim Remainder As Float
' never more than 10K rows so following should be ok
If Not Application.Intersect(Target, Me.Range("O2:O65000")) Is Nothing Then
    i = ActiveCell.Row
    Remainder = Range("O" & i).Value
    'MsgBox (CStr(Remainder))
    ' Just in case previous value set back to zero
    If Remainder = 0 Then
        Range("V" & i).Value = 0     
        Range("W" & i).Value = 0
        Range("X" & i).Value = 0
        Range("Y" & i).Value = 0
        Range("Z" & i).Value = 0
        Range("AA" & i).Value = 0
        Range("AB" & i).Value = 0
        Exit Sub
    End If
    'Value ok - Assign it
    If Remainder >= Range("N" & i).Value Then
        Range("V" & i).Value = Range("M" & i).Value
        Range("W" & i).Value = Range("I" & i).Value
        Range("X" & i).Value = Range("K" & i).Value
        Range("Y" & i).Value = Range("J" & i).Value
        Range("Z" & i).Value = Range("H" & i).Value
        Range("AA" & i).Value = Remainder - Range("N" & i).Value
        Range("AB" & i).Value = 0
        Exit Sub
    ELSE
        'More code to go here to divvy out the value based on values in other cells
    End If
End If
End Sub

Is there a way of firing the macro immediately the user presses enter after entering data or clicks on another cell?

I should point out that there is an excel cell formula that uses the value of the cell (e.g. value in N2 "= M2-02") - I don't know if that would affect the running of this macro

Thanks

 

[PHV]

Why not using formulas in the worksheet ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

Use Private Sub Worksheet_Change(ByVal Target As Range) instead.
The Change event fires when the contents of cell(s) changes (other than recalculation). You may need to temporarily disable events (Application.EnableEvents=False) to avoid handling events when cells are filled in by macro.

combo

 

[SkipVought]

If you have Excel 2007+, make your table a Structured Table and formulas will propogate to new rows as data is entered.

Excel 2003- will behave similarly using Data > List applied to tables.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rogerte]

COMBO: Change to Workshop_change still required me to click back in the cell, but the values returned to the other cells were not as expected. Changed back to _SelectionChange and back to normal.

Adding the Application.EnableEvents lines didn't cause any other changes.

PHV - Didn't use workshop formulas because the "divvying out" code, which I have excluded above) is quite complex for Excel functions and it works well within VBA (apart from the problem with when the event fires!)

 

[PHV]

quite complex for Excel functions
You may use UDF (User Defined Function) in your formula.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

The Change event fires when you terminate edition of the cell: by pressing ENTER key, selecting other cell or pasting data to the cell.

Have you replaced [tt]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/tt] by [tt]Private Sub Worksheet_Change(ByVal Target As Range)[/tt]?

The [tt]Application.EnableEvents=False[/tt] should be after declarations section of this procedure, [tt]Application.EnableEvents=True[/tt] before [tt]End Sub[/tt] - to stop firing events when you change cells by code.


combo

 

[xlbo]

change event fires when the ell is changed
select event fires when acell is selected

the change event will only fire once the cell change is complete ie after you have pressed enter to confirm teh change to the cell

For adjusting things based on data entry I would ALWAYS go with change event - select event should only be used to direct user navigation / add complex "protection" - it is not actually of that much use in general

In either case, this line:
i = ActiveCell.Row

is dangerous and redundtant (dangerous as you should never assume what is active) and redundant as the TARGET variable which is passed to the event is a range variable that defines the cell(s) that is / are changed / selected

You should therefore use:

i = target.Row

The other reason for not using select is the added potential for someone to have selected more than 1 cell in which case your code will fall over


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rogerte]

Sorry for delay in getting back - work sent me to the wilds of Northumbria with no access to Internet!

Have sorted it.

Changed it to worksheet_change event - still needed a second click to get it to work. Then added an explicit jump out to the first IF statement, and, as if by magic, it works. Not certain why though!
Also included a jump out to stop multi-selection, and changed ActiveCell to Target (as suggested by Geoff above)

If Not Application.Intersect(Target, Me.Range("O2:O65000")) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
    i = Target.Row
.....
Else
  Exit Sub
End If

I must say I haven't used BASIC since the days of BBC BASIC and TURBO BASIC under MSDOS, and I am finding it interesting (but also a bit frustrating - but that's probably because I am now used to Pardox/Delphi ).

Thanks to everyone who has given pointers and advice - much appreciated.