Reputation: 11

How to run this function in excel using vba

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("B12").Address Then

        Application.EnableEvents = False

        Dim sOldValue As String, sNewValue As String
        sNewValue = Target.Value

        Application.Undo

        Dim rOld As Range
        Set rOld = Range("A1:E1").Value

        Target.Value = sNewValue

        Range("A15:E15").Value = rOld.Value

        Application.EnableEvents = True

    End If

End Sub

How to run this function, can you please call this function?

Upvotes: 1

Answers (4)

Dominique

Reputation: 17493

One thing is basic: how did you create this function? Did you open the Excel VBA editor, select a sheet and chose the corresponding event, like I did in the following screenshot:

As you see, the macro is linked to "Sheet1", it is linked to the events of the "Worksheet" itself, and it is triggered by any "Change" of that worksheet.

Upvotes: 0

VBasic2008

Reputation: 54777

A Worksheet Change: Change Range Values on Cell Change

Worksheet_ in the signature Private Sub Worksheet_Change(ByVal Target As Range) indicates that this procedure belongs in the sheet module, e.g. Sheet1, of the worksheet where you want it applied (not in the ThisWorkbook module nor in a standard module, e.g. Module1). Such a procedure will run automatically (get triggered) when an event occurs, particularly for this procedure, after a manual change has happened in a range i.e. after
- you write something into the formula bar and press enter,
- you (copy) paste values to a range, or
- you use VBA to write values to a range.
In this procedure, if you want to write something to a range of the worksheet, to not retrigger the event and possibly end up with an endless loop ('crashing' Excel), you will disable events before you start writing, and enable them after writing as you did in your code. If an error occurs between these two lines, the events will stay disabled and the code won't trigger until they are enabled again.
To check if events are enabled you could use the line Debug.Print Application.EnableEvents in another procedure or in the Immediate window just use ?Application.EnableEvents and press enter. Similarly, if the answer is False, in the Immediate window, you can use Application.EnableEvents = True and press enter to enable events.
The line Set rOld = Range("A1:E1").Value is wrong and results in

Run-time error '424': Object required

To avoid the error you could use one of the following:

Dim rgOld As Range: Set rgOld = Range("A1:E1")
Range("A15:E15").Value = rgOld.Value
Target.Value = sNewValue

Dim OldValues() As Variant: OldValues = Range("A1:E1").Value
Range("A15:E15").Value = OldValues ' or after the following line
Target.Value = sNewValue

Range("A15:E15").Value = Range("A1:E1").Value
Target.Value = sNewValue

Basically, you want to write the data before rewriting the new value. Optionally, in the second case where the data is written to an array (OldValues), you can write the values afterward.
Since the use of an additional variable is kind of redundant in the first two cases, the last (the simplest, the most straightforward) case is used in the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Address <> "$B$12" Then Exit Sub

    Dim NewString As String: NewString = CStr(Target.Value)
    
    Application.EnableEvents = False

        Application.Undo ' this will also write (trigger the event)
        Me.Range("A15:E15").Value = Me.Range("A1:E1").Value
        Target.Value = NewString ' redo
    
    Application.EnableEvents = True

End Sub

If you want to modify (experiment with) the code, you should introduce some error handling so you don't end up with events disabled.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ClearError
     
    If Target.Address <> "$B$12" Then Exit Sub
    
    Dim NewString As String: NewString = CStr(Target.Value)
    
    Application.EnableEvents = False

    Application.Undo ' this will also write (trigger the event)
    Me.Range("A15:E15").Value = Me.Range("A1:E1").Value
    Target.Value = NewString ' redo
    
SafeExit:
    On Error Resume Next
        If Not Application.EnableEvents Then Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub
ClearError:
    Debug.Print "Run-time error'" & Err.Number & "': " & Err.Description
    Resume SafeExit
End Sub

Upvotes: 0