Problem Using Combo Box to autofilter 2 columns

[psbrown]

Hi I am a VBA newbie

I have a userform with 2 combo boxes that I want the user to sellect criteria for a autofilter in Excel, when the comboboxes are populated the user hits a command button to set off the filter.
I want the filter to return all values of that column if that combo box is left blank.

The filter is working when there is data in the first combo box and when there is data in the first and second combo box but does not work when there is no data in either the first combo box or both combo boxes.

I was going to do a "if then" selection on the values of the combo boxes to set off different code for the auto filter but I cannot seem to find code that will test the value of an empty combo box.

Can someone please help me with code for this test or alternativly a better way of coding the autofilter to allow blanks.

Thanks

Paul
The code is below with a few spurious msgboxes to track the execution

Private Sub CmdCalc_Click()
'Set up Variables for Worksheet
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("FrontSheet")

'Set Range
With wsSheet
Set rnData = .UsedRange
End With

'Assign Values in Form Combobox to variables
If IsEmpty(CmbBusMan.Value) Then
BusMan = "All"
Else
BusMan = CmbBusMan.Value
End If

If IsEmpty(CmbBusSec.Value) Then
BusSec = "All"
Else
BusMan = CmbBusMan.Value
End If

MsgBox (BusMan)
MsgBox (BusSec)
'If BusMan <> "All" And BusSec <> "All" Then

rnData.AutoFilter Field:=5, Criteria1:=BusMan
rnData.AutoFilter Field:=6, Criteria1:=BusSec
Application.ScreenUpdating = True
'End If

'Sheet1.splithours 'Set off procedure to calculate hours
'Unload Me
End Sub

 

[rmikesmith]

I've made a couple of modifications to your code. First declare BusMan & BusSec as follows:

Dim BusMan As Variant
Dim BusSec As Variant

Substitute this code for yours:

'Assign Values in Form Combobox to variables
If CmbBusMan.Value = "" Then
  BusMan = Null
Else
  BusMan = CmbBusMan.Value
End If

If CmbBusSec.Value = "" Then
  BusSec = Null
Else
  BusMan = CmbBusMan.Value
End If

rnData.AutoFilter Field:=5, Criteria1:=BusMan
rnData.AutoFilter Field:=6, Criteria1:=BusSec

This worked for me when tested. The Null values tell AutoFilter to use "All"


Regards,
Mike

 

[psbrown]

Thanks for that Mike it does seem to assign the null value correctly now.

But my "if then" statments do not trap the null value can you give more guidance?

If BusMan <> Null And BusSec <> Null Then
MsgBox ("here 1")
rnData.AutoFilter Field:=5, Criteria1:=BusMan
rnData.AutoFilter Field:=6, Criteria1:=BusSec
End If

If BusMan <> Null And BusSec = Null Then
MsgBox ("here 2")
rnData.AutoFilter Field:=5, Criteria1:=BusMan
End If

If BusMan = Null And BusSec <> Null Then
MsgBox ("here 3")
rnData.AutoFilter Field:=6, Criteria1:=BusSec
End If

If BusMan = Null And BusSec = Null Then
MsgBox ("here 4")
rnData.AutoFilter Field:=5, Criteria1:=BusMan
rnData.AutoFilter Field:=6, Criteria1:=BusSec
End If


Application.ScreenUpdating = True

 

[rmikesmith]

Perhaps I didn't make the point clear enough, but by assigning a Null value to the Criteria1 property you can dispense with the If..Then constructs. I tested the code as posted using two ComboBoxes containing separate criteria and selecting all combinations. The applied AutoFilter method returned the expected results in each case.


Regards,
Mike

 

[psbrown]

Thanks Mike that works great now, below is the whole code for anyone else who might need it.

Dim BusMan As Variant 'Business Manager
Dim BusSec As Variant 'Business Sector

Private Sub CmdCalc_Click()

'Set up Variables for Worksheet
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("FrontSheet")

'Set Range
With wsSheet
Set rnData = .UsedRange
End With


'Assign Values in Form Combobox to variables
'If the value of combo box field is empty assign the vealue as null
'to get the autofilter to work correctly
If CmbBusMan.Value = "" Then
BusMan = Null
Else
BusMan = CmbBusMan.Value
End If

If CmbBusSec.Value = "" Then
BusSec = Null
MsgBox ("Here val")
Else
BusSec = CmbBusSec.Value
End If

'Use autofilter to select criteria
rnData.AutoFilter Field:=5, Criteria1:=BusMan
rnData.AutoFilter Field:=6, Criteria1:=BusSec

Application.ScreenUpdating = True

Sheet1.splithours 'Set off procedure to calculate hours
'Remove autofilter
rnData.AutoFilter
'Unload Form
Unload Me
End Sub