|
Form If IsEmpty Syntax
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 |
Form If IsEmpty Syntax
Hi Gizmo,
Try replacing: If IsEmpty(cbPMSelect) Then with If Me.cbPMSelect.ListIndex = -1 '\\ Then no selection made --- Regards. Norman "Gizmo" wrote in message ... 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 |
Form If IsEmpty Syntax
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" wrote: Hi Gizmo, Try replacing: If IsEmpty(cbPMSelect) Then with If Me.cbPMSelect.ListIndex = -1 '\\ Then no selection made --- Regards. Norman "Gizmo" wrote in message ... 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 |
Form If IsEmpty Syntax
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. --- Regards. Norman |
Form If IsEmpty Syntax
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 "Norman Jones" wrote: 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. --- Regards. Norman |
| All times are GMT +1. The time now is 11:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com