ThePhantom
New Member
- Joined
- Apr 4, 2014
- Messages
- 2
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.
Thanks in advanced
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.
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
Code:
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
Last edited: