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


Advertisements:










2007 - Protected Workbook - Can't Unprotect It


I have a workbook that many moons ago I protected. Now I want the workbook to be an unprotected workbook so many users can view and update if necessary.

Typically I know you are supposed to go to: Review, Changes, Unprotect Workbook. But all that is available is to protect the workbook, but it is already protected. I know this because every time I open the workbook, I am prompted to enter a password.

Is there another way to remove this workbook protection I do not know about?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Customised Prompt When Trying To Unprotect A Protected Cell
when the user tries to enter data into Ranges E25:E26, G25:G26 OR I25:I26 I want to have my own customised prompt that says: "You are using Imperial Units. Enter drafts in the Imperial range by clicking the Imperial button on the left".

To make it more interesting, the cursor should go to the range that I have named 'Imperial', when the user clicks on the 'OK' button of the above prompt.

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
Password Protected Sheet :: How To Unprotect A Sheet?
Does anyone know if there is a way to unprotect a sheet. The person who created this spreadsheet no longer works for our company and although we can see the spreadsheet, the formulas are hidden so we can't figure out where the data is being pulled in from. Any ideas?

View Replies!   View Related
Avoid 'function Is Not Available On A Protected Worksheet' Message
I am using the before double click event on a protected sheet. When the event fires the first thing the procedure does is unprotect the sheet, does stuff and then it applies protection again at the very end.

Regardless I still get the 'Function is not available on a protected worksheet' message. How can I stop the message? Any ideas? If no standard approach is available does anyone know how I can hook this using API?


View Replies!   View Related
Unprotect A Workbook With VBA And Re-protect It When Saved
I want the excel spreadsheet to "BE Protected" in standard form, and only be "Unprotected" when macros are enabled -- BUT DONT JUMP TO CONCLUSIONS, hear me out, it gets deeper than that

If the user does not enable macros, I want the book to be protected. If they do enable macros, it becomes unprotected.... (On load is good enough, no need to be real-time)..... But, they can save the book, so it would no longer be protected , which is where my question comes in ...

I could use the -unprotect "password"- on load when macros are enabled but this will only work if the spreadsheet was originally protected and is never saved again. This is a problem because this workbook will be given to others and will be saved time and time again.

So, I had a vision :-) .... function 'before save' , protect the sheet using -protect "password"- , allow the sheet to save, and then unprotect back using 'unprotect "password"' so you can continue using - BUT, the problem with this is now I have created an endless loop in theory, because after it unprotects it would want to re-save again because it changed


View Replies!   View Related
Forgotten Password To Unprotect Workbook/sheet
Is there a way to unprotect a workbook and/or worksheet in Excel 2002/2003 if
the original password is forgotten?

View Replies!   View Related
Office 2007 - Where Has The 'do You Want To Update Links' Box Gone
I'm opening an Excel file that automatically attempts to update links when I open it, but I am not prompted with the choice to cancel, or update links, when I open the file, like I was in Office 2003.

I have checked the Excel Options, Advanced, General, 'Ask to update automatic links' box, but I still don't get the dialog box when I open the file.

The file is just riddled with VALUE errors. A colleague of mine who opens the file in Office 2003 can see all the data fine.

View Replies!   View Related
Format (highlight) Cells With 'If' Function With 2007
I have a workbook that I am working with and I need to formatting the cells so that they will have multiple formulas and I am not sure how to make it work.

Sheet1 Column A is titled 'accounts'. There are 2700 rows under Column A that are being used. Column A is already formatted with a Vlookup formula so I can't add another formula in these cells.

What I need to do is highlight many ranges of these 'accounts' cells depending on their account numbers. I need to input this formula in a separate location on the spreadsheet. For example; If 'accounts' is >=110031100000 AND <=110031100099 then highlight those accounts in 'RED'. and If 'accounts' is >=1200454000000 AND <=120045400099 then highlight those accounts in 'green' and so on...I am not sure how to word the if statement. The formula must reside in column D or greater and not in A.

Am I going about this the wrong way. What I am trying to do is isolate the cells (accounts) that I have referenced on another worksheet so that I can visually notice any accounts that may have been excluded.

View Replies!   View Related
Workbook Enable Macro Protect / Unprotect The Sheet
I need a macro that I can have in any workbook enabling me to protect / unprotect the workbook that I have currently opened with a set password (let say "Password")

I would link this macro to a button in excel 2002. I have try the following but it doesnt work

