Lock and Unlock cells using VBA

Hello All
Anyone know how to lock cells using VBA, also unlock others currently locked.
Page protection will be in use at the time this needs to be done.

Also, is there anywhere on the net with a complete list of VBA functions, 
including a short statement of what each one does, with or without examples.

If not, any good books that cover the above area. I'm really (strange as it 
might seem), enjoying playing with VBA and seeing exactly what it can do.

Regards
Peter
0
Peter2031 (452)
1/29/2005 3:33:01 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
628 Views

Similar Articles

[PageSpeed] 32

The easy questions...

Excel's Help is a very good source.

take a look at Peter Nonely's workbook that describes lots of functions:
http://homepage.ntlworld.com/noneley/
Peter's site isn't working, but Ron deBruin has a copy at:
http://www.rondebruin.nl/files/xlfdic01.zip

Debra Dalgleish has some of Norman Harker's files at:
http://www.contextures.com/functions.html

Debra also has a list of books:
http://www.contextures.com/xlbooks.html

Lot's of people swear by John Walkenbach's books.

=======
You can record a macro in excel when you do this kind of stuff.

Turn on the macro recorder:
tools|macro|record new macro
unprotect the worksheet
change a cell from Locked to unlocked and a different cell from unlocked to
locked.
reprotect the workbook.
stop the recorder.

Inspect/generalize the code...

this was my recorded code:

Option Explicit
Sub Macro1()
    ActiveSheet.Unprotect
    Selection.Locked = True
    Selection.FormulaHidden = False
    Range("A24").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Notice that the recorder doesn't display the passwords you entered.

One way of modifying the code is:

Option Explicit
Sub Macro1A
    ActiveSheet.Unprotect password:="hi"
    range("a1").Locked = True
    range("a24").locked = false
    ActiveSheet.Protect password:="hi",DrawingObjects:=True, _
           Contents:=True, Scenarios:=True
End Sub

But it would really depend on what cells are being locked/unlocked.


Peter wrote:
> 
> Hello All
> Anyone know how to lock cells using VBA, also unlock others currently locked.
> Page protection will be in use at the time this needs to be done.
> 
> Also, is there anywhere on the net with a complete list of VBA functions,
> including a short statement of what each one does, with or without examples.
> 
> If not, any good books that cover the above area. I'm really (strange as it
> might seem), enjoying playing with VBA and seeing exactly what it can do.
> 
> Regards
> Peter

-- 

Dave Peterson
0
ec357201 (5290)
1/29/2005 2:00:00 PM
Reply:

Similar Artilces:

Using HTML template in 2007/Vista
Not sure if this is the right sub-forum or not.... I'm able to install an HTML page as a template using Office 2007 on an XP machine, but the same process does not work on Vista machines. The template is saved to the proper location but the problem is that when Outlook opens the form, it opens as Plain Text rather than HTML. I haven't been successful finding any solutions googling the problem - anyone here know of a fix? -- rpw ...

Counting occurences of codes in cells
I have a spreadsheet with various codes separated by periods, in a single cell. Like: C T C,R T,C,R R and so forth. It's easy to count the single codes but how do I cound the "C" when it's with another character? There must be a simple way, without writing code, to do this. Is there? Thanks! maybe... =countif(a1:a10,"*" & "c" & "*") or =countif(a1:a10,"*c*") or if x99 contained the letter c. =countif(a1:a10,"*" & x99 & "*") salgud wrote: > > I have a spreadsheet with various codes separa...

How to use wincrypt.h?
Hi: I use it in a console app. I do this: The first I don't include .h file. Then add #define _WIN32_WINNT 0x0400 and #include <wincrypt.h> in the stdafx.h. compile ... error: error C2146: syntax error : missing ';' before identifier 'HRESULT' fatal error C1004: unexpected end of file found How to do? thanks! >error C2146: syntax error : missing ';' before identifier 'HRESULT' >fatal error C1004: unexpected end of file found > >How to do? Try adding #include <windows.h> before. Dave Thank you. "David Lowndes" ...

insert password using vb
I have multi worksheets in a template with formulas. I can hide th formulas but if I add a password, my macros stop and ask for th password when the macro is in a protected worksheet. Is there a way o adding the password to a macro so that the macro will run smoothl without stopping and asking for a manual insertion of the password? Thanks Do -- abbevill ----------------------------------------------------------------------- abbeville's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=976 View this thread: http://www.excelforum.com/showthread.php?threadid=26122 ...

same subform used in two different forms
Hi Folks, Is it possible to use the same subform in two different main forms simultaneously? If it's possible, is it good practice? I can foresee that there may be problems with code, but the subform I'm thinking of is very basic. Three tables: tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID FirstName MiddleName ....other personal data tblAddress AddressID AdFamID street city state zip Two main forms for different types of data entry frmFamily with family information on the main form and address information...

