Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Auto-Refresh AutoFilter With Sheet Protection On


I found one of your old VBA entries that allowed me to automatically refresh an autofilter function, and it works great.... with protection off.

As soon as I protect the sheet so users can't enter data into the pages showing the autofiltered data, the autofilter kicks off.

i have set the protection to allow autofiltering, so it's something that is preventing the macro (for automatically updating/refreshing the autofilter).

I'd love to either fix the macro to "autorefresh autofilter" with protection on, or just enter another code that will disable all functionality on the worksheet. (workbook is set up with multiple sheets, first sheet is for all data entry, subsequent sheets are filtered data-trying to make them viewable only).


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
No Autofilter With Protection On
It is my understanding that the autofilter should still work on a protected sheet, but despite all of my efforts I have not been able to make this work. I have tried adjusting the settings on the protection to allow filters, formatting, and everything else I could think of all to no avail.

What am I missing here? This is the same on several different spreadsheets that I have put together.

View Replies!   View Related
Automatically Refresh Autofilter
I have a worksheet set with Autofilter. This worksheet is populated by formulas taking data entered on another worksheet.

I want the Autofilter for the first worksheet to automatically refresh each time I view that worksheet or as data is entered on the supporting worksheet.

View Replies!   View Related
Refresh Autofilter If Column Changes
I have an autofilter set up that hides all rows with 0 in the first column. This information is automaticaly filled in from information on another sheet. Using a worksheet code from a previous thread I have got all the worksheets to automatically refresh which is great.

Private Sub Worksheet_Calculate()

If Me.FilterMode = True Then
With Application
.EnableEvents = False
. ScreenUpdating = False
End With

With ActiveWorkbook
.CustomViews.Add ViewName:="Mine", RowColSettings:=True
Me.AutoFilterMode = False
.CustomViews("Mine").Show
.CustomViews("Mine").Delete
End With

The issue I have is that there are 52 worksheets (it's a weekly rota) and if any change is made anywhere on the sheet, all the worksheets then refresh which takes just over a minute (about 40 changes need to be made each day).

Does anyone know if there is a way that the information will only refresh on all sheets if changes are made in colums A? as this is the only information that is taken through to other sheets. Or is there another way of doing this?

View Replies!   View Related
Automatically Refresh AutoFilter When Cell Value Changes
I have a sheet named "Risk Register" and another named "Outside Residual Risk Threshold". I need an event macro on the worksheet "View code" section, so that any time a value changes in column AF on "Risk Register", the filter on rows 8:39 in "Outside Residual Risk Threshold" is refreshed. Both sheets are protected.

The code I tried in the "view code" or "Outside Residual Risk Threshold" was as below...

View Replies!   View Related
Refresh AutoFilter Automatically When Any Change In Filtered Column
I read your reply to the thread below, and used your Worksheet_Calculate routine from it. It only works for the first sheet in my workbook. I have multiple sheets in the workbook, and when I run the routine on an activesheet other than the first sheet in the workbook, it doesnt work.

Is there a way to get this routine to work on a worksheet other than the first one in the book?


View Replies!   View Related
Auto Refresh Data In Pivot - Macro
I see some code on the forum on how to automatically refresh pivot tables in excel. I know to press alt + f11, but where do I go from there to put the code in? Also, what if I have several pivot tables in the spreadsheet, is there anyway to refresh all of them automatically?


View Replies!   View Related
Detect Auto Refresh In VB And Run A Macro
My Excel spreadsheet is linked to an Access database to Get external Data. When the data is periodically refreshed ,while the spreadsheet is open, I need to automatically detect it so that a macro can be run each time to format the new data.
The Excel data is not a pivot table. The spreadsheet is for display of data only (no user updating).

View Replies!   View Related
Auto Refresh Published HTM HTML
I have a worksheet which holds data and formula (obviously) and publishes only the data in a htm file to be viewed by others using a browser (IE). I want the htm file to auto refresh for the viewer.

View Replies!   View Related
Enable Auto Refresh Of Web Queries When File Opened
I have a special use PC which will power a projector 24/7. When it reboots I want it to load Excel, open a spreadsheet, and then connect to the web to get data using web queries.

My problem is that I get a dialogue box each time the spreadsheet is opened: "Enable Automatic Refresh." I need to remotely reboot the PC from time-to-time so I can't click the button.

I have set my security levels to low and that does not help.

View Replies!   View Related
How To Make A Data Miner Auto-refresh A Page That Fails To Load
I'm in the process of continuing to build this babe (very slowly, as I have limited programming knowledge), and I wondered if there was a way to modify this code to make it refresh the data page if a server's page fails to load.

Part of my code is something like this, the first part being just the pseudo log on script.

Especially when there are over 50 pages to grab, the chances of one of the pages failing to load is VERY high, and this macro stops at the "Refreshbackground=False" command.

Is there a way to trigger a "refresh" once it fails?

View Replies!   View Related
Automatically Refresh The Data In Sheet 2 And Put It In Sheet 1
I have 2 sheets.

Sheet2: it contains data that is being imported from an external link with the following info: ....

View Replies!   View Related
"refresh" The AutoFilter With One Click
Is there a way to "refresh" the AutoFilter with one click (even a short-cut key would work for me)?

Basically, I have filters set on a spreadsheet, and I am filling items, and I would like the filter rules to apply to all the new data periodically.

View Replies!   View Related
Password Protection Through A Sheet
I got this form RoyUK and was wondering if anyone know why it comes up with a debug here

Option Explicit
Dim sPw As String
Dim i As Long
Dim r As Long
Dim iChk As Integer

For Each rCl In .Range(Cells(r, 3), Cells(r, Columns.Count).End(xlToLeft))

View Replies!   View Related
Password Protection A Sheet
Is there a way to password protect a sheet so that you have to enter a password just to access it. I dont want most users to be even able to view one sheet unless they have the password.

View Replies!   View Related
2002 Sheet Protection
Is it true that the sheet protection in excel is practicaly none ?

Not even excel 2002 (XP) prevents simple cracking software from removing sheet protection.

Are there any master keys that cause that ?

Is there a way to protect a sheet with strong encryption ?

View Replies!   View Related
Import Data From Access Table To Pivot Table - Enable Auto Refresh
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).

Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.

