After macro run cells are non respondent

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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


Code:
Sub CLEARCELLS()'
' CLEARCELLS Macro
'


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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Do you have anything else going on like the Worksheet Change Event? Right click the sheet's tab, View Code, to see.
 
Upvote 0
Hi,
this is the code on that sheet.

Code:
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
 
Upvote 0
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...
 
Upvote 0
Sorry can you explain as I’m not to clever on understanding that.

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

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top