select case question

[kemperohlmeyer]

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

 

[Norie]

Why not use the combobox change event?

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

 

[lenze]

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.

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]

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

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

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.