Glad you got it working.

And I don't use the MS web based interface--so I don't have a guess about the
checkbox stuff.

Randy wrote:

Actually, I changed "<" to "<<" and it seems to be working fine. I wonder
why I'm not getting notified of replies even though I check the box?

Thanks again Dave!


"Dave Peterson" wrote:

If resp = vbNo Then
CIRCrit = ""
Else
CIRCrit = "<"
End If

.Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit

becomes:

If resp = vbNo Then
CIRCrit = ""
.Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit
end if

Randy wrote:

Dave,

I need the CIR filter to either show all cells if yes or only blanks if no.

Thanks for the help!

Randy

"Dave Peterson" wrote:

Maybe this will help. I changed some variables around--dropped the goto's and
line numbers and replaced a couple if's with select case (I find them easier to
read when there are multiple choices). And added a loop to get a good choice.

And I also changed the filter for the CIR stuff. Either it shows empty cells or
filled in cells. That may not be what you want.

Option Explicit
Option Compare Text
Private Sub Workbook_Open()

Dim ProjectSize As String
Dim resp As Long
Dim CIRCrit As String

Do
ProjectSize = InputBox("Please enter the size of your project:" _
& vbLf & " S for Small" & vbLf & " M for Medium" _
& vbLf & " L for Large" & vbLf & "or Cancel for All.")

'get rid of any leading/trailing spaces
ProjectSize = Trim(ProjectSize)

Select Case ProjectSize
Case Is = "", "s", "m", "l"
Exit Do
Case Else
MsgBox "Invalid choice. Try again"
End Select
Loop

With Worksheets("sheet1") '<--change the name here

'remove any existing filter
.AutoFilterMode = False

If ProjectSize = "" Then
'all selected, do nothing
Else
.Range("C:D").AutoFilter field:=1, Criteria1:=ProjectSize
End If

Select Case ProjectSize
Case Is = "l", "m"
resp = MsgBox("Is there resp CIR for this project?", vbYesNo)

If resp = vbNo Then
CIRCrit = ""
Else
CIRCrit = "<"
End If

.Range("c:d").AutoFilter field:=2, Criteria1:=CIRCrit
End Select
End With

End Sub


Randy wrote:

I've written this macro to ask for input and then autofilter two columns
based on that input. The first part works fine but the second part steps on
the first autofilter. Would one of you gurus mind taking a look at my code
and let me know what's wrong? I'm thinking there must be one simple piece of
code that I'm missing.

Thanks!

Randy

Option Compare Text

Private Sub Workbook_Open()

Dim P As String
Dim B As String

B = ("")

P = InputBox("Please enter the size of your project: S for Small, M for
Medium, L for Large or Cancel for All.")

10 If (P) = "l" Or (P) = "m" Or (P) = "s" Then
Columns("C:C").Select
Selection.AutoFilter Field:=1, Criteria1:=(P)

Else: If (P) = "" Then GoTo 99
If (P) < "" Then
P = InputBox("Please enter a valid selection: S for Small, M
for Medium, L for Large or Cancel for All.")
GoTo 10
End If
End If

If (P) = "l" Or (P) = "m" Then
A = MsgBox("Is there a CIR for this project?", _
vbYesNo)

If A = vbNo Then
Columns("D:D").Select
Selection.AutoFilter Field:=1, Criteria1:=(B)

Else: GoTo 99
End If
End If

99 End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson