Option Button/Sorting

erutherford

Active Member
Joined
Dec 19, 2016
Messages
320
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

Well-known Member
Joined
Aug 28, 2016
Messages
536
Office Version
365, 2013
Platform
Windows
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

Active Member
Joined
Dec 19, 2016
Messages
320
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,034
Office Version
365
Platform
Windows
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

Active Member
Joined
Dec 19, 2016
Messages
320
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,034
Office Version
365
Platform
Windows
Which line gives the error?
 

erutherford

Active Member
Joined
Dec 19, 2016
Messages
320
<code>
<code>ThisWorkbook.Sheets("Entries").Range("A2:J50").Sort Key1:=Range("I2"), Order1:=xlAscending, Header:=xlYes
</code>
</code>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,034
Office Version
365
Platform
Windows
Ok, try
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,034
Office Version
365
Platform
Windows
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
Code:
ThisWorkbook.Sheets("Entries").Range("A2:J50").Sort Key1:=ThisWorkbook.Sheets("Entries").Range("I2"), Order1:=xlAscending, Header:=xlYes
 

Forum statistics

Threads
1,086,259
Messages
5,388,734
Members
402,137
Latest member
pkulkarni

Some videos you may like

This Week's Hot Topics

Top