Would it be possible that the button (first) works (second) understand whether or not protect / unprotected and do the opposite?

Sub Protect
Activeworkbook.protect password:="password", structure:=FALSE, Windows:=false
End Sub


View Replies!   View Related
Changes Aren't Saved While VBE Is Protected
I have a WorkBook of 13 sheets

it has 2 Modules:

one unraveling protection from cells protected and restore it to all Sheets in a single step

And the second controls printing process to make it only for rows that conaining date in the predetermined columns

that latter is to break the protection, execute printing and re protection again without user intervention

The strange thing is : after protecting these modules from being viewd by users as it contains THE PASSWORDS .....

View Replies!   View Related
Allow Sorting: Protected Worksheet Doesn't Allow A Sort
I have a column containing 365 entries, one for each day of the year. This I intend to send out to Army bands, to have them fill in their forecast of engagements. I do not want them to be able to amend the original diary, but to scroll to the bottom of the list, add a new entry with the applicable date and then be able to 'sort' back to chronological order. This would then allow a separate entry for every engagement to allow me to interrogate the spreadsheet. As I have protected the sheet, the 'sort' facility does not function even though when protecting I ticked the box to allow users to 'sort'.

View Replies!   View Related
Check If Workbook Is Protected
I just noticed that if you already have a workbook protected and then run code that will protect it, it will become unprotected! (I have a line of code to protect the workbook in the workbook_open event; incase the last user unprotected the book on the last use). Apparently it doesn't work like protected worksheets, where if the sheet is password protected and you have code that tries to reprotect it with a different password nothing will happen. I can not find a way to check if the workbook is protected (and if so skip, but if not then protect). Worksheets have "activesheet.protectcontents" to tell you True/False.

View Replies!   View Related
Linking To A Password Protected Workbook
My question is as follows:

I have two workbooks in the same folder on a shared drive. Workbook2 is linked to workbook1. I have protected Workbook1 with a password protected to limit access.

When my collegues who use workbook2 try to open it, it asks for the password for workbook1 before opening. When they ignore that and go a head to open the file. The new information from workbook1 is not updated into workbook2.

How can i mentain my password in workbook1, and keep the files linked such that they can be able to see the new information in workbook2 without knowing the password for workbook1.

View Replies!   View Related
Paste To Password Protected Workbook
I've got two files: the one that is the source file and the second one which is the target file. The target file is password protected (let us take the number 1 as the password in this case). I have a macro that would copy the value of an example cell to the target file:

Sub iso()

Dim wBook As Workbook

Range("A2").Activate

ActiveCell.Copy

On Error Resume Next
Set wBook = Workbooks("Test2.xls")

If wBook Is Nothing Then
ChDir "D:"
Set oWB = Workbooks.Open(Filename:="D: est2.xls")

to add a line (or two) to this code, that would open and unprotect the target file and then at the and protect the target file with the same password.

View Replies!   View Related
2007 Enable Macro's
I have converted an Excel file from 2003 to 2007. The file now has a .xlsm extension as it contains macros. I have "enabled all macros" and "trusted access to the VBA project object model". Whilst open, I have tested the macro buttons and they work.

I then close the file and re-open it, and the macro's will not work. A message appears along the lines of "macros are anabled" or "macro is not found". I have double-checked the settings and everything appears normal.

View Replies!   View Related
VBA Macro Not Working When Workbook Is Protected
I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:

View Replies!   View Related
Enable Outlining In A Shared/protected Workbook
I would like to know how the following code provided in the Excel Help Best Practice Forums , used to enable outlining on a protected sheet, can be applied to a shared workbook.

Private Sub Workbook_Open()
With Sheet1
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub

After enabling 'Share Workbook' and re-opening the file it presents the following error:

Run-time error '1004': Method 'Protect' of object '_Worksheet' failed

View Replies!   View Related
Group/Ungroup In A Protected Shared Workbook
I used some code to allow grouping/ungrouping in a protected sheet. This works great! Enable Outline/Outlining on a Protected Excel Worksheet. However, when I share this protected workbook, all of a sudden grouping and ungrouping is not possible anymore. I get the same error as before, when the workbook was protected, not shared, and did not have the code mentioned above. Error: "You cannot use this command on a protected sheet. To unprotect..." Assumption: Is it possible that the Workbook Open Event is not being triggered if the workbook is a shared workbook?

Question: How can I group/ungroup columns in a shared (and protected) workbook?

