Input box sorting?

[Ewing25]

What i want to do is when someone opens the excell file it asks them for
their card number. When they click okay it filters column A in the "expense"
tab just the same way if you auto filtered and used the drop down list.

The only thing is i dont want it to allow them to filter again after they
have clicked okay.

I have no idea how to do this any advice would be great.

Thanks,
Alex

 

[Office_Novice]

'*************************
'AutoFilter by inputbox *
'*************************
Private Sub Sort()
Dim Message, Title, MyValue
Message = "Please Enter Card Number"
Title = "Title" '<--Change
MyValue = InputBox(Message, Title)
If MyValue = "" Then
MsgBox "You Need to Enter your card Number", vbCritical, "Error"
Exit Sub
End If
Range("Headers").AutoFilter '<--Change ("Headers")
Range("Headers").AutoFilter Field:=1, Criteria1:=MyValue
PrintPreview '<-- Or whatever you want to do here

End Sub

 

[Ewing25]

Ok i tried inputing my informaion and it gave me an "application defined or
object defined error".

Heres my code.

Dim Message, Title, MyValue
Message = "Please Enter Last 5 Digits of Your Card Number"
Title = "Title" '<--Change
MyValue = InputBox(Message, Title)
If MyValue = "" Then
MsgBox "You Need to Enter your card Number", vbCritical, "Error"
Exit Sub
End If
Range("Expense").AutoFilter
Range("Cardholder").AutoFilter Field:=1, Criteria1:=MyValue
PrintPreview

End Sub

 

[Office_Novice]

Assuming ("Cardholder") is a named range in your worksheet

Range("Cardholder").AutoFilter
Range("Cardholder").AutoFilter Field:=1, Criteria1:=MyValue

 

[Office_Novice]

Paste this into your project...

'*************************
'AutoFilter by inputbox *
'*************************
Private Sub Sort()
Dim Message, Title, MyValue
ActiveSheet.Unprotect
Message = "Please Enter Card Number"
Title = "Title" '<--Change
MyValue = InputBox(Message, Title)

If MyValue = "" Then
MsgBox "You Need to Enter your card Number", vbCritical, "Error"
Exit Sub
End If

Range("A1").AutoFilter
Range("A1").AutoFilter Field:=1, Criteria1:=MyValue
ActiveSheet.Protect '<-- Or whatever you want to do here

 

[Ewing25]

oops! forgot to make it a range. It works great thanks!

One last question however is there anyway to make it so the drop downlist
doesnt appear anymore when they have finished with the macro?

 

[Office_Novice]

Range("A1").AutoFilter Field:=1, Criteria1:=MyValue, visibleDropDown:=False

 

[Ewing25]

Again perfect!

When using "activesheet.protect" how do i make it password protected
automatically?