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

 

[DanteAmor]

You don't answer, it's the third time. I want to know if it is really entering the macro.

Try again:

Private Sub OptPartNo_Click()

Msgbox "send this msg to Dante Amor"
  Application.ScreenUpdating = False
  If OptPartNo.Value = True Then
    Sheets("Table").Range("B4:CG6566").Sort Sheets("Table").Range("D4"), xlAscending, Header:=xlGuess
        
    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

 

[NUC_N_FUTS2]

You don't answer, it's the third time. I want to know if it is really entering the macro.

Try again:

Private Sub OptPartNo_Click()

Msgbox "send this msg to Dante Amor"
  Application.ScreenUpdating = False
  If OptPartNo.Value = True Then
    Sheets("Table").Range("B4:CG6566").Sort Sheets("Table").Range("D4"), xlAscending, Header:=xlGuess
       
    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

 

[NUC_N_FUTS2]

Hi Dante,

I have been telling you that it doesn't appear the macro is doing anything.
I entered the msgbox and I do not get the message once I click the button.

 

[DanteAmor]

I entered the msgbox and I do not get the message once I click the button.

It's not that the macro isn't doing anything. It's just not starting the macro.
That may be because you don't have the macro in the right place.
But let's make it work.
Do the following:

- Select the sheet where you have the optionbutton
- Go Developer tab and press Design Mode

- Double click on optionbutton1. And in the panel that opens, paste the code:

Private Sub OptPartNo_Click()
  Application.ScreenUpdating = False
  If OptPartNo.Value = True Then
    Sheets("Table").Range("B4:CG6566").Sort Sheets("Table").Range("D4"), xlAscending, Header:=xlGuess
        
    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

Return to the excel sheet.
Turn off Design Mode.
Select another optionbutton, now select optionbutton1 to start the macro, even in the code you can put the msg so that you can see that the macro is actually being executed.


To see where the code is, go to the sheet events:
Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code.

 

[NUC_N_FUTS2]

It's not that the macro isn't doing anything. It's just not starting the macro.
That may be because you don't have the macro in the right place.
But let's make it work.
Do the following:

- Select the sheet where you have the optionbutton
- Go Developer tab and press Design Mode

View attachment 119148​

- Double click on optionbutton1. And in the panel that opens, paste the code:
View attachment 119150

Private Sub OptPartNo_Click()
  Application.ScreenUpdating = False
  If OptPartNo.Value = True Then
    Sheets("Table").Range("B4:CG6566").Sort Sheets("Table").Range("D4"), xlAscending, Header:=xlGuess
       
    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

Return to the excel sheet.
Turn off Design Mode.
Select another optionbutton, now select optionbutton1 to start the macro, even in the code you can put the msg so that you can see that the macro is actually being executed.


To see where the code is, go to the sheet events:
Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code.

 

[NUC_N_FUTS2]

Hi Dante,

The macro is running now, thank you very much. I had put it in a module because I thought I had to since i was sorting on a different sheet than where the button was.

Another issue, the sort appears to be excluding row 4. I do not have headers, just want the data on column D to sort Ascending, currently all begin with a number, but they are not formatted by number because they end in text.

Lastly, I am getting a run time error ('424' object required), and when I debug, it is at the ListFillRange row of the Combobox.

 

[DanteAmor]

Another issue, the sort appears to be excluding row 4.

Use:
Header:=xlNo

Sheets("Table").Range("B4:CG6566").Sort Sheets("Table").Range("D4"), xlAscending, Header:=xlNo

Lastly, I am getting a run time error ('424' object required), and when I debug, it is at the ListFillRange row of the Combobox.

That's because the combobox does not exist on sheet Sheet1.

To verify the combo name. Select tab programmer, select Design, click on your combobox and verify that the name of the combo is "cmbDatabase"

NOTE:
I continue to insist that if you are going to put several columns, it is indicated that you use a listbox instead of a combobox.

But do the above and we see...

 

[NUC_N_FUTS2]

Hi Dante,

You are right, it was the name of my combobox, everything is working fine.

Thank you very much!

One more thing: The sort in Column D, my range is multiple rows/columns, but there will be empty cells.
Is there something I could add to the code that will only sort rows that contain a value and put the empty cells below the cells that have data?

 

[DanteAmor]

One more thing: The sort in Column D, my range is multiple rows/columns, but there will be empty cells.
Is there something I could add to the code that will only sort rows that contain a value and put the empty cells below the cells that have data?

Excel certainly does that, unless instead of having the cell empty, it has spaces, that is, if you have a formula and the result of the formula returns "", that is not empty, that is having space.

If you don't have formulas, check that you have empty cells, perhaps you have one or more blank spaces.

 

[NUC_N_FUTS2]

Excel certainly does that, unless instead of having the cell empty, it has spaces, that is, if you have a formula and the result of the formula returns "", that is not empty, that is having space.

If you don't have formulas, check that you have empty cells, perhaps you have one or more blank spaces.