Any other solution to enable the automatic refresh on open the excel workbook?

Or Access can overwrite the exist file or save it as another file name with timestamp ?


View Replies!   View Related
Sheet Protection Causes Macro Error
I've been using the attached spreadsheet to record daily sales for a while now, however, we've recently intorduced a new product and so I've had to add a new column to record daily sales of it.

On the spreadsheet is a 'New Week' button. Pressing this deletes all entries and moves the week numbers on by 1, so giving me a blank template each week. However, after the addition of the new column I'm now getting a bug error message. I've adjusted the code to take account of the new column but I'm still getting a bug error. When I press 'debug' it's highlighting something I have changed so I'm a little confused why I'm getting the bug error. Would someone have a look for me. I only seem to get the bug when there is protection on the sheet, I need protection. The password is PHUKET38

View Replies!   View Related
Macro: Protection From Same Password To All Sheet
i have a spreadsheet with about forty different sheets and all of these sheets are protected with the same password, and wheni come round to doing an amendment to the spreadsheet i have to unprotect and then re protect each sheet in turn, very time consuming

each sheet is naturally named different


View Replies!   View Related
Sheet Protection Problem With Groups
I have an excel workbook I'm working on, and it needs to be protected. I have some groups that can be expanded or hidden, but once the sheet is protected you can no longer use the little button to hide or expand the group.


View Replies!   View Related
Show Status Of Sheet Protection
I have a workbook with about 70 sheets, some are specific for data entry, others for data review.

I share the workbook with my data entry team, and lock the review sheets so that no one can modify the data on them or change the formating. End users review the final protected sheets for the information that is appropriate to them.

I need to track the status of the protection of each sheet, such that I can edit the sheets as needed, then protect / lock them once I am done.

Is there a way to indicate, in a cell within the indvidual sheets, the status of protection for that sheet?

For example, if the sheet is protected, the Cell would read "LOCKED" and if the sheet is not protected, the cell would read "EDIT".

View Replies!   View Related
Automatically Refresh A Sheet Every Hour
I have a workseet that is basically a massive list of orders that have been placed. I have some conditional formatting based on cells that use the today() function to work out how long (in days) since the order was placed.

Trouble is, the spreadsheet is always open, and is never closed down... so it does not refresh automatically. So orders that should go red to indicate a week since they've been placed, don't!

I don't want to rely on people refreshing the screen. Ideally, I would like it to refresh once a day at 1 minute past midnight, or even every hour...

View Replies!   View Related
Remove Sheet Protection From Every Workbook In Folder
I have tried to cobble together a macro to remove the sheet protection from every sheet in every workbook in a folder. Not surprisingly, it is not working. Unfortunately, I have reached the limits of my VBA abilities.

View Replies!   View Related
Copy Cells Removes Sheet Protection
I have hidden columns on a sheet.
I protect the sheet with a password before I send out the file to a 3rd party. However, it seems that all a user has to do is select the sheet cells, copy and paste onto another sheet or file. The protection is nonexistant and the user is able to unhide the columns.

Am I missing something?
Is there a way to "really" protect a sheet so that a user is unable to unhide confidiential columns? Preferably a method without VBA code.


View Replies!   View Related
Event Macro Causes Sheet Protection Prompts
One of the things it does throughout the course of the run is unprotect the worksheet, do what it needs to and then reprotects the sheet (using a password). On the second run, the sheet protection input box is shown and I am required to input the password before the code will execute.

The code that I have written uses an Add-in that pulls data from a database. As part of that retrieval, the other add-in protects the sheet without a password. Would that be causing my error?

View Replies!   View Related
Cell & Sheet Protection Basics
I have 2 spreadsheets: in a Sheet1 I keep some values and in a Sheet2 5 names of users with special rights (range A1:A5). I want to have sure that anybody will change/delete that names without password. Only the user "Administrator" can do that(I check the name of the user using "Application.UserName"). Additional question: is it possible to still protect those cells if somebody turn off macros during opening file?

View Replies!   View Related
Refresh Master Sheet To Reflect Changes In Other Sheets
I am running a macro to create a combined master summary sheet from data in several other sheets contained in one workbook. That is working fine. I need to create some kind of refresh macro so that when data in these sheets change the master sheet will change and update automatically.

View Replies!   View Related
Being Able To Refresh Data- So That When I Hit Refresh It Gets Info Frm Another File
i have an excel spreadsheet with 27 or so workeets. it contains sales figures in it.
I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.

View Replies!   View Related
Setting Protection Change To Rest Of Sheet After Last Row
I have a form that will be populated with different numbers of records. I need the records to be locked, but the remaining rows (if the user needs to add new records) to be unlocked. Can I use some sort of range.end function for this?

View Replies!   View Related
Modify Macro To Check And Turn Off Sheet Protection.
I am using the macro below to import every spreadsheet into a folder. I would like to modify it to check the sheets it is importing for sheet protection. If the sheet protection is turned on, I would like it to turn it off. The password for the sheet protection will be "PIR".

View Replies!   View Related
User Allow To Format The Cell And Text In Protection Sheet ?
how to allow formating a text and cell in protect sheet ?

View Replies!   View Related
Automatic Refresh Pivot Table In Protected Sheet
In sheet1 (SA Awards) I have the source table for my pivot table in Range ("A1:G50"). In sheet2 (Team Listing) my pivot table is located in Range("K2:S13")
When I make changes in sheet1 I need my pivot table to update, I recorded a macro to refresh, however have only got it to work via a button & only if Sheet2 is unlocked

Sub PivotTableUpdate()
Sheets("Team Listing").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
Sheets("SA Awards").Select
Range("B2").Select
End Sub

1. How can I get this to work in the Worksheet_Change Event?
2. How can I password protect Sheet2 & still have it work?

View Replies!   View Related
Pivot Refresh After ODBC Query Complete Refresh
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.

I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)

View Replies!   View Related
Sheet Protection Conflicts With Code To Autosize Row Height
When turning on sheet protection, I get an error with the following code that I'm using to make sure rows adjust their height when lines wrap.

Rows.EntireRow.AutoFit

What can be done to eliminate this conflict? All of the cells I want available for editing are unlocked.

View Replies!   View Related
Worksheet Protection From Editing And At The Same Time Allow All Data Source Links Within The Sheet To Be Refreshed
how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.

View Replies!   View Related
Protected Sheet - Macro To Unprotect, Refresh Sql Query & Re-protect Fails
I have a protected worksheet containing an SQL query. I have two cells on the worksheet unlocked so that users can enter fresh parameters to feed the query.
Because I have to use MS Query to interrogate our SQL server (current IT decree), the query won't support parameters directly when the query cannot be represented graphically - so I have to manually edit the sql command text and then refresh the query - or at least that was why I wrote the code below

