Filter Locked Worksheet Conflict - 2000 Vrs. 2003
Feb 1, 2007
I recently launched a model that uses filtering on protected worksheets. The model was developed in Excel 2000, and everything works perfectly for the users who are also on 2000, but my Excel 2003 users get an error on opening the workbook.
The error asks for the password to unlock the sheet and is difficult to clear. Users have to hit Cancel 8 times to clear the error, at which point, the model appears to function perfectly. (There are 12 worksheets in the model, 2 with filters, all locked and password protected.)
The follwoing code is in the 'ThisWorkbook' module:
Private Sub Workbook_Open()
With Worksheets("Initiatives")
.Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True
End With
Because the error occurs when first opening the model but not at other times, I'm thinking that there's something in the Workbook_Open procedure that is causing the error.
View 9 Replies
ADVERTISEMENT
Jun 15, 2009
I've created quite a few workbook/worksheets in excel 2007 compatibility mode that are data entry intensive. When protecting these worksheets, you have the option of allowing the user to "select locked cells". I invariably uncheck this option because the user doesn't need to interact with these cells.
However, several of my users are running Excel 2000 and the same option is not available when one protects a worksheet in Excel 2000. Is there a way to do that in VBA for these Excel 2000 users such that when they click on a locked cell, the cursor will not respond to that cell?
View 5 Replies
View Related
Dec 3, 2009
With the kind help of this forum, I put together a nice business tool that has a fair amount of automation via macro buttons, etc. It works great in XL 2007 but some users are having issues in 2003. I appreciate this is quite general but are there significant conflicts / differences with coding in these two different versions?
View 6 Replies
View Related
Nov 23, 2006
Excel 2000 spreadsheet everything is fine.
When I open the same file in excel 2003 #NAME? Error appears.
I check Accept Labels in Formulas and it helped in some places but not everywhere.
I still have lots of NAME errors in a relatively simple spreadsheet.
I Excel 2000 everything works fine.
View 9 Replies
View Related
Aug 17, 2006
I have an activity recording tool created in Excel 2003 which works perfectly (ish)
However when some of my colleagues use it on machines running Excel 2000 it falls over at the following definition
Dim dlgFile As FileDialog
this is part of the following sub routine
Sub doCopy()
Application.ScreenUpdating = False
Call Archive
Dim dlgFile As FileDialog
Dim rv As Long
Dim sCreated As String
Dim sClosed As String
Dim wsThis As Worksheet
Dim wbkAOM As Workbook
Dim sThisCol As String
Dim aAOMCol As String
Dim strDate As String
Dim wsCore As Worksheet
Dim wsDiverted As Worksheet
Dim iRow As Integer
Set wsThis = Application. ActiveWorkbook.ActiveSheet
Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
dlgFile.Title = "Select AOM Sheet"
dlgFile.InitialFileName = "*.xls"
rv = dlgFile.Show.......................
View 5 Replies
View Related
Mar 15, 2007
I convert text file into Excel. Then what I want is to look in column C for cell value “UNT:” (it is chopped word account: ) and copy value what is in the same row next column D into column K.
Then in column K copy down the value until next not empty cell.
My problem is that it works on Excel 97 and 200 but 2003 doesn’t work.
View 9 Replies
View Related
Apr 25, 2008
I have constructed a macro, (with lots of help from the fine people on this site), and have distributed it to several people in the company. The macro was written in Excel 2003, and runs perfectly on all the machines running Excel 2003, but persons using Excel 2000 are getting a runtime error, variable not defined.
Is there a trick to getting a macro to run in both versions of Excel?
View 9 Replies
View Related
Feb 18, 2008
I have a macro which is able to run very fast in Excel 2000. Almost 5s. When i run it in Excel 2003, it takes almost 4 to 5 mins to complete. Is there any patch in 2003 i need to run the macro smoothly?
View 4 Replies
View Related
Mar 20, 2007
I have a Find Method routine in a procedure that works fine in xl2000, but fails in xl2003 with error code 9, subscript out of range.
Is there a different format for the Find Method in xl2003?
'code in xl2000
Dim CourseI As Range 'Students taking BusinessI
Dim CourseII As Range 'Students taking BusinessII
Dim BothCoursesTrue As Range
Dim Student As Range
'code
'code ...
For Each Student In CourseI
Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, LookAt:=xlWhole)
If Not BothCoursesTrue Is Nothing Then Student.Offset(, 3) = 3
Next Student
View 8 Replies
View Related
Jun 29, 2006
I'm trying to run a macro on my machine (Windows 2000 and Office 2003) and I keep getting an error on one method. Now I tried to run the same macro on a different machine (Windows XP and Office 2003) and it didn't error out.
Public strControlTitle As String
Public strControlTime As String
Public strControlUnit As String
Public intControlItem As Integer
Public intLastNameOnly As Integer
Public intDataType As Integer
Sub Main()
Dim strControlItem, strPageName, strValue, sigma, z95, z99 As String
Dim r, c, count, cmax, rmax, rt, ct, low, high, a, b As Integer
Dim lesscol As Integer
With ControlChartForm
.ComboBox1.AddItem ("Average Turn Around Time All Patients")....................
View 4 Replies
View Related
Apr 24, 2007
When I open an excel 2000 spreadsheet in 2003, the formulas fields do not populate data just #N/A. I can see data just fine in Excel 2003 viewer.
View 5 Replies
View Related
Jul 30, 2014
I have a spreasheet with one locked collum that I don't want to be edited. Is there any way of filtering while this collum is locked? It seems to be that once it locks the collum it also locks the filters?
View 6 Replies
View Related
Oct 23, 2008
how to create two buttons so that I can have a data entry specific protection and then a lock all button. Here's the link to the thread:
http://www.excelforum.com/excel-prog...-a-button.html
And here's the code I'm currently using:
View 3 Replies
View Related
Nov 12, 2005
I have twelve sheets in the book called "Jan", "Feb" to "Dec". Now I will use value in the two sheets by this format:
Row1 will contain columns, "jan", "feb", and etc.
Row2 should be like "=JAN!b2", "=FEB!b2", etc till "=Dec!b2".
Now the question is, how can I use reference, but not directly put in the sheetname such as "Jan!b2"?
In another word, what I want to use is like "=Worksheets(cells(...))!b2".
View 11 Replies
View Related
Jun 27, 2009
I have a workbook developed in Excel 2007 (compatibility mode) that contains Userform4. This form can be displayed (or not) depending in the wishes of the user. What I am trying to do is return focus to the worksheet after the UserForm is displayed.
When the worksheet is activated, I have the following .........
View 14 Replies
View Related
Oct 19, 2006
Is there an easy way to show/return any Unprotected Cells on a Protected Worksheet?
View 8 Replies
View Related
Mar 7, 2008
I am fairly new to Macros and such so please bare with me. I work with workbooks that have 30-50 worksheets in them. They are fairly complex and we send them out to our agents to use and need them protected so they cant change anything. I found how I can protect all the worksheets with one macro. There is one more part that I would like to add to this macro that I cant seem to find an answer for on these forums (Yes I have spent the better part of the day looking).
When you manually go to protect a page. It asks you to type in your password for the first time and below that there are a bunch of boxes that can be checked or unchecked. I need the first box "Select locked cells" to be unchecked. It is checked by default. Is there something I can add to my macro to uncheck this box?
This is the topic I used to create my Protect/Unprotect macro.
Macros to Protect/Unprotect Worksheets and Workbooks
View 9 Replies
View Related
Jan 5, 2014
I have a protected worksheet that is shared between 2 departments. Is there a way of making the hide/unhide function available without unprotecting the worksheet.
Macro would be the most obvious way but believe you can not share a workbook when a macro is incorporated.
View 2 Replies
View Related
May 19, 2009
I have a column containing 8000 records, some of them are in upper case & rest are in proper case, is there any way wherein i can filter all records which are in upper case without using macros, i mean is it possible to do it using ISNA function ?
View 2 Replies
View Related
Nov 24, 2013
I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.
Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?
Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?
View 1 Replies
View Related
Sep 12, 2013
I've got a spread sheet at work that is shared between 4 people. It's password protected and its been protected so only four of us can edit it. It was working fine until yesterday for no reason the auto filter stopped working. The drop down arrows are still there but nothing happens when you click on them?
The sheet was made on excel 2003 but one of the users uses the newer excel which we will all eventually get.
View 2 Replies
View Related
Feb 8, 2012
Using Excel 2003.
I have a spreadsheet that summarizes all of our assets in cell C1947: subtotal(3,D2:D1944)
In column G, I have a "Y" or "N" or BLANKS "". I'm trying to come up with a formula that would give me the correct totals for the Y's or N's or blanks when I select one of the AutoFilter columns. For example:
I currently have 1,943 assets. Column G has the Y's N's and blanks. This is showing all the assets. If I select column F which is a specific location, I would like to see the correct count of Y's N's and blanks while the subtotal command shows the total assets.
View 9 Replies
View Related
Feb 15, 2010
I have the below two codes in my worksheet, the first one is run using
a button and the second one is an automatic macro to enter fixed
dates. There seems to be a conflict, as each time I use the button to add a
row, I get an error, and when I go to debug it highlights this row in
the second macro --> If Target.Value = "" Then. Is there a solution? I can't see why there is an error, as both are
separate codes?
View 2 Replies
View Related
Apr 10, 2009
I think I have a problem with a conditional formatting conflict. I have 2650 rows and columns A - H. The rows are alternately shaded using =MOD. In column "A" I will enter serial numbers. I formatted column "A" to showed any repeated serial numbers in red with =COUNTIF($A$3:$A$2650,A3)>1. It works with one problem. Any repeated serial number in a shaded cell appears as black. I assume there is a conflict between the two formulas. I have tried switching them, but nothing I do will make the serial number in the shaded cells appear as red.
View 3 Replies
View Related
Feb 13, 2007
I have a workbook that multiple users access (read only) to do quick estimates.
This workbook has a "Print & Record" button/macro that does just that... it prints two copies of the estimate, then records/copies the basic data to another workbook (Retail Estimates Data.xls). The macro opens, pastes to, and closes this file in a fairly quick time frame, but it is conceivable that two (or more) users could be trying to do this at the same time.
What I need is some code to check if the Data file is open by another (read only status?), and if so give a message to that effect, and ask to try again, by which time the other user instance should have finished with the file.
View 5 Replies
View Related
Jan 29, 2013
This is my code:
=IFERROR(IF($B$41="","",HLOOKUP($B$41,InsulationH,(Mark+1))*$C$41),"")
If value of $B$41 is "A1", it does not execute
In all other cases it works fine ("B1", "C1", "D2"...)
Apparently, it's a cell name conflict with macro code
View 2 Replies
View Related
Feb 10, 2014
I have a simple form built, but the data validation is only partially working. I've attached a sample workbook.
basically, when a user tries to put in a letter in the GP interaction field rather than a number, it prompts user to re-enter. This part is working, except when the submit button has been clicked, the field clears. the field clears, it prompts the user to re-enter.
form based input - Copy.xlsm‎
View 3 Replies
View Related
Dec 29, 2011
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
View 1 Replies
View Related
May 22, 2008
I'm struggling constantly with the Excel 2007 I use, and the Excel 2003 that another group uses.
I'm dealing with large files; 200000 to 300000 rows often.
I wish there was a setting or way to automatically force excel 2007 to create a file in 2003 format.....meaning I want 260000 rows to automatically be divided up into 4 sheets limited to 65K each. 260001 rows would be put into 5 sheets, so that I could save it as an excel 2003 file.
Is there also a way to do this from access 2007 exported to excel?(I know that's an access question and probably outside the bounds of this board).
View 9 Replies
View Related
Aug 21, 2008
how i insert text box on excel sheet.
In which i take values from user and then manipulate those values on click event of button.
View 14 Replies
View Related