After macro run cells are non respondent

[ipbr21054]

Hi,
I have a simple macro which just clears some data in a couple of cells.
The macro code is shown below.

Once it is run i am then unable to do anything with these cells.
If i save,close the worksheet & then open again it allows me to add values etc but as soon as i run this macro again back to square one in that i need to save,close & reopen again.

Can something be added to this macro which say resets etc without me having to close & open each time.

Thanks

Sub CLEARCELLS()'
' CLEARCELLS Macro
'


'
    Range("G27:I27").ClearContents
    Range("L31:O31").ClearContents
    Range("G47:G50").ClearContents
End Sub

 

[Kenneth Hobson]

Do you have anything else going on like the Worksheet Change Event? Right click the sheet's tab, View Code, to see.

 

[ipbr21054]

Hi,
this is the code on that sheet.

Private Sub Worksheet_Change(ByVal Target As Range)Dim C As Range, d As Range
Set d = Intersect(Target, Range("G13:O17", "G27:O42"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each C In d
        If C.Column <> 14 Then
            If Not C.HasFormula Then C = UCase(C)
        End If
    Next
        If Intersect(Target, Range("G13")) Is Nothing Then Exit Sub
    Dim rName As Range, srcWS As Worksheet
    Set srcWS = Sheets("DATABASE")
    Set rName = srcWS.Range("A6:A" & srcWS.Cells(srcWS.Rows.Count, 1).End(xlUp).Row).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not rName Is Nothing Then
        Range("N15") = srcWS.Range("B" & rName.Row)
        Range("N14") = srcWS.Range("D" & rName.Row)
        Range("N16") = srcWS.Range("L" & rName.Row)
        Application.EnableEvents = True
        End If
End Sub
Private Sub Clear_Invoice_After_Printing_Click()
    Dim strFileName As String
    
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & Range("N4").Value & ".pdf"
    If Dir(strFileName) <> vbNullString Then
        MsgBox "INVOICE " & Range("N4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
        Exit Sub
    End If
    
    With ActiveSheet
        .PageSetup.PrintArea = "$G$3:$O$60"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        MsgBox "INVOICE " & Range("N4").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
        Range("G13:I18").ClearContents
        Range("N14:O18").ClearContents
        Range("G27:N42").ClearContents
        Range("G13:I13").ClearContents
        Range("G45:I49").ClearContents
        Range("N14:O15").ClearContents
        Range("N17:O17").ClearContents
        Range("N18:O18").ClearContents
        Range("G27:N42").ClearContents
        Range("G48:I48").ClearContents
        Range("G49:I49").ClearContents
        Range("N4").Value = Range("N4").Value + 1
        Worksheets("INV2").Range("N4").Value = Range("N4").Value
        Range("G13").Select
        ActiveWorkbook.Save
    End With
End Sub


Private Sub Print_One_Invoice_Click()


    If Range("N18") = "" Then
        MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "Payment Type Not Selected"
    Else
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End If
End Sub
Private Sub Print_Two_invoices_Click()
    If Range("N18") = "" Then
        MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "Payment Type Not Selected"
    Else
ActiveWindow.SelectedSheets.PrintOut Copies:=2
    End If
End Sub
Private Sub Worksheet_Activate()
Range("G13").Activate
End Sub

 

[Kenneth Hobson]

Disable and enable events in the macro like you did in the event macro. That is if you don't want the event to trigger due to the macro...

 

[ipbr21054]

Sorry can you explain as I’m not to clever on understanding that.

Thanks.

 

[Kenneth Hobson]

Sub CLEARCELLS()
  Application.EnableEvents = False
  Range("G27:I27,L31:O31,G47:G50").ClearContents
  Application.EnableEvents = True
End Sub

 

[ipbr21054]

Many thanks,that worked a treat.

Would you mind taking a look at this https://www.mrexcel.com/forum/excel-questions/1083009-update-code-data-entered.html
basically i have two sheets & on one sheet it collects data from the other or supposed to.

Thanks