The problem: My macro won't unprotect the sheet before it calls the SQL.it therefore won't update the sheet to detail the DSN, command text and number of parameters (latter should be 0) - info just used to verify queryit won't run the query / return the data as the sheet is still protected Comment: I have had to remove sheet protection in order to allow the sql to execute and return data. If protection is removed, the query does execute and return data ( changes in the "parameter" cell contents do cause the query to be correctly modified)
I could comment out the debug info but the query still doesn't return data if the sheet is protected.

Activesheet And worksheets(ndx) where ndx has been defined As ActiveSheet.Name
I apologise If my post Is difficult To follow - especially the code.

Sub AgedStockParameters()
nmb = ActiveWorkbook.Name
ndx = ActiveSheet.Name
Workbooks(nmb).Activate
Worksheets(ndx).Activate
ActiveSheet.Range("A1").Select
WkshtQryCon = Workbooks(nmb).Worksheets(ndx).QueryTables(1).Connection
ActiveCell.Value = WkshtQryCon
Range("A2").Select
WkshtQryFld = Workbooks(nmb).Worksheets(ndx).QueryTables(1).CommandText
ActiveCell.Value = WkshtQryFld.......................

View Replies!   View Related
Auto Duplicate Row Contents From One Sheet To 2nd Sheet If Column Contains Set Words
I want to end up with is when I type text in any row of the 'Master' sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (referred to by text entered in column O). For example row 5's contents from the Master sheet would duplicate into the sheet named 'Accommodation' as O5 contains the word Accommodation.

In addition, should the details ever change in the Master, this would also need to be reflected in the individual sheets. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie if row 5 had the first occurrence of the word Accommodation in column O, the contents of that row would be placed into the next available first blank row of the Accommodation sheet, which in this case would be row 2 as the headings are on row 1.


View Replies!   View Related
Auto Duplicate Row Contents From 1 Sheet To 2nd Sheet If Column Contains Set Words
I want to end up with is when I type text in any row of the Master sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (as per column O). For example row 5's contents from the Master sheet would duplicate into the sheet named Accommodation. In addition, should the details ever change in the Master, this would need to be reflected in the individual sheets also. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie row 5's contents to be placed into row 2 of the Accommodation sheet seeing as it's the first occurrence, and row 6's would go into row 3 etc.

View Replies!   View Related
Create New Sheet For Each Row Of Another Sheet Auto Populate Data.
i have a sheet called 'sample database'. it consists of 56 columns, each with a specific title in row 1. i.e. name, surname, mobile_number, and so on.
from row 2 onwards the data has been populated for roughly 200 rows.

i have another 'Capture Sheet' which has the same titles as 'Sample database' except it's in a different format. its a printable form that is given to new employees when they start. once they have completed it it gets captured into the 'Sample Database' sheet.

my wifes boss now wants 1 new sheet for each row in the 'sample database' sheet based on the format/layout of the 'capture form'.
how can i get the 'capture form' to auto populate the data from the 'sample database' sheet and create a new sheet for each row?
i know this is possible i just dont know how to do it.


View Replies!   View Related
Autofilter Locks Up Sheet!
Autofilter is turned on in a weekly spreadsheet that I receive. Functionality to move around by arrows or page up/down works until I select something out of an autofilter drop down. Screen locks in place. Can't arrow down or up (but can go left/right). Page up/down is broke also.

If I exit and re-enter it works until I filter. Scroll lock does nothing.

View Replies!   View Related
Protected Sheet & Allow Use Of Autofilter
I've locked certain columns on my worksheet so that users cannot overtype target dates etc. I've password protected the worksheet.

The password protection means that for some reason the users can't use the autofilters that are on the header row.

Can someone help me solve this problem; I still need the locked cells and password protection on the sheet but the success of the sheet depends on users being able to filter for specific rows using the autofilter....

View Replies!   View Related
Unable To Use Autofilter On A Protected Sheet
i am uable to use autolfilter when protecting sheet via vbacode even though i have checked the autofilter box to be able to use it whilst protected, why does it work when i manually do protection but does not work via vba?

View Replies!   View Related
Autofilter Copy Data To Other Sheet
Trying to filter list and copy filtered data to another sheet in the same workbook.

I'm receiving this error:
Run-time error '438'
Object doesn't support this property or method

Worksheets("Data"). CurrentRegion.Copy _
Worksheets(" Totals"). Range("A1")

Sub GetTotals() ..............

View Replies!   View Related
Autofilter Will Not Always Select Entire Sheet
I have a workbook that uses Autofilter.
Column A has data. Column B is hidden, has no data, and is empty.
Colmn C has data, on through the rest of the sheet (to Column AB).