View Replies!   View Related
Export (Copy) Sheet From Password Protected Workbook
I was handed a workbook that is password protected. The worksheets are not password protected. Because the workbook is protected, I cannot add or delete worksheets.

I figured that I could get around this by copying the worksheets to another workbook, but I can't even do that. Naturally, no one remembers what the password is.

View Replies!   View Related
2007 Doesn't Want To Open WB Created In Code
In my first workbook I select an area to copy/paste into a new wb with this code (from the board)

[Set source = Range("A55:K109").SpecialCells(xlCellTypeVisible)

Dim FName As String
Dim VBComp As VBComponent
Dim wbnew As Workbook
Dim x As String

Set wbnew = Workbooks.Add

Set dest1 = ActiveWorkbook

dest2.Activate
With ActiveWorkbook
FName = .Path & "code.txt"
If Dir(FName) "" Then
Kill FName
End If
For Each VBComp In .VBProject.VBComponents
If VBComp.Type vbext_ct_Document Then
VBComp.Export FName
wbnew.VBProject.VBComponents.Import FName
Kill FName
ElseIf VBComp.Name = "ThisWorkbook" Then
If VBComp.CodeModule.CountOfLines > 0 Then
wbnew.VBProject.VBComponents

The complete code in this workbook sends the entire workbook to one person and it creates a new workbook and pastes a range and the VB code needed to run the button it copies and sends this to another person.

The codes runs excellent in 2003. In 2007 I can get the entire workbook to send to the first person, but

The email to the second person will send, but they can not open the workbook. I get the error "File Format or file extension is not valid. Please verify file is not corrput and that extension matches file.

I know trust access to visual basic has to be enabled in 2003 for this code to run, so in 2007 I went to the Excel options trust center and clicked the box which says Trust access to the VBA project object model.

When I step through this code everything looks great. The worksheet area is correct, the button is copied, the code can be seen in the properties window, but then I get the email and it will not open.

There is even a line of code that kills the file after the mail is sent (temporary saving location is the desktop). So, I used a ' and made it a comment line so I could try to open the file from my desktop. It will not open there either. File extension being used is .xlsm

View Replies!   View Related
Copy A Range And Paste Into Another Workbook - Files Are Password Protected.
I need to copy and paste a range from one workbook to another workbook automatically.

Basically, I have 180 files in a folder. The details are:

1) 90 master files

2) from these a range named "COPYTOE" has to be copied and pasted into another 90 report files.

3) I have list of files & its passwords in a separate file named FNAME.XLS. In this, Sheet1, A1:C the details consist like this: ....

View Replies!   View Related
2003 To 2007, VBA Codes Doesn't Work
I always used Office 2003 .. I just installed Office 2007 to try it out. But everything doesn't work in Excel 2007, which does work in Excel 2003.

I added an example of the list, but here are the codes:
Sheet1

View Replies!   View Related
Insert Picture Macro Doesn't Work In 2007
I just upgraded to Excel 2007 and my macro from Excel 2003 for inserting a picture doesn't work correctly in 2007. I have a command button that states insert picture and when you click it, it will let you insert a picture into the cell and hide the command button. In 2007 it will let you insert the picture, but it isn't centered and expands into other cells and the command button is still visible. The picture appears to be the correct size it just doesn't center itself in the cell and the command button is still visible. Below is the

View Replies!   View Related
Unable To Use Arrow Keys To Select Hidden Cells On A Protected Workbook
I have a problem with a spreadsheet I am working on, and I'm not sure if its something I've done, or whether its corrupted.

Basically, Column A is Hidden and not Locked in the protection tab of formatting. The other columns are Locked and not Hidden.

I used to be able to use the arrow keys to scroll through Column A, but this now doesn't seem to work. I can select the cells within column A with the mouse and I can scroll with the mouse and the scroll bars, but I can't move between the cells with the arrow keys, enter button or tab button.

Does anyone know what might be causing this, or is the workbook corrupt?

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
Macro To Create Worksheet 'xxxx' And List All Worksheets In Workbook
I am after some code to create a new worksheet called 'xxxx' in my workbook, and then list all the sheets within the workbook (excluding the newly created 'xxxx' sheet.)

View Replies!   View Related
VBA Replace Method Retains 'Within Workbook' Setting
I am selecting a block of cells to apply numerous "Replace" functions to.
So I use the Selection.Replace command - for example:

