Option Button/Sorting
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Option Button/Sorting

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Option Button/Sorting

    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?


    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

  2. #2
    Board Regular JustynaMK's Avatar
    Join Date
    Aug 2016
    Location
    London, UK
    Posts
    436
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Option Button/Sorting

    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."

  3. #3
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Option Button/Sorting

    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


    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


    Ideas????

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Option Button/Sorting

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Option Button/Sorting

    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


    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


  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Option Button/Sorting

    Which line gives the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Option Button/Sorting


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


  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Option Button/Sorting

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Option Button/Sorting

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

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Option Button/Sorting

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •