Autofiltering With Password?
Dec 11, 2006
I have a worksheet that is password protected. The userforms used to input all the data have an initialize event that unprotects the sheet to input the data and then before protect the sheet upon closing the userform. This works perfect so that input data cannot be changed once entered; however, the problem I have is that I need to allow autofiltering so users can sort through their input data. I have a command button that unprotects the sheet and places the autofilters in the correct locations, but once this is activated the worksheet then is open for modifications by the users. Is there a way that I can allow autofiltering while still keeping the data protected? If .
View 9 Replies
ADVERTISEMENT
Feb 26, 2006
How to speed up Autofiltering? Excel 2003 sp2 hangs up for 10 minutes after
I choose a record in the Autofilter.
View 13 Replies
View Related
Jul 7, 2006
I am trying to make it so a user can input a variable such as a date and then press a button which will autofilter a lot of data by what the user entered. The problem is some of the data I want users to be able to filter by needs to be filtered using "begins with" or is "greater than" or "less than". I can only make it so I can filter by "equals" when I try to do for example "begins with" using "*" it doesn't work out. Here is an example of my code
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:= Range("Sheet1!B4").value, Operator:=xlAnd"
This code will auto filter data by what ever is entered into B4 on sheet 1. I can't figure out how I can filter with the "begins with" function or "less than" function using a variable.
View 5 Replies
View Related
Jan 6, 2010
I have a spreadsheet with approx 12000 rows.8 columns. it represent material in our inventory and each row is a specific records that contains a lot of info. I need to be able to have a field in a top pane (could be top row i guess. or a small form) where i can start typing text and as i am typing, the spreadsheet auto filter the rows to display only whatever fit my parameters i am typing. a few example of what i will type in are:
bearing*2217
valve*gate*3*150
asco*8344
as you may have guess, "*" is a wildcard. The spreadhseet are generated using crystal reports over night, so i am not sure how i can or should use macros or add in for this..
View 5 Replies
View Related
Dec 11, 2006
When I protect my sheet and have the option to allow the users to autofilter the sheet, how come it doesn't work? I want to allow people to autofilter the data in the worksheet but not modify the contents of the cell.
View 9 Replies
View Related
May 14, 2009
I have a large spreadsheet which has a column of dates (dd-mmm-yyyy) in it. I would like to be able to filter by month regardless of dd and yyyy. I would like to see all quotes in Jun.
View 2 Replies
View Related
Jul 25, 2014
I am able to autofilter a sheet to show only rows with a date in Column U but no "x" in Column BC. Now I want to be able to put an "x" in BC in only the filtered rows. The purpose is if someone enters a date in Column U, the macro will show only those rows minus the rows that have already had an "x" added to BC.
For the most part I've gotten this to work using but it will also put an "x" in Column BC, row 3001 on down.
How can I get it to stop at the last row?
Cells.AutoFilter Field:=21, Criteria1:="<>"
Cells.AutoFilter Field:=55, Criteria1:="="
Columns(55).SpecialCells(xlCellTypeVisible).Value = "X"
View 5 Replies
View Related
Mar 2, 2012
I need to delete entire row of the result of autofiltering i.e. the visible rows only
If I do this manualy it works but if I record this on a macro, it records the absolute cells range I pick, when in fact the result of the filtering is different every time.
Is there a way around this with VBA?
I've Seen Copy paste in this forum but in my case i need to delete the entire row.
View 1 Replies
View Related
Aug 9, 2007
I am trying to figure out how to save a worksheet's autofiltering settings and be able to re-create those settings.
I would start off with a spreadsheet with a few Autofilters on. I would like to make a macro that will record the initial Autofilter settings, then the macro would do a bunch of stuff that would need to 'Show All'. After these actions take place, I would like to be able to return to the initial Autofilter settings. I'm not even sure which VBA command should be used for this sort of thing.
View 9 Replies
View Related
Oct 4, 2005
I have a spreadsheet which I distribute weekly to certain people.
It is basically a top level view of incidents, which shows the following.
Sheet Number 1 is :
Person - Count
Joe Bloggs - 42
Fred Bloggs - 12
And so on.
This shows me a summary of how many incidents per person have been logged.
Sheet Number 2 is the actual raw data for that count, so it shows every line.
So in the above example, there would be 42 lines for Joe and 12 for Fred.
What I'd like is to put a hyperlink on sheet 1, whereby if you click onto Fred's name, it will take you to sheet 2 and autofilter to only show Fred's records (i.e 12 of them)
View 9 Replies
View Related
May 27, 2009
When opening a new Excel file, it is asking me for a password to an old excel file that was password protected, which I have deleted from my computer. It is still asking for this password every time I open a new or existing file. When I hit cancel it lets me open the file.
View 11 Replies
View Related
Mar 21, 2007
I have a spreadsheet, which has certain worksheets that are password protected. I need to make changes to some of the formulas, and the VBA modules, however I cannot remember the password!! Is there a way of identifying the password??
View 3 Replies
View Related
Nov 28, 2008
I am havng problems with the belwo macro code -
Range("G7").Select
Selection.Copy
Workbooks.Open Filename:= _
"Q:PublicPAYMENTS Q&RREPORTSSuspense Activity BUSINESS2008 - Suspense BUSINESS - Activity Reports2008 Avon.xls"
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
When i open the workbook it requires me to insert a password, i tried to type this but excel will not follow keystrokes, i then inserted the password in cell G7 which i then copy and paste, which you can see the copy at the beginning of the macro.
Is there a way where you can get it to insert the password so i can open the workbook in a macro.
What im trying to do is save 26 suspense spreadsheets by just clicking on a macro.
View 9 Replies
View Related
Jan 12, 2007
I have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.
Private Sub CommandButton1_Click()
ActiveSheet.Unprotect "rainforest"
Columns("O:O").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1
Selection.AutoFilter
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
View 3 Replies
View Related
Mar 9, 2009
how to set password for VBAProject?
i know to protect the spreadsheet
i want to password protect my coding in the VBA Project module ...
View 10 Replies
View Related
Dec 14, 2006
When using the a file with VBA based on a button is there any way to add a password to that file at the same time?
That way it can be opened by anyone?
The code below works fine but the user can open the file in fact anyone can once they press the command button I want to add a password so when the file is reopened it requires a password?
Is this some thing vba even does?
Private Sub CommandButton1_Click()
Const PTHNAME As String = "C:Feedback"
Dim FileNameWithDate As String, x As Integer
FileNameWithDate = Format(Date, "yyyymmdd")
x = 1
If Dir(PTHNAME & FileNameWithDate & ".xls") = "" Then
ActiveWorkbook.SaveAs Filename:=PTHNAME & FileNameWithDate & ".xls"
Else
Do Until Dir(PTHNAME & FileNameWithDate & x & ".xls") = ""
x = x + 1
Loop
ActiveWorkbook.SaveAs Filename:=PTHNAME & FileNameWithDate & x & ".xls"
End If
SendMailtoBDTT
SendMailtoPerson
End Sub
View 9 Replies
View Related
Apr 27, 2009
First, I know passwords are easy to break. The point is only to keep the average Excel user in a dept of 3 from accidentally damaging the data.
Normally I hardcode passwords to view xlveryhidden sheets.
But this workbook is for a dept head who has standard password(s) he uses to open or edit a file. I would like to capture these passwords and make it the same password(s) to view a hidden sheet.
This way if he ever changes the password to open/edit it will automatically change the password to view the hidden sheet(s).
View 7 Replies
View Related
Apr 27, 2009
How to set a password before executing any action.
For example, I have "cancel" button in my userform. If i click "cancel" it unloads the form. Instead of that i need a password prompt. so when i click the "cancel" button ,it should ask for a password. only if i provide the correct password then only the form should be unloaded.
View 6 Replies
View Related
May 5, 2009
when i run this marco, i get a Password msgs box appear, now is there away of running it so that it does not ask for a password, I have tried to unprotect the sheet with a password which works, but it never re-protected it self,
I don't know that much about VBA and my programming knowledge is very limited. I dont want the code re-written, as its someone elses work and it does what i want, i just need to know if an extra line can be entered to stop the password msg box. I.E. it needs to run without asking for the password
View 4 Replies
View Related
Jun 10, 2009
I have a 3 2 user forms, plus a calandar pop-up user form. One is a Log In Screen with a user id combo box and a password textbox referenced to ranges in a very-hidden sheet.
My second form is a spend tracker with mulitple fileds, text boxes, combo boxes, etc.
One of the fields in the spend tracker is user name, what I would like to do is when a user enters their user name and password the entered user name as long as it matches one in the database be populated in the user name field and not allow it to be edited. Is there a way to do this, if so, what is the code and where do I put it.
I will attach my file so you can check it out. Log in: tester
Password: test01
Programmers Access Code is 1234.
View 9 Replies
View Related
Sep 1, 2009
I was working on a workbook that unhides certain sheets depending on who's logged in. It seems to work fine. However, after it's run (and sheet2 has been unhidden), when I go to unprotect the workbook, the password ("test") no longer works.
View 5 Replies
View Related
Sep 30, 2009
I have an excel spreadsheet that has password to open it as well a read only prompt. What I would like to happen is after the user has typed in the password, clicked ok, and then clicked either "yes" or "no" for read only or not that a specific named macro immediately is run.
View 4 Replies
View Related
Dec 30, 2009
I have a workbook where each tab represents a 'base.' You can select which base to view by selecting an option from a drop down box on a menu page.
The data for each base is of a confidential nature so I would like to password protect each tab so that it asks for a password BEFORE the page opens. I don't think Excel has the functionality to do this unless I make the pages 'Very Hidden'?
View 8 Replies
View Related
Feb 2, 2010
I have givien a password to a workbook through the save as meathod. How do I remove this password so anybody can just select the workbook to open it?
View 2 Replies
View Related
Oct 25, 2011
I have a excel document with 5 tabs (sheets) and, because different people work with a different tab (sheet), I want to lock each tab (sheet). I want the tab to be visible on the tab bar but when you click on it you will not be able to see the content of that tab (sheet) until you provide the right password (each tab with a different pasword).
View 2 Replies
View Related
Feb 4, 2012
how do i put the password of a file into a macro ?
View 4 Replies
View Related
Aug 28, 2007
I use the following piece of code to show/hide certain worksheets in a workbook. To access the hidden sheets, a command button runs the code. It works very well, except that the password is openly displayed in the message box (as opposed to returning asterisks for the typed characters).
Sub togglesheets()
Dim Ws As Worksheet
Dim strPassword As String
strPassword = InputBox("Enter Password")
If strPassword "Password" Then MsgBox "Wrong Password": Exit Sub
Application.ScreenUpdating = False
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name = "Apr-Sep" And Ws.Visible = xlSheetVisible Then
Ws.Visible = xlSheetVeryHidden
ElseIf Ws.Name = "Apr-Sep" And Ws.Visible = xlSheetVeryHidden Then
Ws.Visible = xlSheetVisible
End If..............
View 9 Replies
View Related
Sep 10, 2008
I'm trying to use the Tools|VBA Project Properties dialog box to stop folks from unhiding an xlveryhidden sheet, but even though I check the "lock" checkbox and input passwords in the password and the password verify boxes, Visual Basic fails to stop me from selecting the sheet in question from an Alt-F11 spawned VBA, and editing away.
View 9 Replies
View Related
Dec 9, 2008
is it possible to password protect a tab (worksheet) within a multiple worksheet file.
View 9 Replies
View Related
Jul 4, 2009
However, one problem in particular has gotten me stumped after a few days of working on it and I was hoping someone might be able to help me understand how to crack it. The question is from chapter 7, question 12(a).
"All passwords in your company's system must be 8 characters long, must start with an uppercase letter, and must consist only of uppercase and digits -- no spaces.
Write a sub to get a user's new password. It should use an input box, embedded within a Do loo, to get the password. The purpose of the loop is to check that a valid password has been entered."
Below is my latest try. It will technically work for a password of 4 characters instead of 8, but it already runs pretty slow and I have a feeling that there must be a better way to do this.
Sub Password()
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim pw As String 'pw is the password
Dim Length As Integer
'48-57 = 0 To 9, 65-90 = A To Z, 97-122 = a To z
Do Until isValid = True
pw = InputBox("Please enter a valid password.", "Password")
Length = Len(pw)
For i = 65 To 90
For j = 48 To 90
For k = 48 To 90
For l = 48 To 90
If pw = (Chr(i) & Chr(j) & Chr(k) & Chr(l)) And Length = 4 Then
isValid = True
Exit For: Exit For: Exit For: Exit For
End If
Next: Next: Next: Next
Loop
End Sub
View 9 Replies
View Related