Selection.Replace What:="Street", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

if I have been working with Excel previously and did my own "Replace" and applied it to the entire Workbook (i.e. changed the within field from ' Sheet' to:
"Within: Workbook"

Then that field stays set to "Workbook" for subsequent Replace activities, including my VBA code !! Therefore if I do not manually go back and run one "Replace" and set the within field back to "Sheet", the VBA code will apply my Replacements to every sehhet and every cell in the entire workbook. Even if I have selected a Range of cells before issuing the command !! It ignores the selected Range and runs the "Selection.Replace" for the entire Workbook.

The "fix" I found on another site is to run a dummy command:

Set dummy = Worksheets(1).Range("A1:A1"). Find("Dummy", LookIn:=xlValues)

Which works. However, I am looking for a way to add a parameter to the "Selection.Replace" command that will cause it to search using the "Within: Sheet" setting. Otherwise I always have to remember to add that dummy line of code for every single Selection.Replace line of code.

View Replies!   View Related
Exporting Macros To Another Workbook From The 'sheet' Code Module
I've used the following code to export macros from one workbook to another but I was wondering how you export macros from the ' Sheet' code module to the 'Sheet' code module of another workbook.

Sub Import_Macro()

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

strRet = .Lines(1, .CountOfLines)

End With

Set wkb = Workbooks("Test Workbook.xls")
wkb.VBProject.VBComponents. Import ("G:SCSSCSALLReportsVB MacrosGeneral.bas")
wkb.VBProject.VBComponents.Import ("G:SCSSCSALLReportsVB MacrosMJ Selections.bas")

With wkb.VBProject.VBComponents("ThisWorkbook").CodeModule

.DeleteLines 1, .CountOfLines '//Delete Codes already wrriten
.InsertLines 1, strRet '// Write Code

End With

End Sub

View Replies!   View Related
Run-time Error '1004' :: Method 'Range' Of Object'_Global' Failed
I am trying to create a macro in my personal macro book such that whenever any workbook is opened the calculation settings (tools, options, calculation tab) are set to semiautomatic and do not calculate before save. The macro works when I am opening Excel itself (Book1) but when I open an already saved file it gives me

Run-time error '1004'

Method 'Range' of object'_Global' failed

From there I choose Debug, the VBE window comes up, and I hit F5 to continue the code without doing any actual debugging. Here is the code that I am using. This is in the Personal Macro book on the "This Workbook" section....

View Replies!   View Related
Run-time Error '1004' Method 'Range' Of Object '_Worksheet' Failed
I have an interesting error that only happens when there is one row of data in the worksheet (sheet2 or "Half Payout"). Rows 1 & 2 are headers, row 3 is when the data starts - if any. With either no rows of data or more than one the coding works just fine. Here is the exact error message I'm getting: Run-time error '1004': Method 'Range' of object '_Worksheet' failed.

The following code is supposed to sort the rows of data when opened and then activate the first open cell below B2.

View Replies!   View Related
Run-time Error '1004': Methd 'Range' Of Object '_Global' Failed
I have constructed the following code to set the print area of worksheets that have been selected to print to the range referenced in a worksheet level named range "xPrintArea". This named range is set using the OFFSET function. The procedure also sets the left footer to be a copyright notice that is also contained in a cell referenced by a named range.

Set oPrintArea = Range(sPrintAreaName)

is generating the error "Methd 'Range' of object '_Global' failed".

Note that the line

Set oCopyrightNotice = Range("CopyrightNotice")

does not generate this error. From what I've been able to determine from other research on this forum and others, I believe the problem is that I need to more fully qualify the object which Range(sPrintAreaName) is referencing. I've already tried to use

Set oPrintArea = wkSht.Range(sPrintAreaName).................

View Replies!   View Related
Run-time Error '1004': Method 'Worksheets' Of Object '_Global' Failed
I am not familiar with Excel/VBA and I tried a couple of suggestions with no luck. The excel file contains three forms and three modules and it is intended to calculate and build Electrical panels. I didn't write the code. I am just trying to fix the error. I was trying to attach the .xls file but it is 178 KB. How can I post the whole code.

View Replies!   View Related
Method 'Add' Of Object 'CommandBarControls' Failed (run-time Error '-2147467259')
i get this error on the line in bold Method Add' of object 'CommandBarControls' failed (run-time error '-2147467259'). im not sure why its giving that problem, but im not very fluent in custom toolbars.

'//The following two procedures add a custom menu to the workbook programmatically//'
'//and then delete it//'
Public currentMonth As String
Sub CreateMenu()
Dim mybar As CommandBar
Dim myControl As CommandBarControl
Set mybar = Application.CommandBars.Add( Name:="CustomButtons", _
Position:=msoBarBottom, Temporary:=True)
mybar.Visible = True
Set myControl = mybar.Controls _
.Add(Type:=msoControlButton, ID:=1)
With myControl...........................

View Replies!   View Related
Listbox Linked To Workbook Tabs (2007)
I hope I can explain what I am trying to accomplish properly. Many years ago (5+), I had this exact workbook setup, but am having a lot of difficulty remembering VB & Excel and making this again from scratch.

I am looking to create an Excel Workbook. The workbook will contain numerous tabs, each tab will essentially be a template, and contain basic formulas.

On tab 1(Sheet1), I would like to have a listbox. The individual items in the listbox, should have the same name of, and be linked to all tabs in the workbook.

There will also be three buttons.

Button1 - Generates the names in the listbox based on existing tabs within the workbook
Button2 - Opens a new workbook, and copies over the selected tabs (AKA - creates a working copy of the workbook)
Button3 - Clears the selection and resets the listbox.

The number of tabs, and contents of tabs, within the workbook will be changing. So the listbox should know to look for all tabs, be it 5, or 50.

The end result would allow someone, who knows nothing of Excel, to open this workbook, as a read-only file. Generate a list of available tabs, select the tabs they need, generate a new workbook containing these tabs, that they can then edit to suit their needs.

In my line of work (and I am sure everyones) there is a huge problem with consistancy. This leads to a snowball effect of problems carrying over, and being copied from one project to the next. As I mentioned earler, I once already had this workbook setup, and it solved all our consistancy problems. I am hoping to do the same.

View Replies!   View Related
When The Workbook Opens Compile Error In 2007
I have written a very basic Macro code to retrieve a photo off of my server. I want it to run this macro when a person has selected a certain item off of a drop down list. The problem I am having is that when I try and run the macro manually or when the workbook opens I get a an error message that reads "Compile Error: Invalid outside procedure". I click "Okay" and it highlights this portion of the code

View Replies!   View Related
Access Private Module Of The Workbook. 2007
I want to write some code that runs everytime the workbook is initialized. I found out that the code should be placed in the following "Private Sub":

Private Sub Workbook_Open()
'My code
End Sub

I know that this code should be placed in the Private Module of the Workbook! I just can not find where is the Private Module of the Workbook in Excel 2007... =/

View Replies!   View Related
Save Workbook As 'Values Only'
I have 5 workbooks, loaded with macros and VBA, each of which has 12 tabs representing months of the fiscal year. Values for the previous day's financials are updated daily.

The files are then posted to the server, so that certain bigshots can view the results and subsequently complain to us about our miserable lack of committment.

Is there a way to save or export these files without the macros and code, (i.e. as 'Values Only')? Apparently, some of them get frightened by the message box that appears warning them about the potential dangers of roque macros and code, and would prefer me to deal with the problem, rather than adjusting their security settings or allowing me to install a Trust Cert.

View Replies!   View Related
2007 Macro Relative References Works On Only One Workbook
In Excel 2007 I am creating a macro with the "relative references" setting turned on. I want to repeat some tasks like: go to column A, sort, hide columns B thru F, hide columns H & I, return to column A.

Every time I try to create this macro by capturing key strokes, it inserts the workbook / worksheet name in the macro! Kinda defeats the ability to use it in any other workbook.

I am recording the macro in the PERSONAL.XLSX workbook. I have other macros that successfully perform in any workbook they are used in . . . but today I can't create one that doesn't incorporate the name of the original sheet it was recorded in.


View Replies!   View Related
Automatically Minimize The 2007 Ribbon On Opening A Particular Workbook
I would like to be able to automatically minimize the Excel 2007 ribbon on opening a particular workbook. My preference is to be able to apply that minimization to certain worksheets only, but if that's not possible then minimizing the ribbons on all sheets in the workbook will be ok.

Will I also need a BeforeClose code as well to reinstate the ribbon so that it is visible when opening subsequent workbooks?

View Replies!   View Related
FileSearch Method In 2007, Run-time Error 445 Object Doesn't Support This Action
I was using the following macro on Excel 2000, 2002 & 2003 for many years without a fuss. Recently, I upgraded to Excel 2007. When I run the macro now, I get the following error message:- Run-time error 445 Object doesn't support this action.

Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Documents and SettingsDesktopCommercial Database"
.SearchSubFolders = True
.Filename = "*.*"
.TextOrProperty = "BANK"
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
End If
End With
End Sub

Additional info:- The 1st line of my code which is With Application.FileSearch is highlighted in yellow when I run this macro.

View Replies!   View Related
Using 'IF' With Conditional Format (change Cell Color Based On The 'P' Or 'F')
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters.
Samples of what I've tried:
=IF(LEFT(L23,1)=P)
=IF(MID(L23,1,1)=P)
etc.

I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?

View Replies!   View Related
How To Restrict The Cell Data To Letters 'a' Through 'e' AND 'A' Through 'E'
I am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E.

What I am looking for

1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that.

2) I would like this validation/formula to be applied to all the cells in a particular column.

