Option Button/Sorting

erutherford

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

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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."
 
Upvote 0
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????
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
Which line gives the error?
 
Upvote 0
<code>
<code>ThisWorkbook.Sheets("Entries").Range("A2:J50").Sort Key1:=Range("I2"), Order1:=xlAscending, Header:=xlYes
</code>
</code>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top