Workbook not running macro on close

[Pacman52]

Hi could someone advise me how to get a workbook to run a macro before it closes please.

I have a workbook called 'StaffDetails' it contains a list of staff names and staff grades. In this work book I have a module with the following code

Sub SortCol()
Dim rng As Range, r As Range
Set rng = Range("A2:B1000")
Set r = Range("A2")
 
 Application.ScreenUpdating = False
 
 rng.Sort key1:=r, order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
 MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
 
 Application.ScreenUpdating = True
 
 End Sub

And on ThisWorkbook I have the this code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
Call SortCol
ActiveWorkbook.Save
End Sub

In another workbook book I have a userform with the following code behind the Commandbutton click event

Private Sub CommandButton1_Click()
Dim StaffName As String
Dim StaffGrade As String
Dim StaffDetails As Workbook
If Textbox1.Text = "" Or ComboBox1.Text = "" Then
        MsgBox "Missing entry.", vbExclamation, "Entry Canceled"
    ElseIf IsNumeric(Application.Match(Textbox1.Text, Sheets("StaffDetailsList").Range("A:A"), 0)) Then
        MsgBox "This name is already in the list, please click cancel and look again.", vbExclamation, "Duplicate Entry"
    Else

Worksheets("StaffDetailsList").Select
StaffName = Textbox1.Value
Worksheets("StaffDetailsList").Select
StaffGrade = ComboBox1.Value
Workbooks.Open ("C:\Users\Paul\Desktop\2017 Diaries\StaffDetails.xlsm")
Worksheets("StaffList").Select
Worksheets("StaffList").Range("A1").Select
RowCount = Worksheets("StaffList").Range("A1").CurrentRegion.Rows.Count
With Worksheets("StaffList").Range("A1")
.Offset(RowCount, 0) = StaffName
.Offset(RowCount, 1) = StaffGrade
End With
Workbooks("StaffDetails").Close SaveChanges:=True
End If
 
Unload Me
MsgBox "Your entry has been accepted. Please return to the date(s) required to enter the name on the  list", vbInformation, "Entry Accepted"
 
End Sub

Everything works fine apart from the columns in the first workbook StaffDetails don't sort themselves when the workbook closes, although if I close it manually then the SortCol macro works fine.

I'm thinking it has something to do with the commandbutton click event but to be honest I'm not that sure and have tried loads of different methods to get the workbook to close and sort the columns.


Please could someone let me know where I'm going wrong

Many thanks

 

[steve the fish]

First thing you must do is qualify your ranges with a sheet name in the sort macro. Once you have done that report back if further problems.

 

[Pacman52]

Hi Steve thanks for the quick reply. My VB knowledge is basic to say the least - I google everything before asking hence all the code above lol.

I have changed it to this - is that what you mean by Qualifying it?

Sub SortCol()
Dim rng As Range, r As Range
Set rng = Range("A2:B1000")
Set r = Range("A2")
 
With Workbooks("StaffDetails.xlsm").Worksheets("StaffList")
    Application.ScreenUpdating = False
    rng.Sort key1:=r, order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
 
    Application.ScreenUpdating = True
End With
 
End Sub

 

[steve the fish]

No these two lines need a sheet name:

Set rng = Range("A2:B1000")
Set r = Range("A2")

if no sheet is used the activesheet at the time the line is run is used. That may or may not be the correct sheet to apply the sort to!

 

[Pacman52]

Ahh ok I think I understand what you mean so If I changed it to

Set rng = Workbooks ("StaffDetails").Range ("A2:B1000") would that qualify it?

Thanks again

Paul

 

[steve the fish]

No. Its the sheet name you need there. The parent of a range is the sheet to which it belongs. The parent of the sheet is the workbook.

 

[Pacman52]

Hi Steve

So I have changed it to the following but it is still not sorting the columns once closed.

Dim rng As Range, r As Range
Set rng = Worksheets("StaffList").Range("A2:B1000")
Set r = Worksheets("StaffList").Range("A2")
 
    Application.ScreenUpdating = False
    rng.Sort key1:=r, order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
 
    Application.ScreenUpdating = True

End Sub

 

[steve the fish]

See if this does it:

Dim rng As Range, r As Range, sh As Worksheet, lr As Long
Set sh = Sheets("StaffList")
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
Set rng = sh.Range("A2:B" & lr)
Set r = sh.Range("A" & lr)
sh.Sort.SortFields.Clear
sh.Sort.SortFields.Add Key:=r, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With sh.Sort
    .SetRange rng
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With