Option Button/Sorting

[erutherford]

The project has grown and has a few pains still and this is one. Sometimes it will work and sometimes it doesn't and not sure why. The objective is to sort on Col "J " within a range. Seems simple. We never allow more than 50 entries, so I first setup the range @ "A2:A50", but if there were blank cells then an error would occur "sort reference is not valid". Then I would manual change it the last full cell, then it worked. Save the file and next time I would use the function, like this morning an "error".

Any thoughts or a better way?

<code>
Private Sub CommandButton2_Click()
If OBSummary.Value = True Then
Unload UserForm7
If Application.Dialogs(xlDialogPrinterSetup).Show = True Then ThisWorkbook.Sheets("Summary").Range("PrtSummary").PrintPreview

'*****************************************************
ElseIf OBDetailSum.Value = True Then
Unload UserForm7
If Application.Dialogs(xlDialogPrinterSetup).Show = True Then ThisWorkbook.Sheets("SummaryDetail").Range("PrtSummaryDetail").PrintPreview

'*****************************************************
Else
Unload UserForm7

If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
ThisWorkbook.Sheets("Entries").Range("A2:J43").Sort Key1:=Range("J3"), Order1:=xlAscending, Header:=xlYes
ThisWorkbook.Sheets("Entries").Range("A2:J43").Sort Key1:=Range("I3"), Order1:=xlAscending, Header:=xlYes
Range("A2:J43").PrintPreview
PrToFileName = True

ThisWorkbook.Sheets("Entries").Range("A2:J43").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End If
End If
End Sub
</code>

 

[JustynaMK]

Hi,

I would start by sorting your code - you have four "If" statements but only two "End If"; this is potentially where VBA gets lost and you experience the issues.
Also blank cells shouldn't disturb the sorting - as per other MrExcel post:
"Sort xlAscending to put numbers at the top, then text, then blanks. xlDescending to put text at the top, then numbers, then blanks."

 

[erutherford]

I think you may be correct on your answer. So in order to troubleshoot this I am using 3 separate command buttons. So now I only have to deal with one issue, I hope.

A command button opens UserForm 7. On this UserForm7 are the 3 command buttons. Below is the code for the CMD button that gives the following error. " Sort ref not valid"

Row A1 - Blank
Row A2 - Headers
Row A3:J50 - Contains the data

<code>
Private Sub CommandButton8_Click()
Unload UserForm7
If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
ThisWorkbook.Sheets("Entries").Range("A2:J50").Sort Key1:=Range("I3"), Order1:=xlAscending, Header:=xlYes
ThisWorkbook.Sheets("Entries").Range("A2:J50").Sort Key1:=Range("J3"), Order1:=xlAscending, Header:=xlYes
Range("A2:J50").PrintPreview

ThisWorkbook.Sheets("Entries").Range("A3:J50").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End If
UserForm7.Show
End Sub
</code>

Ideas????

 

[Fluff]

Your last sort line has different ranges from the first two.
Also the Key needs to be in the sort range, but your is outside.

 

[erutherford]

I hoping you were out there watching! Sorry about the typos, I have played with this section way to many times!

I still get the error

<code>
Private Sub CommandButton8_Click()
Unload UserForm7
If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
ThisWorkbook.Sheets("Entries").Range("A2:J50").Sort Key1:=Range("I2"), Order1:=xlAscending, Header:=xlYes
ThisWorkbook.Sheets("Entries").Range("A2:J50").Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlYes
Range("A2:J50").PrintPreview

ThisWorkbook.Sheets("Entries").Range("A2:J50").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End If
UserForm7.Show
End Sub

</code>

 

[Fluff]

Which line gives the error?

 

[erutherford]

<code>
<code>ThisWorkbook.Sheets("Entries").Range("A2:J50").Sort Key1:=Range("I2"), Order1:=xlAscending, Header:=xlYes
</code>
</code>

 

[Fluff]

Ok, try

Private Sub CommandButton8_Click()
Unload UserForm7
If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
   With ThisWorkbook.Sheets("Entries")
      .Range("A2:J50").Sort Key1:=.Range("I2"), Order1:=xlAscending, Header:=xlYes
      .Range("A2:J50").Sort Key1:=.Range("J2"), Order1:=xlAscending, Header:=xlYes
      .Range("A2:J50").PrintPreview
   
      .Range("A2:J50").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes
   End With
End If
UserForm7.Show
End Sub

 

[erutherford]

That did it. Was the duplicate of "This workbook.sheets..." causing the problem?

 

[Fluff]

No it was you hadn't specified the workbook/sheet for the Key.
The With statement is a shortcut, but you could have written each line like

ThisWorkbook.Sheets("Entries").Range("A2:J50").Sort Key1:=ThisWorkbook.Sheets("Entries").Range("I2"), Order1:=xlAscending, Header:=xlYes