3) Nice-to-have.

If the user enters a lower case letter, then I would like to convert to upper case.

View Replies!   View Related
Run-time Error '1004' Method 'Add' Of Object ' Sheets' Failed Adding Multiple Sheets
I have been running a simulation for about 18 hours now and just received:

Run-time error '1004':
Method 'Add' of object ' Sheets' failed

I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:

ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)

The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...

View Replies!   View Related
"Run-time Error '1004', Method ' Range' Of Object '_Global' Failed"
I keep getting this "Run-time error '1004', Method ' Range' of object '_Global' failed" Here is the code that has the problem:

Option Explicit
Dim i As Long
Dim j As Long
Dim lDup As Long
Dim lRow As Long
Dim NoDupes As Collection
Dim rRng As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Swap1 As Variant
Dim Swap2 As Variant
Dim wks As Worksheet

Private Sub UserForm_Initialize()
Call DefaultSet
For lDup = 1 To 3
Call NonDuplicatesList(lDup)
Next lDup
End Sub.....................

It does not even loop once though the original UserForm_Initialize For/Next loop.

View Replies!   View Related
Run Time Error 1004 - Method 'Range' Of 'Object'_Global' Failed
I am trying to build a macro which will format the columns of a spreadsheet - basically it inserts some columns, writes formulas and highlights them. Here is a code I have got so far...

