PROTECTING FORMULAS #2

 I have a sheet where when opened, it runs an auto macro to move cell
contents from one
column to another and uses a formula to do some calculations. If I select
the columns I want unlocked, then protect the sheet and try to run the
macro, I get an "run time error 1004 unable to set the numberformat property
of the range class" When I debug the following is highlightd
"Selection.NumberFormat = ";;;"  "       I was a Lotus user and in Lotus I
could select a range of cells or columns to protect, I'm having a hard time
to get Excel to do the same.
I appreciate the help.
Danny


0
shamoon (2)
12/26/2006 5:13:02 AM
excel 39879 articles. 2 followers. Follow

1 Replies
813 Views

Similar Articles

[PageSpeed] 45

Danny wrote:
> I have a sheet where when opened, it runs an auto macro to move cell
> contents from one
> column to another and uses a formula to do some calculations. If I select
> the columns I want unlocked, then protect the sheet and try to run the
> macro, I get an "run time error 1004 unable to set the numberformat property
> of the range class" When I debug the following is highlightd
> "Selection.NumberFormat = ";;;"  "       I was a Lotus user and in Lotus I
> could select a range of cells or columns to protect, I'm having a hard time
> to get Excel to do the same.
> I appreciate the help.
> Danny

Hi Danny,

Is the problem that you have the worksheet protected when you try
running the macro?  The error "unable to set the numberformat property
of the range class" suggests to me that the cells you are trying to
modify are protected.  Before you change the numberformat, you need to
unprotect the cells.

0
12/26/2006 10:16:04 AM
Reply:

Similar Artilces:

Strange formula popping up
Hello. I have a problem in excel 2003. My SS has lots and lots o figures on it. I used format with no $ , 2 decimals, and chose (1234 56). However every time I try to enter an amount in the minus in an cell it comes out strange. For instance say I want to enter (250.00). It does this: - 250.F8 (or any other cell ). and then jumps to the cel next to it and does the little flashing 'outline the cell' thingy. tried deleting and clearing all the formulas and then reformatting bu it keeps doing it. Amounts in the black come out just fine such a 1234.56. It only does it in a cell with ...

protecting outlined data
I want to be able to use the expand/collapse function in outlines when the worksheet is protected. Copy this macro in a normal module It will run automatic when you open the workbook You can also use the workbook open event Sub auto_open() With Worksheets("sheet1") .Protect Password:="rbelecki", userinterfaceonly:=True .EnableOutlining = True End With End Sub Look in the VBA help for more information about protect(userinterfaceonly) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "rbelecki" <rbelecki@l...

Pivot Charts
Hi all, I'd like my pivot chart to do 2 things: 1) If I select an item from one of my page field dropdowns, I'd like the dropdown items on another page field to filter according to what was selected. Example: if the first page field is "Teams" and I choose a team from the list, I want the other page field "Players" to offer only the players from that team. Possible? 2) I used the macro recorder to automatically replace one data item in the pivot chart with another. Example (football): I assigned a macro to a button called "Pass Attempts", which will...

RMS 1.2 Database Backup
I am looking for a small batch files that backs up my POS database on a daily basis where the backup file name is the system date and time. I found a batch file backup with a fixed file name on MS Customer Source but would rather want to keep daily copies. "Gerd" <gerd.goebel@bavarian-cons.com> ha scritto nel messaggio news:%23qixfi9qFHA.3736@TK2MSFTNGP10.phx.gbl... >I am looking for a small batch files that backs up my POS database on a >daily basis where the backup file name is the system date and time. > > I found a batch file backup with a fixed file n...

Password Protect Outlook #2
I work in an offcie environment and sometimes have to leave my PC unattended for just a few minutes. Unfortunately some confidential emails have been read by my staff and I need a way of safeguarding them other than turning off the PC, is there any way I can password protect access into my Outlook 2000, so all I do is close Outlook. (Sorry still operating under W95) Many thanks You can set a password on a Personal Folders File. If the folder list isn't visible in Outlook, click View | Folder List. Right-click the root folder (usually "Personal Folders" or "Outlook T...

Random numbers #2
Hi all, My application is in a nead for a very big number of randomal numbers. I use srand() and rand() calls. My problem is that I suspect that the random numbers I get, starts to repeat them self at some point. My questions are: What happends when the srand() list is over? Does rand() start all over again and begins returning the same numbers? (with a certain bit offset)? How long is the list srand() creates? Is there any better way to create many random numbers? Thanks in advance Dudu Arbel "dududuil" <dududuil@discussions.microsoft.com> wrote in message news:B022C3...

