CODEWITHSUNDEEP
excelvba
Michael Jarvis
Michael Jarvis

Reputation: 1143

Enter value into multiple cells without using Ctrl+Enter

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?

Upvotes: 0

Views: 48

Answers (1)

BigBen
BigBen

Reputation: 50007

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

Upvotes: 1

Related Questions