select case question

kemperohlmeyer

Board Regular
Joined
Oct 4, 2006
Messages
73
Fellow Nerds,

quick question...I wrote a macro that is used in conjunction with a combo-box using select case. The select case sub looks at the output cell of the combo box and then calls one of 5 subs to sort the data on the sheet. The sub works fine, but i want to make it real-time so that the user doesnt have to run the sub in order to sort the data. I just want the user to select his/her sort method and then have the code pick up the fact the output box changed and then automatically runs the sub.

I have VBA experience, but am a bit rusty. The present code is below.

Sub selectsort()
Dim dd As Integer
Select Case Range("c6").Value
Case 1
Call SortNames
Case 2
Call SortRoom
Case 3
Call SortAirline
Case 4
Call SortArriveTime
Case 5
Call SortDepartTime
End Select
End Sub

Thanks for the help,
ko
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Why not use the combobox change event?

The exact code will depend on how you created the combobox.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Instead of using a ComboBox, simply use a good old data validation list in a cell. Now you can use the WorkSheet_Change event to call the macro.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$C$6" Then Exit Sub
Select Case Target
Case Is = 1: Call SortNames
Case Is = 2: Call SortRoom
Case Is = 3: Call SortAirline
Case Is = 4: Call SortArriveTime
Case Is = 5: Call SortDepartTime
Case Else: Exit Sub
End Select
End Sub

lenze

Note: Will not work on Excel 97
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hello kemperohlmeyer,
I agree with lenze. I'd likely use a data validation cell & SheetChange event.
If for some reason you want to use the ComboBox then I agree with Norie.
(Howdy fellas! :) )
For a ComboBox from the Forms toolbar. . .
Code:
Sub DropDown1_Change()
'Change "DropDown1" to its real name
  Select Case Range("c6").Value
    Case 1
      Call SortNames
    Case 2
      Call SortRoom
    Case 3
      Call SortAirline
    Case 4
      Call SortArriveTime
    Case 5
      Call SortDepartTime
  End Select
End Sub

Or, if you're using a ComboBox from the Controls Toolbox toolbar. . .
Code:
Private Sub ComboBox1_Change()
'Change "ComboBox1" to its real name
  Select Case ComboBox1.Value
    Case 1
      Call SortNames
    Case 2
      Call SortRoom
    Case 3
      Call SortAirline
    Case 4
      Call SortArriveTime
    Case 5
      Call SortDepartTime
  End Select
End Sub

(Note with the last one you won't need the linked cell unless it's used for other purposes.)

Hope it helps.
 

Forum statistics

Threads
1,181,055
Messages
5,927,858
Members
436,573
Latest member
CMR237

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
Top