Closing workbook AND VBA file

Question

I have built a small VBA script, but I am having trouble fully closing out the workbook. I can close the work book but have noticed that it still shows up in the VBA editor. I have seen other forms on placing a bit of code to close everything out but I can not get it to work.

Can anyone point me in the right direction?

Private Sub PNTXLXS_Click()

Application.DisplayAlerts = False
Application.EnableCancelKey = xlDisabled

RCD_PNT.Hide


 With Sheets("Clash List").UsedRange
      mr = .Rows.Count
      mc = .Columns.Count
     .Range(Cells(1, 26), Cells(mr,mc)).SpecialCells(xlCellTypeVisible).Copy
 End With

 Workbooks.Add

 Application.Visible = True

With ActiveSheet.Range("A1")
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With

With Selection
    .WrapText = False
    .EntireColumn.AutoFit
    .WrapText = True
End With


InitialName = Range("A1") & " - " & Format(Now(), "DDMMYY")

filesavename = Application.GetSaveAsFilename(InitialFileName:=InitialName, 
fileFilter:="Excel Files (*.xlsx), *.xlsx")

ActiveWorkbook.SaveAs FileName:=filesavename

ActiveWorkbook.Close

I know this is is the location i need something to close the VBA file correct?

Application.CutCopyMode = False

Application.DisplayAlerts = True

End Sub

Thanks

Mark

Answer 1

Remove Application.Visible = Truefrom your code and instead of RCD_PNT.Hide use Me.Hide

Private Sub PNTXLXS_Click()

Application.DisplayAlerts = False
Application.EnableCancelKey = xlDisabled

Me.Hide


 With Sheets("Clash List").UsedRange
      mr = .Rows.Count
      mc = .Columns.Count
     .Range(Cells(1, 26), Cells(mr,mc)).SpecialCells(xlCellTypeVisible).Copy
 End With

 Workbooks.Add

' Application.Visible = True

With ActiveSheet.Range("A1")
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With

With Selection
    .WrapText = False
    .EntireColumn.AutoFit
    .WrapText = True
End With


InitialName = Range("A1") & " - " & Format(Now(), "DDMMYY")

filesavename = Application.GetSaveAsFilename(InitialFileName:=InitialName, 
fileFilter:="Excel Files (*.xlsx), *.xlsx")

Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub

And it is certainly a good idea to read Userform.show

And it is better way to show the userform like

Sub Show_RCDPNT()

Dim frm As New RCD_PNT

    frm.Show
    Unload frm

End Sub

Answer 2

I see a Workbooks.Add command so a new Workbook is being opened: are you doing anything with it? You're using Sheets before that line so you've got one opened already and it's the ActiveWorkbook, not that new one. Then you make Excel visible, work with some ranges, and save a copy of the ActiveWorkbook. But since the Excel Application object still exists that new workbook is hanging out in the Workbooks collection, yes? Maybe that's what you're seeing?