When I try to run this I get a run time error 1004 - Method 'Range' of 'Object'_Global' failed. The part of the code
Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"

is highlighted in the debugger.

Can anyone tell me why this is happening, also it would be great if you could suggest better ways of writing this code - as I am new to vba programming and most of my macros are built using the recorder and then 'working' on them.


View Replies!   View Related
At Startup .xls Doesn't Open At Startup Any Longer- 2007
I use Excel 2007 and I have an .xls that I've had set to auto-open at startup and has worked fine for months now. Alll of a sudden a couple days ago it stopped auto-opening, and I have to now manually open the .xls. I looved in the Advanced>At Startup setting and it's set to the correct folder. What other setting in Excel 2007 do I need to check that would be causing this startup problem?

View Replies!   View Related
Vba Error Run-time 429 Activex Component Can't Create Object - Excel &amp; Outlook 2007
I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error:

Run-time error '429':
ActiveX component can't create object

and is stopping on the line

Set OutApp = GetObject(, "Outlook.Application")

FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error.

Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String

'Set OutApp = CreateObject("Outlook.Application")
Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

View Replies!   View Related
Run-Time Error '1004' Cannot Open PivotTable Source File 'Consolidated Report'
I have a macro that takes the date from different excel sheets, consolidates the data and renders the pivot table and chart accordingly. It was working fine when the date range defined for the pivot table was static. Now I have made it dynamic since the data range changes each month depending on the number of days it has got. When I run the macro, it runs succesfully, generates the report and save & close the report, but after that I am getting this error. Errorneous

View Replies!   View Related
Conditional Format The List So That It Shows The Lowest Percentage In 'green' And The Highest In 'red'
I have a list in column A of percentages (to 4 decimals 0.0013) i need to conditional format the list so that it shows the lowest percentage in 'green' and the highest in 'red'.

View Replies!   View Related
Error On Data Transfer Between Sheets (Method 'Range' Of Object '_Global' Failed)
I get an error message on the "Range(lstRow).Select line. (Method 'Range' of Object '_Global' failed). My goal is to transfer a specific range (C24:H24) to the first available row in Data Entry sheet.

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