formula question #8
Hi, Well I've looked in my excel books and I have excel for dummies (and yes I need it) :-), I am sure there must be a way and have tried several things hoping to hit on the right one. I want a result in one cell, reliant upon two different cells having a $amount over zero. I tried this formula but it only comes back with the word "true" or "false" and only works for one cell with an amount over zero. =OR((D4>0)*(G4>0),'TDBonus'!K3,0). In other words if D4 is more than zero or G4 is more than zero, then I want the cell to display what is on another ...

Quicken Password protected
I just got Money 2004 Deluxe, I am trying to import my data from Quicken and the program says there is a password for my quicken account that is required, but I never had a password. No way to get past this. Any ideas? If your data file is Q04, Money04 can only deal with Q03 or older. Otherwise, who knows? "Stephen Weiss" <stephenaweiss@comcast.net> wrote in message news:012f01c3cb22$9a957ac0$a101280a@phx.gbl... > I just got Money 2004 Deluxe, I am trying to import my > data from Quicken and the program says there is a password > for my quicken account that is req...

Autofill Formulas
Hello. I have a workbook with 31 sheets of data. Each sheet is a form with a record of data. I would like extract the data into a list. For example, range A5 is the same field on each sheet, so I want to autofill a formula reference the same cell on different sheets. I suspect I need a macro for this, but wanted to make sure first. Can you autofill like this =Sheet1!$A$5, =Sheet2!$A$5, =Sheet3!$A$5? -- Thanks, Mike =INDIRECT("Sheet" & (ROW()) & "!A5") Entered in A1 of new sheet. Copy down to A31............will increment the Sheet numbe...

word art 2.0
i have word art 2.0....there must be a way to change the thickness of the font...every font i choose to use seems to be in bold...which i DO NOT want...is there a setting to change..thank you Download the old WordArt. See if you get better results. You use it from Insert, Object, scroll down to Microsoft WordArt 3.2 (This will work in 2007 too) Publisher 2003/2002 Add-in: WordArt Compatibility http://www.microsoft.com/downloads/details.aspx?FamilyId=897AA11B-A37D-4586-A1A7-54BBEA375AE2&displaylang=en -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://ms...

How to Install BCM? #2
Hello -- I have Ms office 2003 pro, which after installation is working fine, including Outlook 2003. I cannot, however, find/locate BCM. I tried the add/remove option in office 2003 but it did not show any option of adding BCM. Documents, which I've received with office 2003's cd, mention that it has BCM. Can someone, please, help me to instal BCM, or tell how to find, and if there is, BCM in the CD I have. Thanks, SRW are you impatient? "S R W" <SRW@discussions.microsoft.com> wrote in message news:18DAE3D6-5CDB-4570-8B47-681E9DB38C1B@microsoft.com... > He...

How to protect 2. axis in an Excel Chart to stay if data changes
A Pivot table is created on Tab1 in an Excel workbook. On Tab2 there is a Line Chart created with data from Tab 1 using 2 axis. If data in Tab1 change the 2nd axis on Tab 2 disappears, jumps ober and overrules axis 1. How can I protect chart area with 2 axis being able to change referring data in the pivot table. ...

Password protection on split database
I have a database which is split into front end and back end. The front end is password protected but the backend isn't Is it possible to protect both with the same password? I've tried protecting the backend with a password but when I open the front end it can't "see" the backend. Any ideas? Thanks Tony Put a password on the backend, then, after opening the front-end, use the Linked Table Manager to update the links (just like you would if you had moved the backend). You should be prompted for the 'missing' password during this process. You should only...

Cell ref is it possibile to calculate / change this in a formula
Any SUPER USERS uot there? I am trying to construct a formula where I need to copy it down 6500+ rows but I need one of the cell refs to be calculated in sted of having its row number to raise by one for each row copied. sample, Normel coping: =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25) =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C26) What I am trying to get: =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25) =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16) For every line the for...

How to I use ** without Excel thinking I want to type a formula?
I am using Excel (Office 2003) to create a table and I need to use two of these: * to indicate significant results for a research study. How do I tell Excel that I just want to display this symbol after a three digit number without it thinking I want to type a formula? Turn off Lotus transition under tools>options>transition and uncheck transition formula entry Regards, Peo Sjoblom "Buff" wrote: > I am using Excel (Office 2003) to create a table and I need to use two of > these: * to indicate significant results for a research study. How do I tell > Excel th...

formula required
Hi, Cell A1 has the information. abcdef200912070005643<ready>,abcdef200912070005644<ready>,abcdef200912070005645<ready> In Cell B1 i need the answer, abcdef200912070005643 Thanks in Advance!!!! Vicky "Vicky" <Vicky@discussions.microsoft.com> wrote: > In Cell B1 i need the answer, abcdef200912070005643 So, type abcdef200912070005643 into B1 and press Enter. Okay, that's probably not the answer you were looking for ;-). But the point is: you don't say much to tell us what kind of answer you are looking for. If you want...