Error 425. Printer is locked
"Error 425. Printer is locked and cannot be accessed." Anyone know what causes this error? I have only the SO POS software running, and I also have the option in Configuration->Options set to share OPOS printers setting. Printer works in the Epson OPOS "check healthtouch" test. Hi, I have this same problem, and I don't have a solution. Have You found a solution for this error, 425? It is urgent, for me. Thanx in advance... Antonijo -- ADTodorovik "craiga" wrote: > "Error 425. Printer is locked and cannot be accessed." > >...

XML TreeNode.Parent property when using 2.0 TreeView?
How do I get a TreeNode.Parent property when using the 2.0 TreeView control? When the data source is an XML file there may be redundant names in the tree. For example, when a child node with the value of 'name' is selected I don't have a clue how to get the name of its parent which may be 'person' or 'school.' <%= Clinton Gallagher "clintonG" <csgallagher@REMOVETHISTEXTmetromilwaukee.com> wrote in message news:%23erL6WOXFHA.3532@TK2MSFTNGP10.phx.gbl... > How do I get a TreeNode.Parent property when using the 2.0 TreeView > control? &...

Conditional Cell Category
Is it possible to make a cell's category conditional on the value in another cell? I would like to have a cell display its value as a currency if the cell before it says "Monthly", and as a percentage if the cell before it says "Annual". I am working in Excel 2003. Thank you, Trey ~ On 29 June, 15:39, one third <g...@hotmail.com> wrote: > Is it possible to make a cell's category conditional on the value in > another cell? > > I would like to have a cell display its value as a currency if the > cell before it says "Monthly", and as ...

Not even Office Live Using Document Connection
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3331376369_11931625 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit While others are saying they can use the SP2 version of Document Connection to Office Live but not Sharepoint I can�t even get connected to my Office Live Workspace which I have been using via the browser for a long time. I can't add my Office LIve Workspace location to Document Connection. The gear just spins and what�s almost as bad it...

How to change cell data from all upper to title case
I need to change several columns of data from all upper case to Title Case. What formula should I use & How can I apply the formula to the data in each cell? Check out the Proper() function. It changes any case to title case, and can be copied from cell to cell to apply the formula to your data. Check out the Proper() function. It changes any case to title case and you can copy it to a range of cells to apply it to your data. You have other answers showing the use of the PROPER Function to act upon one cell at a time. If you want to change many cells at once you will need a mac...