Why column B has no data is unimportant for now. It, however, must maintain its existance and cannot be deleted.

When I select the autofilter sort button on column A, it selects only that column, and not the rest of the sheet. Data is subsequently scrambled.

When I unhide the empty column B and select the sort button on column B, it selects the entire sheet. Data remains intact.

When I select the sort button on column C, it selects the rest of the sheet, but not column A (with data), or B (with no data). Data is subsequently scrambled.

View Replies!   View Related
Autofilter - Show All On Shared/protected Sheet
I have autofilter enabled on the header row across the top of my worksheet. Is there a quick line of VBA I can use to toggle EACH one to (ALL). This would act as a "Reset" in my spreadsheet and display all content. I had a solution that worked, until I made the workbook shared / password protected.

View Replies!   View Related
Auto-add Sheet Name To Summary Sheet
I have a summary sheet with a numerous data sheets to the left of it in a single workbook. Every time I add a new data sheet I have to type it's name on the summary sheet. How can I automate this?

View Replies!   View Related
Copy Row From Autofilter & Paste To Another Sheet
I have 2 sheets. The first sheet contains rows of data that I wish to search (Query Results) through and find matching data. The second sheet is the source of the data I want to find (Notifications). I want to take the number located in column A on sheet 2 and then use it to auto filter the rows on sheet 1. With the range that is left visible on sheet 1 I want to select the first row (can offset(1,0) to remove the headers, and paste the selected row back in to Sheet 2 ontop of the row where I sourced the original number to filter by.

Once the item is pasted I then want to pick up the next number from Sheet2 to repeat the process until all numbers have been processed in Sheet2. I have been going round the block on this for the past few days!

Sheets("Query Results").Select
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Selection.AutoFilter Field:=1, Criteria1:=vFroID
Selection.AutoFilter Field:=15, Criteria1:=vReqData
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
On Error Goto 0
End With
vDestRow = "A" & vLoopCount
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Proposed Notifications").Range(vDestRow)
End If
vLoopCount = vLoopCount + 1

View Replies!   View Related
Autofilter Col A,copy Col C To New Sheet, Name New Sheet With Criteria Value
In my "MAIN" Sheet. Column A starts at 12th row and contains ONLY Strings. I would like to Autofilter Column A for every unique String (criteria) in Column A (from 12th row to 1000th row). Copy the thus filtered (i.e. visible) cells from (ONLY) column C (for every criteria). Paste the copied cells to a new sheet, starting from A4 of the new sheet. Give the name of the Criteria as the name for the new sheet. Come back to "MAIN" sheet and show a pop up "Sorted".

View Replies!   View Related
Autofilter Code Failing On Shared Workbook & Protected Sheet
I have a protected worksheet with some macros running on it, and I have been requested to make the workbook shared. Trying to implement this, I keep getting the standard "1004 You cannot use this command on a protected sheet" error when applying an autofilter.

Here is what is making this problem a little tricky:

- As the workbook is shared, I cannot do Protect UserInterfaceOnly because you can't change the protection settings on a shared workbook without unsharing it. Needless to say, I cannot unshare the workbook.

- When I protect the sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both are on. Still, I keep getting the error.

The failing line of code is:

shData. Range("_filterDataBase").AutoFilter lngField, strArg

where shData is the codename of the sheet, lngField is the number of the field and strArg is the filtering criteria.

My guess is, that the error comes from the fact that (for some silly reason) in VBA the .AutoFilter method is used to create a new filter, not only to use an existing one. The members of Sheet.AutoFilter.Filters collection are read-only, and cannot be used to modify the existing filter.

View Replies!   View Related
When Query Refresh Its Say "Enable Automatic Refresh"
I've got a spreadsheet with a few queries to extract data into my sheet. Whenever I open the spreadsheet, I get a "query refresh" dialog box, asking if I want to "Enable automatic refresh". I keep clicking on the "Enable" button but I have to keep answering the question for each open. Isn't there a way to set "yean - ok - refresh the data" so that I don't have to keep saying "yes"?

View Replies!   View Related
Run-time Error '50289' :: Project Protection / Module Protection / Module Visibility
I recieve an error :

Run-time error '50289':
Can't perform operation since the project is protected.

When i try and run my code.

The code does add parts to modules and workbook events, but I need the code to be protected.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved