Solved: Incorrect VBA Sort on Workbook Close

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

You'll see from the attached file that I have two worksheets contained within my workbook, one called "Input", the other called "Resource Summary".

Although this is a cut down version of my spreadsheet, basically the "Resource Summary" sheet is just that, it creates a unique list of names from the multiple lines data on the "Input" sheet.

Part of this workbook contains the following code using the 'Workbook close' event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim EndRow As Long

Sheets("Input").Protect "password", UserInterfaceOnly:=True
With ThisWorkbook.Worksheets("Input")
ActiveWindow.FreezePanes = False
If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
If .Range("B7").Value = "" Then Exit Sub
'find last row of data
EndRow = Range("B7").End(xlDown).Row - 1
.Range("B7:D" & EndRow).Sort Key1:=.Range("B7"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End With

End Sub
The main purpose of this code is to sort the data via column B on my "Input" sheet always excluding the last row which has the value of "Enter your name".

If I open the file and select the "Input" sheet and then close the file, the sort function works perfectly i.e leaving the cell with the value "Enter your name" as the last row.

The problem I have is that if I open the file, and select the "Resource Summary" sheet, and then close the file the "Enter your name" is mistakingly sorted as part of the named list on both the "Resource Summary" and "Input" sheets, and I've no idea why.

I just wondered whether someone may be able to look at this please and let me know where I've gone wrong.

Many thanks and kind regards

Chris

[Paul_Hossler]

A quick and untested guess is that if you want to work with 'Input' you need the dot in front of the Cells and Range everywhere within the With, otherwise I believe it will use whichever sheet happens to be active




Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim EndRow As Long


Sheets("Input").Protect "password", UserInterfaceOnly:=True
With ThisWorkbook.Worksheets("Input")
ActiveWindow.FreezePanes = False
If Sheets("Input").FilterMode = True Then
Sheets("Input").ShowAllData
End If
.Cells.EntireRow.Hidden = False
.Cells.EntireColumn.Hidden = False
If .Range("B7").Value = "" Then Exit Sub
'find last row of data
EndRow = .Range("B7").End(xlDown).Row - 1
.Range("B7:D" & EndRow).Sort Key1:=.Range("B7"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


End With


End Sub


Paul

[Teeroy]

Hi Hobbinton73, Paul beat me to the same conclusion. Isn't it annoying when code works "sometimes" :banghead:.

[hobbiton73]

Hi @Paul,thank you very much for taking the time to reply to my post, and for the solution, it works great!

Forgive me for asking, as I'm relatively new to VBA, but what does the dot do?

Many thanks and kind regards

Chris

[hobbiton73]

Hi @Teeroy, thank you for taking the time to reply to my post and for the solution. As per my reply to Paul, it works great.

Once again many thanks and kind regards

Chris

[Paul_Hossler]

Forgive me for asking, as I'm relatively new to VBA, but what does the dot do?

In general, the dot connects an object to it's parent

Worksheets("Sheet1").Range ("B2") is explicitly part of Sheet1

Just Range ("B2") is assumed to be that activesheet, which might not be the sheet you expect, unless by cooincidence Sheet1 happens to be active.

Also works for connecting methods. Like you had in your code

Sheets("Input").ShowAllData

this will only work on Input

Just ShowAllData will work on the Activesheet

FWIW, I always use the dot.

Paul

[hobbiton73]

Hi Paul, thank you very much for taking the time to come back to me with this. This is really very interesting and will certainly help me in the future.

Many thanks and kind regards

Chris