determining macro status in a cell
Hi Is there a way to determine the status of macros (enabled or disabled within a cell formula? Thank -- Message posted from http://www.ExcelForum.com I don't believe this is possible. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "newengland >" <<newengland.11d8au@excelforum-nospam.com> wrote in message news:newengland.11d8au@excelforum-nospam.com... > Hi > > Is there a way to determine the status of macros (enabled or disabled) > within a cell formula? > > > Thanks > > > --...

line spacing in cell
I would like to type 3 pieces of text, each on a seperate line in one cell. I've looked under cell format Press Alt-Enter where you want to start a new line! Regards, Stefi „dlh” ezt írta: > I would like to type 3 pieces of text, each on a seperate line in one cell. > I've looked under cell format Thanks so much. Works great!! "Stefi" wrote: > Press Alt-Enter where you want to start a new line! > > Regards, > Stefi > > „dlh” ezt írta: > > > I would like to type 3 pieces of text, each on a seperate line in one cell. &...

Adding an Active X control to a cell
I would like to add a drop down lists to a bunch of cells. The control adds a box over the top of all the cells, trying to size it so that it fits over the top of the cell seems to be a rather inaccurate method of doing it. Hold down alt while you place it in the cell and it will fit the cell -- Regards, Peo Sjoblom "Rick" <rstenson@qualnetics.com> wrote in message news:317AA5B4-7970-45B2-8497-556670E48018@microsoft.com... > I would like to add a drop down lists to a bunch of cells. The control adds a box over the top of all the cells, trying to size it so that it f...

Can't use MS-Money with Bank of America???????????
I've used Quicken for a while and am using Quicken2003. I've just gave the new MS-Money2004 trial version a try and I like it better, so I was going to switch...... Until I discovered that Bank of America does not support MS-Money, only Quicken. That sounds crazy to me, but apparently this is the case. If I want to switch from Quicken to MS-Money I will loose the ability to download bank transactions automatically and must use the old fashioned manual download statement method. Does anyone know why a bank as large as Bank America doesn't support MS-Money? Does anyone...

Colouring cell if it has a specific date
Hi, I have a column for each month where the dates are entered on rows. Is it possible I can color a cell if the date entered in a cell for that month is 15 days past the end of the month. Like, if in a January column I enter a date on one of the rows as Feb.20, then the cell fills with red colour. I tried doing it using conditional formatting, but don't know how to say 15 days past end of the month? Any help will be appreciated! Thanks very much! Shivam Hi there Assuming 2007 If column A is January. I don't know where you have your reference date i.e. las...

Font property in Excel VBA causes 100 % CPU loop
I am using Excel 2002 SP-2 on a Windows XP machine running a Pentium 4 2.8 GHz processor with 1 Gb RAM. When building a VBA userform, whenever I click on the properties box to change the font size for e.g. a label or textbox caption, Excel starts another process (i.e. I see another Excel process on the Windows task bar) and consumes close to 100 % of my CPU. The first couple of times I tried this, Excel eventually brought up the font properties window (after more than a minute). The third time I tried this, I had to crash Excel via Windows Task Manager. At the time of trying to change the ...

How do I set up a stacked bar chart using military times?
When my values are time, whenther they are reagular or military, the bottom bar is correct, but the others are not. As anexample, if I want a stacked bar that shows the following 3 times: start work 8:00 lunch 12:30 clock out 16:30 how do I get the bars displayed correctly as well as the "displayed values"? I have just put up a proposed solution for you at:- www.pierrefondes.com - first (top) example on the home page. If my comments have helped please hit Yes. Thanks! "Healthcare Quality Analyst" wrote: > When my values are time, whenther they ar...

Timestamp cell to left after update
For every row when cell C<whatever> is updated I need coresponding cell D<whatever> to update with a date time stamp until such a time that particular cell C is updated again. Been all over the place looking at macros worksheet functions etc. and nothing quite matches what I need. Currently I am using Ctrl-Shift-; followed by Ctrl-; to quickly enter this, but there has to be a way where I do not have to enter it all. Thanks in advance Hi see: http://www.mcgimpsey.com/excel/timestamp.html -- Regards Frank Kabel Frankfurt, Germany "Tahlmorrah" <Tahlmorrah@discus...

preserving cell formatting inside a CONCATENATEd string
Hi to All, I have formatted cell C5 as follows: ##"F1" I entered the number 1712 cell C5, which gives the desired result 1712F1 Now I want to CONCATENATE(C5,xx,xx,xx) to give result 1712F1 xx xx xx but what I get is 1712 xx xx xx Note that the formatting of cell C5 is variable and will change, for example from ##"F1" to ##"P1", ##"F2" or to something else. In each case I want the CONCATENATED string to correspond to the present format of cell C5, for example 1712F2 xx xx xx How can I achieve this variable format in my CONCATENATED string to automat...

Move one cell to another
I need to copy the content of one cell to another when a different cell is greater than 239. Example: if N20 is greater than R20(value 239) then copy the content of B20 to S20. OR A method to delete all records that have a value less than 240 in column N. Any help would be appreciated. Thanks, Mike -- Mike Formula in S20: =IF(N20>R20,B20,"") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Mike" <Mike@discussions.microsoft.com> wrote in message news:219BB2BA-6CEF-4E74-AB00-DCFCEB84BAED@micro...

Anyone know how to use hex formula that reads a cell? I want to .
Anyone know how to write macro/formula that will convert "a column" of Hex to decimal? I'm looking for a hex conversion by cell content, rather than hard coded value in "quotes". How about the Hex2Dec worksheet function found in the analysis toolpak. See Excel Help on the Hex2Dec function for details. -- Regards, Tom Ogilvy "KarenSue33" <KarenSue33@discussions.microsoft.com> wrote in message news:CD60372E-351F-4A73-8957-DF685D0F7EDD@microsoft.com... > Anyone know how to write macro/formula that will convert "a column" of Hex to >...

EFT using ACH and auto settle
Our client is using EFT for receivables and payables and is setting up banking information for an ACH bank. The bank requires a summary record within the file traditionally setup as an "auto settle indicator". How is this record achieved? Surely someone has run into this before. The bank requires an auto settle line in the ACH file coming from EFT Payables or they will no accept the file. Essentially this is a summary line. There is a setting in payroll for the line but I have yet to find a similar setting in EFT-Payables. I sent an ACH payable test file to the bank and t...

Automatically numbering lines and columns in a chart using Excel
I have a question.......I know it can be done, but I was wanting to know if say, you have a grid chart made. How do you go about automatically numbering the lines and columns? ...

how do i shade cells that contain a formula
It seems like it should be possible to do this with conditional formatting, but i can't find a function that detects whether a cell contains a formula. any help on this is much appreciated. thanks. You would need a UDF for that, nothing built in http://www.mvps.org/dmcritchie/excel/formula.htm Regards, Peo Sjoblom "peter" wrote: > It seems like it should be possible to do this with conditional formatting, > but i can't find a function that detects whether a cell contains a formula. > any help on this is much appreciated. thanks. > > Than...

Use XSLT to add header text to CSV
Hello, I'm trying to convert an xml file to csv using xslt. I can get the data to write out to the csv. I want to put a line of text at the beginning of the csv file that describes the content of the file. How do I do this using xslt? Thanks! stephen.surina@ferc.gov wrote: > Hello, > > I'm trying to convert an xml file to csv using xslt. I can get the > data to write out to the csv. I want to put a line of text at the > beginning of the csv file that describes the content of the file. How > do I do this using xslt? In the template for the document root, ad...