Enter value into multiple cells without using Ctrl+Enter

Question

I want to select multiple cells and whilst typing a value, it to be added to all the cells without having to do Ctrl+Enter at the end

Is this possible using VBA?

So is the question to be able to just use Enter instead of Ctrl+Enter? — BigBen, Apr 10 '20 at 19:46
@BigBen Preferably it's as I'm typing, so I select the cells and then when typing it adds the value to all the cells. If not then pressing Enter only would be the alternative — Michael Jarvis, Apr 10 '20 at 19:48
Ok, posted an answer that works just using Enter... I think you'll have to you [this trickery](https://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell) to modify cells as you're typing. — BigBen, Apr 10 '20 at 19:51

BigBen · Accepted Answer · 2020-04-10T20:00:17.363

For an approach that just requires you to press Enter, you could leverage the Change event, something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If TypeOf Selection Is Range Then
        If Not Intersect(Selection, Target) Is Nothing Then
            On Error GoTo SafeExit
            Application.EnableEvents = False
            Selection.Value = Target.Cells(1).Value
        End If
    End If
SafeExit:
    Application.EnableEvents = True
End Sub

If you want this to work when a formula is entered (not sure you would, but in any case):

Private Sub Worksheet_Change(ByVal Target As Range)
    If TypeOf Selection Is Range Then
        If Not Intersect(Selection, Target) Is Nothing Then
            On Error GoTo SafeExit
            Application.EnableEvents = False
            If Target.HasFormula Then
                Selection.Formula = Target.Cells(1).Formula
            Else
                Selection.Value = Target.Cells(1).Value
            End If
        End If
    End If
SafeExit:
    Application.EnableEvents = True
End Sub

Enter value into multiple cells without using Ctrl+Enter

1 Answers1