Protection
Hi everyone OK my problem is I am using an offie computer that everyone has access to and we cannot - and do not want to - change this. Is there a way that I can put a password for my profile on Microsoft outlook so that no one can read the new AND the old messages found in my inbox? You can put a password on your PST file, presuming you use a PST file... Emad Kamel wrote: > Hi everyone, > OK my problem is I am using an offie computer that everyone has > access to and we cannot - and do not want to - change this. Is there > a way that I can put a password for my profile on Micr...

Rounding Formulas
I'm working in a spreasheet that has a lot of formulas, if I want to round the number that a formula shows, do I need to do that in a different colmn or is there something I can add to the formula to round it to the nearest dollar? Here is an example if the formula I'm often using in this spreadsheet. =((C22*G11)*G17) =round((C22*G11)*G17),0) should do it. If you want the result to the nearest PENNY, do this: =round((C22*G11)*G17*100),0)/100 "Chandra" wrote: > I'm working in a spreasheet that has a lot of formulas, if I want to round > the number that...

outlook PST file #2
Need help I installed Win XP PRO then Office 2003 and tried to import my PST's. It did that just fine but for some reason it gave me two Personal folder both with all the same files and folders. I need to know how to get rid of one of the personal folder. This happened to me also, but I have 3 personal folders. Can you post the solution if you find it elsewhere? Thanks... Can you right-click on one of them and select Close? willy wrote: > Need help I installed Win XP PRO then Office 2003 and > tried to import my PST's. It did that just fine but for > some reason it ga...

How the heck do I plot 'my text' for the X axis? (instead of 1, 2, 3, 4 etc.)
Hi, I'm using XL2002 & Win2000Pro. How can I display the week numbers (as shown) in the x axis, rather than 1, 2, 3, 4 etc. that M$ uses. Here's my data Week 11 12 13 14 Sales A 43 55 48 32 Sales B 32 36 41 29 Here's what I want the axis's to look like 50 40 30 20 10 0 11 12 13 14 How do I do it? Many thanks!!! Norm Father Guido ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ I plan on living forever... so far, so good Option 1: Del...

Registry #2
use VC .NET 2003 Necessary to use whole the registry. SetRegistryKey - only for \\HKEY_CURRENT_USER\Software.. Help me... Check www.codeguru.com under Visual C++ / Windows Programming / System / Registry There are some classes there that make it possible to read and write in the entire registry. Rob "Sergey Popkov" <sergey_popkov@mail.ru> wrote in message news:%230bEO3UUEHA.1012@TK2MSFTNGP09.phx.gbl... > use VC .NET 2003 > > Necessary to use whole the registry. > > SetRegistryKey - only for \\HKEY_CURRENT_USER\Software.. > > Help me... > > ...

CANNOT PROTECT
Trying to password and read only protect a Microsoft Word docuement that is embedded in Excel. When I open the "protected" document, the read-only prompt does not appear...and I can still make changes and close the docuement. HELP! Try this: create the document directly in word, protect it and save it to disk. Then copy the text you want and past it special as an OLE object in excel Peter ...

Activity Tracking #2
How can I purge older activity tracking data? For example, I'd like to have access to the most recent year or so, but would like to delete a lot of the historical info. We are running GP version 7.5 using SQL. Thanks, Hello Dan, You may want to look at table SY05000 (Activity Tracking) of the DYNAMICS database. If you want to purge information, you can run a DELETE statement and restrict it using the DATE1 column. rgds, timber On Oct 6, 9:47 pm, Dan <D...@discussions.microsoft.com> wrote: > How can I purge older activity tracking data? For example, I'd like to have >...

How to create a formula with multiple contraints and answers
Please help! :) I need a formula that basically says this: If cell C7 is less than 80%, then C8 equals 0% If cell C7 is greater than or equal to 80%, but less than 90%, then C8 equals 75% If cell C7 is greater than or equal to 90%, but less than 100%, then C8 equals 85% If cell C7 is greater than or equal to 100%, but less than 125%, then C8 equals 100% If cell C7 is greater than or equal to 125%, then C8 equals 140% Any help would be appreciated! One way: =IF(C7>=125%,140%,IF(C7>=100%,100%,IF(C7>=90%,85%,IF(C7>=80%,75%,0)))) Regards Trevor "torky1" <torky1@...

Help Needed: "IF" formula/logic too limited
I need to determine a value that is predicated upon the selections of about 6 or so drop down menus. It'd be General Text within drop-downs but final value is an Accountant price Basically if someone selects Option #1 out of Cells A-G's drop down menus, then the value of H1 = a cell on another page that fits the category of 1- 1-1-1-1-1 from the drop down menus. I don't know how to do this. I imagine it's possible but have no clue. Your help is greatly appreciated. From what you say, the meat of this problem lies in the layout/format of the data you have in this "...