ActiveX Option Button to sort Data in another sheet and update conbobox

[NUC_N_FUTS2]

Hello,

I have ActiveX Option Buttons on one Sheet and once clicked, would like to go to another sheet and sort data then update a combobox with the sorted data.
There are 4 Option buttons in a group that will sort the data in another sheet based on option button clicked, and then update the ListFillRange and ComboWidths.

When I click the button, nothing happens.

Here is the code located in Module 1:

Option Explicit

Private Sub OptPartNo_Click()


Application.ScreenUpdating = True
If OptPartNo.Value = True Then

Sheets("Table").Select
    Range("B4:CG6566").Select
    ActiveWorkbook.Worksheets("Table").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Table").Sort.SortFields.Add2 Key:=Range("D4:D6566" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Table").Sort
        .SetRange Range("B4:CG6566")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
    Sheets("Sheet1").Select
    
With cmbDatabase
    
   .ListFillRange = "SEARCH"
   .ColumnWidths = "0 pt;0 pt;0 pt;108 pt;144 pt;108 pt;108 pt;49.95"
End With
End If
 Application.ScreenUpdating = True
End Sub

Not sure what i am doing wrong here. I kept screenupdating as True so I can see what is happening. The sort doesn't happen.

Can anyone help?

Thanks in advaice.

Nuc

 

[NUC_N_FUTS2]

Hi Dante,
I do in fact have a formula that returns "".
I am basically pulling data from other sheets and it will return 0 if I did not include the formula. Is there a work around?

 

[DanteAmor]

I do in fact have a formula that returns ""

These are details that you should include in your original post.

Try the following:

Private Sub OptPartNo_Click()
  Dim lr1 As Long, lr2 As Long
  Application.ScreenUpdating = False
  If OptPartNo.Value = True Then
    
    With Sheets("Table")
      lr1 = .Range("D" & Rows.Count).End(3).Row
      .Range("B4:CG" & lr1).Sort .Range("D4"), xlDescending, Header:=xlNo
      On Error Resume Next
      lr2 = WorksheetFunction.Match("", .Range("D4:D" & lr1), 0) + 2
      On Error GoTo 0
      If lr2 = 0 Then lr2 = lr1
      
      With .Range("B4:CG" & lr2)
        .Sort .Range("D4"), xlAscending, Header:=xlNo
        cmbDatabase.ListFillRange = .Address(0, 0, external:=True)
        cmbDatabase.ColumnWidths = "0 pt;0 pt;0 pt;108 pt;144 pt;108 pt;108 pt;49.95"
      End With
    End With
    
  End If
  Application.ScreenUpdating = True
End Sub

 

[NUC_N_FUTS2]

Works great Dante!
Thank you very much!

Have a wonderful day!