Form If IsEmpty Syntax

[Gizmo]

Excel2003

I can't seem to get the correct syntax to get this to work.
The Process Runs New sheet isn't filtered by cbPMSelect even if a value is
input.
It works fine if i remove the If statement.
The user inputs ModSN, PM, and CH and the sheet is filtered correctly.
I want to be able to make the PM optional and have the Chart show all the
PMs if the PMSelect is blank.

Private Sub cmdAshNUSelect_Click()
'Open Process Runs New sheet and filter
If IsEmpty(cbPMSelect) Then
GoTo Line1
Else
End If
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="XXX"
With Sheets("Process Runs New")
.Activate
.Unprotect Password:="FtMrT2008"
If .FilterMode Then
.ShowAllData
End If

.EnableAutoFilter = True
.Range("A3:AS503").AutoFilter , Field:=4, Criteria1:="<>"
.Range("A3:AS503").AutoFilter , Field:=3, Criteria1:=cbModSelect.Value
.Range("A3:AS503").AutoFilter , Field:=6, Criteria1:=cbPMSelect.Value
.Range("A3:AS503").AutoFilter , Field:=7, Criteria1:=cbCHSelect.Value
.Range("C2").Value = cbModSelect.Value
End With
Line1:
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="XXX"
With Sheets("Process Runs New")
.Activate
.Unprotect Password:="XXX"
If .FilterMode Then
.ShowAllData
End If
.EnableAutoFilter = True
.Range("A3:AS503").AutoFilter , Field:=4, Criteria1:="<>"
.Range("A3:AS503").AutoFilter , Field:=3, Criteria1:=cbModSelect.Value
.Range("A3:AS503").AutoFilter , Field:=7, Criteria1:=cbCHSelect.Value
.Range("C2").Value = cbModSelect.Value
End With

cbModSelect.Value = ""
cbPMSelect.Value = ""
cbCHSelect.Value = ""
frmChartSelect.Hide

Sheets("Ash NU Chart").Activate
Sheets("Ash NU Chart").Visible = True
Sheets("Ash Chart").Visible = False
Sheets("ChuckCalChart").Visible = False
Application.ScreenUpdating = True
ActiveWorkbook.Protect Password:="XXX"
End Sub

 

[Norman Jones]

Hi Gizmo,

Try replacing:

If IsEmpty(cbPMSelect) Then

with

If Me.cbPMSelect.ListIndex = -1
'\\ Then no selection made

 

[Gizmo]

Hi Norman,

Made the change you suggested and get the same result.
Is my code in the correct module? It's in the Userforms module.

 

[Norman Jones]

Hi Gizmo,

=============
Made the change you suggested and get the same result.
Is my code in the correct module? It's in the Userforms module.
=============

Your code is corrrectly located in the
Useform module.

I have not sought to follow the logic of your
code beyond noting that you provide two
possible branches according to the evaluation
of an initial condition.

However your condition will always return False,
irrespective of the selection or non selection of
a ComboBox value, and, thereore, your code
will always process the first block of code.

My suggstion checks if a ComboBox selection
has been made and processes the first block (or
not) acording to such check. According to your
requirements, you may wish to reverse the logic
of the intial If ... Then condition.

Incidentally, as writen, your code would appear
to apply an autofilter in both blocks of code.

 

[Gizmo]

Hi Norman,

I figured out the problem.
I needed to add "GoTo Line2" at the end of the first block. The code was
working correctly. It was running the first block and then also running the
second block which leaves out the cbPMSelect filter.
Here's the completed code:

Private Sub cmdAshNUSelect_Click()
'Open Process Runs New sheet and filter
If Me.cbPMSelect.ListIndex = -1 Then
GoTo Line1
Else
End If
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="XXX"
With Sheets("Process Runs New")
.Activate
.Unprotect Password:="XXX"
If .FilterMode Then
.ShowAllData
End If

.EnableAutoFilter = True
.Range("A3:AS503").AutoFilter , Field:=4, Criteria1:="<>"
.Range("A3:AS503").AutoFilter , Field:=3, Criteria1:=cbModSelect.Value
.Range("A3:AS503").AutoFilter , Field:=6, Criteria1:=cbPMSelect.Value
.Range("A3:AS503").AutoFilter , Field:=7, Criteria1:=cbCHSelect.Value
.Range("C2").Value = cbModSelect.Value
End With
GoTo Line2
Line1:
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="XXX"
With Sheets("Process Runs New")
.Activate
.Unprotect Password:="XXX"
If .FilterMode Then
.ShowAllData
End If
.EnableAutoFilter = True
.Range("A3:AS503").AutoFilter , Field:=4, Criteria1:="<>"
.Range("A3:AS503").AutoFilter , Field:=3, Criteria1:=cbModSelect.Value
.Range("A3:AS503").AutoFilter , Field:=7, Criteria1:=cbCHSelect.Value
.Range("C2").Value = cbModSelect.Value
End With
Line2:
cbModSelect.Value = ""
cbPMSelect.Value = ""
cbCHSelect.Value = ""
frmChartSelect.Hide

Sheets("Ash NU Chart").Activate
Sheets("Ash NU Chart").Visible = True
Sheets("Ash Chart").Visible = False
Sheets("ChuckCalChart").Visible = False
Application.ScreenUpdating = True
ActiveWorkbook.Protect Password:="XXX"
End Sub