Making me crazy

[ThePhantom]

Hi all

While i am still learning VBa coding is have run into a problem i seem to not be able to solve.

I have a list in which i have alot of data which are copied in by macro (Works fine) and values are extracted from this list to input cells when selected, from which you also change the values in the list.
Now i have been working on a solution for being able to select a row in the list but not change it but it keeps giving me problems.

The Problem is what i run a macro to save the data to the list, but the macro in the worksheet.change event runs even after the main macro is done.

My goal is to run the save macro, without having the event handler checking for changes while it is running.

I have tried to change a cell to true while the save macro are running and use if in the event, and i tried to use Application.enableevents=false with no result.


Anyone with an idea to why this code is behaving like it does ?


The save macro are bound to a Button which have the Cont_save code

In worksheet change i have another code.

(the save button 
Sub Cont_Save()
With Sheet2
 .Range("B10").Value = True
If IsEmpty(Range("F6").Value) Or IsEmpty(Range("F8").Value) _
    Or IsEmpty(Range("F10").Value) Or IsEmpty(Range("F12").Value) Or IsEmpty(Range("I4").Value) Then ' Checks if the yellow cells are empty
        MsgBox "Alle gule felter skal udfyldes" 'Text Box
        Exit Sub
Else


    ContRow = .Range("D5013").End(xlUp).Row + 1  ' First availble row OPDATERE denne linie hvis du vil udvide databasen
        For ContCol = 4 To 14 'Sets ContCol to the column number though a loop
            .Cells(ContRow, ContCol).Value = .Range(.Cells(14, ContCol).Value).Value 'Sets Input cells location (Column and Row)
        Next ContCol
    .Shapes("ExistContGrp").Visible = msoCTrue 'Sets Icon group 1 to true
    .Shapes("NewContGrp").Visible = msoFalse 'Sets Icon group 2 to False
    .Range("B3").Value = False
    .Range("B10").Value = False
End If
End With


End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


If Sheet2.Range("B5").Value = True Then


    If Not Intersect(Target, Range("F4:M14")) Is Nothing And Range("B3").Value = False And Range("B4").Value = False Then ' Checks for changes in
On Error Resume Next
        Cells(Range("B2").Value, Cells(Target.Row, Target.Column + 13).Value).Value = Target.Value
        Cells(Range("B2").Value, Cells(6, 22).Value).Value = Range("I6").Value 'Rengør beløb
        Cells(Range("B2").Value, Cells(8, 22).Value).Value = Range("I8").Value ' Michael
        Cells(Range("B2").Value, Cells(10, 22).Value).Value = Range("I10").Value ' Uffe
        Cells(Range("B2").Value, Cells(12, 22).Value).Value = Range("I12").Value 'Status
        Cells(Range("B2").Value, Cells(4, 25).Value).Value = Range("M4").Value 'Valuta
        Cells(Range("B2").Value, Cells(4, 22).Value).Value = Range("I4").Value  '****dato
    End If
End If
        If Not Intersect(Target, Range("D16:N5013")) Is Nothing And Sheet2.Range("B6") = True And Range("B10").Value = False Then
        MsgBox "Brug indtastningsfelterne til at ændre disse poster"
        'Application.EnableEvents = False
        Application.Undo
       Range("B2").Value = Target.Row
       'Application.EnableEvents = True
Exit Sub


End If
Exit Sub

Thanks in advanced

 

[Yongle]

The Problem is what i run a macro to save the data to the list, but the macro in the worksheet.change event runs even after the main macro is done

A Worksheet_Change macro is triggered EVERY time a change is made in the worksheet
- to avoid that, EnableEvents must be set to FALSE before changes are made to the values in that worksheet
- the value must be set back to TRUE even if the code fails (otherwise no events will trigger until you re-open the workbook

Try inserting these lines

Sub Cont_Save()
        'rest of code...
Else
[COLOR=#ff0000]    On Error Resume Next[/COLOR]
[COLOR=#ff0000]    Application.EnableEvents = False[/COLOR]
        ContRow = .Range("D5013").End(xlUp).Row + 1
        'rest of code...
End If
[COLOR=#ff0000]Handling:
Application.EnableEvents = True[/COLOR]
End Sub

 

[ThePhantom]

Thank you for your Reply Yongle.

After also trying your suggestion without any different result, i became so furious what i copied into a new sheet and testet it with succes. I when copied it back and now it is work perfectly. I am totally lost on why it is.