Automate unprotecting of blank cells

I have a spreadsheet that has several cells with values entered int
it.

I want others to add some values, but I don't want them to change cell
that already have values.

I know that I can use goto, special, constants and protect thos
cells.

Can someone recommend a macro or method to review each cell within 
specified range and unprotect it if it is blank?

Any suggestions would be appreciated.

Jim Palme

--
Jim Palme
-----------------------------------------------------------------------
Jim Palmer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=521
View this thread: http://www.excelforum.com/showthread.php?threadid=46686

0
9/12/2005 5:39:12 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
363 Views

Similar Articles

[PageSpeed] 39

Record a macro when you select your range
Edit|goto|special|but click blanks
then format|cells|protection tab|uncheck Locked



Jim Palmer wrote:
> 
> I have a spreadsheet that has several cells with values entered into
> it.
> 
> I want others to add some values, but I don't want them to change cells
> that already have values.
> 
> I know that I can use goto, special, constants and protect those
> cells.
> 
> Can someone recommend a macro or method to review each cell within a
> specified range and unprotect it if it is blank?
> 
> Any suggestions would be appreciated.
> 
> Jim Palmer
> 
> --
> Jim Palmer
> ------------------------------------------------------------------------
> Jim Palmer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5210
> View this thread: http://www.excelforum.com/showthread.php?threadid=466861

-- 

Dave Peterson
0
petersod (12005)
9/12/2005 6:13:53 PM
Thanks Dave

I was looking for "blanks" on the go to special screen, however 
didn't notice it.

Thanks for your help.

Jim

My new glasses arrive at the end of the week

--
Jim Palme
-----------------------------------------------------------------------
Jim Palmer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=521
View this thread: http://www.excelforum.com/showthread.php?threadid=46686

0
9/12/2005 9:19:11 PM
LensCrafters--glasses in an hour!

<vbg>

Jim Palmer wrote:
> 
> Thanks Dave
> 
> I was looking for "blanks" on the go to special screen, however I
> didn't notice it.
> 
> Thanks for your help.
> 
> Jim
> 
> My new glasses arrive at the end of the week.
> 
> --
> Jim Palmer
> ------------------------------------------------------------------------
> Jim Palmer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5210
> View this thread: http://www.excelforum.com/showthread.php?threadid=466861

-- 

Dave Peterson
0
petersod (12005)
9/12/2005 11:09:32 PM
Reply:

Similar Artilces:

deselect cells within a range of cells
I have found this to be most frustrating!!! Windows Explorer allows you to select an entire list of items and then deselect individual items by holding the ctrl key down and clicking the items you wish to deselect. Why does this not work in Excel??????? I have a list of 100 items and wish to deselect about 10 that are randomly dispersed in the selection. This seems to such a simple thing, but I've found no solution. Try this: =INDIRECT("E"&C1) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all m...

Specifying next cell to "jump" to
When a user completes as much of a specified cell range as necessary an wishes to move on to the next range, I would like to give them a option to go "automatically" to the first cell of the next range a opposed to having to tab through the remainder of the range or click i the first cell of the range they wish to go to. For example, the firs range is A10 through D25. After inputting data in D15, the user want to go to the next range, the first cell of which is A30. How can th user most quickly and easily go from D15 to A30? I appreciate your help -- Message posted from http://...

on last enter start new blank form
is there a way to have a new blank form start after entering last input of information? Thanks in advance for the help! If you set the form's property to add new records, and add a <Save> command button, you should get "a new blank form". If you're asking how to have it happen without the <Save> button, how will Access "know" when you're finished entering data? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a produ...

Slight problem automating Excel in a service
I hope someone can help me out here. I'm using VB.Net and VS .Net 2003 to write code that automates Excel 2003. Basically, a FileSystemWatcher object watches a folder and it's subfolders if a user desires. If an Excel generated HTM files is output to the folder(s), the FSW detects the creation of the file, loads it into Excel in the background (visible = false), and prints out the document. I wish I had a copy of the code to paste (forgot to bring some home with me). Basically, I let the program thread sleep for 1 second (to ensure the file is fully written out), I just creat...

Locking certain cells
I have an expense reporting template which I have locked certain cells that I do not want changed while allowing input into other cells. This is under the tools functions but its not coming to me right away how I did this. The users do not need to use any passwords --its just certain cells are read-only and cannot be changed unless unlocked. How'd I do this I need to update? Disregard- figured it out again "Scott" wrote: > I have an expense reporting template which I have locked certain cells that I > do not want changed while allowing input into other cells. Th...

Blank lines on line input
Hi I'm using a recordset to read a text file and input the data into an access 2000 table. My problem is I want the line input to treat a blank line as eof, there is information after the blank line but I don't need to import it. This is my code Function ap1() Dim Filepath As String Dim Currentfile As String Dim rs As DAO.Recordset ' Database Dim fh As Integer ' File handle Dim lc As Long ' Line counter Dim ln As String ' Actual line Dim sp As Long ' Position of first space Dim thefile As S...

IF number is grather than 100 color the cell red
I did this before but I can't remember how I did it. I want to have a column of numbers and if they are greater than a certain value ie: 120 , I want the sell to have the color red. Any ideas how to do this in Excel 2007? THanks Take a look at format|conditional formatting. Ron Boetger wrote: > > I did this before but I can't remember how I did it. I want to have a > column of numbers and if they are greater than a certain value ie: 120 > , I want the sell to have the color red. > > Any ideas how to do this in Excel 2007? > > THanks -- Dave Peterson ...

Removing Excess Digits From Cells
I have two lists of numbers that I'd like to use to join two tables in Access. One list has an extra "Check Digit" at the end. I'd like to know how to remove the "Check Digit". (It may not matter but one of the characters in the list of "numbers" is a letter.) I tried to "Format Cells" by typing in one less ###-##-#### but it did not do anything. Hi Raquel, try using the formula =LEFT(E6;1) for a column next to the list and use E6 for every cell... so you get rid of the last digit. Best Markus >-----Original Message----- >I hav...

"De-name" cell references in formulas
Hello, I'm working in Excel 2003 SP2, and I'm trying to replace all references to named ranges used within my formulas within a workbook with the underlying absolute cell references. I found a solution in the archives of this newsgroup ( http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/4ebb9d1ee0076045/a0d6406155d03ccc#a0d6406155d03ccc ) , but I can't get it to work (neither the manual version nor the macro version). My only guess is that the behavior has of TransitionFormEntry has changed in Excel 2003. If so, is anyone aware of any other solutions. I found...

Copying Info Into Numerous Cells
I have just completed creating and entering data into thousands of worksheets. Now someone has decided they want to change and add columns to my worksheets. Is there any way to have data copied from 1 cell to another in several worksheets at a time, even if the data is different? If there isn't...I'm going to have a melt-down right here at work! :eek: -- calimari ------------------------------------------------------------------------ calimari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24537 View this thread: http://www.excelforum.com/showthr...

Unprotecting multiple worksheets w/ Macro
I am protecting with a macro, multiple worksheets in a workbook with a macro. I can't get the unprotect of multiple worksheets to work. Does anyone have a working macro to do this. _____________ Tippy Sub UnprotectSheets() Dim sh as Worksheets for each sh in ThisWorkbook.Worksheets if sh.ProtectContents or sh.ProtectScenarios or _ sh.ProtectDrawingObjects then sh.unprotect Password:="ABCD" end if Next End Sub -- Regards, Tom Ogilvy <tippy@att.net> wrote in message news:c7vlpv0sqb9eov641aj31ohnqnps7hvgvm@4ax.com... > I am protecting with a macro,...

How to select a sheet and input data into certain cells
I have 36 sheets, sheet1 is my menu, also on sheet1 I created in colum AB1=1stQTR AC1=2ndQtr AD1=3rdQTR AE1=4thQT AA2=2004 Sheet2 Sheet3 etc . . . AA3=2005 Sheet6 Sheet AA4=2006 Sheet8 Sheet AA5=2007 Sheet4 Sheet I would like to be able to select a year and a quarter and it goes to the sheet, i.e 2005 2ndQtr goes to sheet5 and in a couple of cells input 2005 and 1st Quarter I would also like to be able to print sheet5 and ask me if I want to print it agai or close and upon closing go back to sheet Is there an...

Inserting extra info into a cell
I currently have three columns in a spreadsheet. This spreadsheet is used as a bilingual glossary of terms. The first column simply contains the following: ** Now the second column contains my Spanish words and the third column my English words. So, it looks something like this:- ** Hola Hello Now, I wish to import this data into some new software I have however, it requires me to put the word <Spanish> before every words in the second column and <English> before every word in the third column. So, in essence, it will be ** <Spanish>...

Conditional Formatting Across Multiple Cells
I have data file that I want to conditional format Red/Green fill based on >= conditional on a single row of data. In Excel 2003 it was fairly simple to lock a row but not a column and by selecting all the cells to format it would change the column relative to the cell. Just having an issue transitioning to conditional formatting in 2007. Sample Data a b c d 1 goal 10 245 125 2 day1 8 200 76 3 day2 8 250 125 4 day3 15 250 130 5 day4 15 300 150 6 day5 0 100 0 7 Avg 9 220 96 Cells Rows 2 - 7 should be conditional on Row ...

Ignoring blank cells in a combo box...
Is there any way I can ignore blank cells when using a combo box fro the forms menu? For instance, my combo box is being filled by th range A1:A5. However, if A3 is blank, I don't want it ( a blank) t show up in the drop down box. Is there is a more efficient way to d this or it might it be more simple to use a combo box from the contro toolbox? Any help would be appreciated. Thanks -- Message posted from http://www.ExcelForum.com The combobox from the Forms toolbar is also called a DropDown. And you could use a little code that filled up that dropdown: I chose to put it into the...

Return a number in one cell to long hand text in another.
Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents Take a look at http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP "CP" <CP@discussions.microsoft.com> wrote in message news:C0D9B8E3-3D66-4EA0-9184-57762BD663D6@microsoft.com... > Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents Go to Google and search the newsgroups for "spell number" On Wed, 20 Oct 2004 15:07:02 -0700, "CP" <CP@discussions.microsoft.com> wrote: >Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents ...

Locking cells #3
hi there, I was wondering if it is possible to Lock a cell so that no-one ca enter data into it, the reason behind this is because the cell i update from a different worksheet. Thank you for your time Kind Regards Swmasso -- Message posted from http://www.ExcelForum.com Hi It's possible. Simply protect the sheet - by default all cells are locked then. When you want to allow users to access some cells, unlock them before protecting the sheet (Format.Cells.Protection). When you want to restrict the access to some cells only, and to allow for rest of worksheet, then unlock all cells ...

What is the warning triangle in a cell and how do I get rid of it.
I'm using the new Office 2004 for the Mac and first, I hate it! But, how do I get rid of the green (warning?) triangle in the cells. I haven't entered a equation and I have no idea where it came from. Please help. I don't have a Mac but you may be seeing the background error checking flag. If so, you can turn off this feature (in Windows versions at least) under Tools, Options, Error Checking. -- Jim "confused" <confused@discussions.microsoft.com> wrote in message news:71CF7B27-CD53-4E18-805A-7F8FFE79FB27@microsoft.com... > I'm using the new Office 2...

Multiplying cells
I am creating a spread sheet that with each Monday in a calendar year, I am needing to multiply 8 weeks, 13 weeks, and 26 weeks out. Please help =if(weekday(Today()) = 2, date+(8*7),"") format as date Change 8 to 13 and 26 for your other dates. if you need it to show these dates for the remainder of the week =Today()-(weekday(Today())-2)+(8*7) format as date. Again, use this formula and change 8 to 13, then 26 for the other dates. the date would actually advance a week on Sunday. -- Regards, Tom Ogilvy "jamie" <tscharbrough@quest-grp.com> wrote in message...

pasting of variable cell value into macro
I have written a macro that runs an autofilter which grabs cell info from a different worksheet within that workbook (an entered date). It then uses that date to autofilter. It works fine the first time, but aparently plugs that info permanently into the macro and will not work on subsequent runs. I need a macro that will grab variable info from a certain cell each time and run with that new cell value each time. Any ideas?. (& yes, I am a newbee) sorry.. Your problem is refreshing the autofilter. It is best to clear the old filter before applying the new filter. Here ...

Problem with Chart drawing blank cells as zero
Hi I am currently trying to create an excel line chart showing cumulative baseline vs Cumulative Actual vs forecast actual lines (the forecast line is a continuation of the Actual to date line) which I have done - but am trying to automate it so it is 'more user friendly' and can be created at the touch of a macro. The problem I am having is that one of the formulae i am using looks up a date in the timescale and if it is in the future I want it to leave the cell bank (using the "" option" - problem is the chart then decides this is not a blank cell it is a zero ...

Finding Merged Cells
Is there any (easy) way of finding a cell or cells that have been merged? I am trying to sort a (very large) spreadsheet but can't because Excel "...requires the merged cells to be the same size..." I can always select all and remove the merge - but I am wondering why there are merged cells - hence the reason for trying to find them! Many Thanks - I like the macro solution - it certainly works on a test setup - I'll try it on the real spreadsheet tomorrow >-----Original Message----- >Manually, you could divide and conquer. Select half the range, hit ctrl-1 (t...

Password protect cells
I have a sheet that people need to enter data into, but I have a problem with people deleting the formulas in other cells. Does anyone know if there is a way to password protect one or several cells without protecting the entire sheet? You can lock cells or unlock cells (format|cells|protection tab). After you lock/unlock cells, you can protect the worksheet (tools|protection|protect sheet) When the worksheet is protected, the user won't be able to changed the locked cells, but will be able to change the values in the unlocked cells. There are other things that are disabled when the...

Reference a cell value in a formula
I have the following formula: =SUMPRODUCT((MONTH(Summary!$F$7:$F$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!$E$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...

automatically import data from a cell in one workbook to another workbook
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How do arrange for workbook A to import the data from a cell in workbook B? In other words, what formula (address?) should I enter in a cell in workbook A so that it will import the data in a cell from workbook B? This would be just another formula; start with =+ and then just use the mouse/trackpad to click on the referenced cell (assuming you have both wordbooks open. Be careful of relative/absolute references. Thanks, Mungo <br><br>Cheers <br><br>Karshish 1) Make sure both workbooks ...