Password in vba code

I have a worksheet that I want to protect but needs 
amending and saving when a macro is running.  how do I 
unprotect using a password and re protect using same 
password in my code?
0
anonymous (74722)
8/6/2004 2:37:12 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
447 Views

Similar Articles

[PageSpeed] 50

Sub test()
With Sheets(1)
..Unprotect Password:="Jolene"
..Cells(1, 1).Locked = True
..Cells(1, 1).Value = Time
..Columns(1).AutoFit
..Protect Password:="Jolene"
End With
End Sub

HTH. Best wishes Harald
'
"MikeyMay" <anonymous@discussions.microsoft.com> skrev i melding
news:01ea01c47bc2$dc9ac960$7d02280a@phx.gbl...
> I have a worksheet that I want to protect but needs
> amending and saving when a macro is running.  how do I
> unprotect using a password and re protect using same
> password in my code?


0
innocent (844)
8/6/2004 2:44:26 PM
Mikey,

If you're unprotecting it for the user to make changes, unprotect it in
code.  If you're wanting a macro to be able to make changes, you can protect
it against the user, but not against the macro with

Sheets("Whatever").Protect UserInterFaceOnly:=True, Password:="MyPass"

Now the code can mess with the sheet, but the user can't.


-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"MikeyMay" <anonymous@discussions.microsoft.com> wrote in message
news:01ea01c47bc2$dc9ac960$7d02280a@phx.gbl...
> I have a worksheet that I want to protect but needs
> amending and saving when a macro is running.  how do I
> unprotect using a password and re protect using same
> password in my code?


0
nowhere1083 (630)
8/6/2004 4:55:38 PM
Note that the UserInterfaceOnly switch is not persistent - you'll have 
to set it every time the workbook is opened.


In article <eHAy0Y9eEHA.2532@TK2MSFTNGP09.phx.gbl>,
 "Earl Kiosterud" <nowhere@nowhere.com> wrote:

> If you're unprotecting it for the user to make changes, unprotect it in
> code.  If you're wanting a macro to be able to make changes, you can protect
> it against the user, but not against the macro with
> 
> Sheets("Whatever").Protect UserInterFaceOnly:=True, Password:="MyPass"
> 
> Now the code can mess with the sheet, but the user can't.
0
jemcgimpsey (6723)
8/6/2004 6:55:48 PM
Reply:

Similar Artilces:

Outlook 2000 doesn't remember email password
I cannot get Outlook 2000 to remember my email password, with the consequence that I have to send and receive emails manually. I was directed to Microsoft Knowledge Base Article - 259416 which in turn prescribes an edit of HKEY_CURRENT_USER\Software\Microsoft\Protected Storage System Provider. The problem is that that file does not seem to exist on my comptuer (WIndows XP Home). I would welcome advice. ...

Business Type Code Vs Industry
Hello, Can someone explain the difference between Business Type Code & Industry? What is the role or purpose of Business Type code. If anyone knows of any resources that could explain the intended purpose of this and some of the other fields on the account object such as Classification, and Category that would be great. Hi, It totally depends upon your business requirments. If you think some field is not required then just remove it or modify it to suit your requirments. There are many attributes of Account which are not even shown on Account Form by default but they are there in ...

Code Comment Web Report,
I have enabled VC++ XML comments in Visual Studio 2005. However, I do not see the "Build Comment Web Pages" under the Tools file menu (which my book says should be there--but the book was written for VS02). Did they get rid of Build Comment Web Pages in VS05, or simply move it? > I have enabled VC++ XML comments in Visual Studio 2005. However, I do not > see the "Build Comment Web Pages" under the Tools file menu (which my book > says should be there--but the book was written for VS02). Did they get rid > of Build Comment Web Pages in VS05, or simply ...

output to
I created a button to save a report in a designated folder. The date parameter is a box on the form the query for the report runs off of. I want to include that date field in my report so I know what parameters I ran the report on by looking at the contents of the folder. I tried to code the start date into my stOuputFile portion but didn't do it correctly. Here's my code so far for the button. Let me know if anything should be cleaned up as well. Thanks. Private Sub Cmd_MailEngineerRpt_Click() On Error GoTo Err_Cmd_MailEngineerRpt_Click Dim stDocName As String stD...

VB code
Hi all, I am trying to create a macro using VB. Here is what I want my macro to do : I have file1 and file 2 saved in each monthly folder like c:/month/file1.xle c:/month/file2.xle I have a spreadsheet like this A B C D E Date Unit No. Sale Amt Source1 Source2 9/4/2005 UN01 $2.00 6/8/2004 UN02 $3.00 10/5/2004 UN03 $5.00 My file1 like this colum A colum B Unit No Source1 Un02 10 Uni03 12 Now I need go to file1 to find data for Colum D(Source1) by using vlookup function based on the unit number then extrive the data to colum D and times 50%. So after I run the ma...

outlook running on XP Pro in workgroup mode prompts for password on exchange 2003
we have a windows 2000 network with exchange 2000 local user are members of the domain, remote offices connect via Lan to Lan VPN with 5 or more computers running in workgroup mode. The users in the workgroup have local accounts on there XP pc that match the domain username/password. Currently they local to the exchange server with any issues. Outlook passes there username/password to the exchange server and logs then on. I have added a windows 2003 server and promoted it to a backup ADS server, added another windows 2003 server as a member server. Installed Exchange 2003 SP2. Move all the lo...

Excel VBA CPU Usage
I have a workbook that does a bunch of statistical calculations through VBA macros. Runs can take a couple of hours on my (inferior) machine at work (1.4GHz P4). When I run the same macros on my 2.8GHz P4 at home, it actually ran slower! I checked out CPU usage in Task Manager, and Excel was only using (exactly) 50% of the available CPU. The rest was System Idle. If I run another program at the same time, they each use 50%. If I run other programs by themselves, they use up to 100%. Excel on my machine at work runs at 100%. Running the macros does not use up all available syst...

Trapping SQL errors in VBA
ok... making progress on this relinking SQL tables routine. I was getting wierd results before apparently because I was not passing a UID; it was using my Windows logon (by default I guess) so automatically using a trusted connection. Now I've got another problem! When I intentionally use an incorrect password, I get a popup window with Connection Failed: SQL State '01S00' SQL Server Error: 0 <more lines of similar stuff> but VBA does NOT detect an error. Any idea how I can trap this error? Or is there some other way to test whether a SQL uid/pwd is valid before I start...

VBA to select formula cells OK -but want certain cells with ColorI
Using XL 2003 & 97 The following subroutine works. That said, how can I fine formula cells with a "!" in the cell. (In short, probably a cell formula referring to another sheet) With the following code I would like all formula cells set to colorindex 6 (yellow) but all formula cells with "!" set to a color index of 3 (red) I tried to use an If statement but to no avail. Sub SelectFormulaColor() Selection.SpecialCells(xlCellTypeFormulas, 23).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With ActiveWindow.Dis...

Where best to place an instruction in a VBA script
Hi All I'm using this code when I open my workbook. Private Sub Workbook_Open() Dim mysheets As Sheets Set mysheets = Worksheets(Array(1)) For Each Sheet In mysheets Sheets("ShareSheet").ScrollArea = "A1:J27" Next End Sub I'd like cell A200 to be selected and have the cursor box on it on opening the worksheet. I'm trying to place this line in the code : Application.Goto Reference:="R200C1" but can't get it to work. Can someone advise please? Grateful for any assistance. Best Wishes hi Range("A2...

Enter Network Password dialog box
Since we upgraded to Office 2000, I receive the "Enter Network Password" dialog box every time email is received. The server, user name, and password are all filled in. I have the option to OK or Cancel. Selecting either will close the dialog box and bring up the Microsoft Outlook new mail has arrived notification. How can I get the "Enter Network Password" dialog box to go away? If numerous emails come in, the dialog box pops up for each one. Chris Bauer ...

password for workbook
Hello, How can i set the workbook so that when opened, a dialogu appears asking for password to open up the workbook before being abl to read or Read/write anything.... Thanks a lot From Joh -- Neo ----------------------------------------------------------------------- Neo1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3032 View this thread: http://www.excelforum.com/showthread.php?threadid=53146 One simple way, although the password input is not masked '----------------------------------------------------------------- Private Sub Workbook_Open() '...

Office Document Connection
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel No matter how many times I click "save my password in keychain" it always asks me for my password when I open up the document connection. Additionally, when I click "new file" , then edit the file and save it does not save to the location I was on the SharePoint site but locally. Other than that great work, this saves me so much time, I can't wait to have it fully integrated in Office! There might be a problem with the Keychain database. Open Keychain Access, then click on Keychain>Keych...

Conditional Formatting VBA with formula to find string
Hi, Please can I get some help.... I need to create a conditional format VBA in Excel 2007 (because I have many conditions to include) and I don't know how to do it... The Action Required: If Product A appears in any text string in range($C$95:$C$300) then colour that cell RED, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell BLUE, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell GREEN, and so on through 41 products... Data - I have a list of about 41 Product names in range Z...

VBA in .mdb vs .adp
Hi, I just start reading about projects in Access, I have learned a lot of VBA in a .mdb application, my question is, the VBA code in a access project is the same that in a .mdb application? or I need to learn a new way of programing. Thanks Orlando <Orlando@discussions.microsoft.com> wrote: >Hi, I just start reading about projects in Access, I have learned a lot of >VBA in a .mdb application, my question is, the VBA code in a access project >is the same that in a .mdb application? or I need to learn a new way of >programing. > >Thanks The VBA programming lang...

Enter Network Password #4
I keep getting a dialogue box entitled Enter Network Password pop up for each of my mail accounts. while running Outlook XP (fully service packed) under XP Pro. It lists mail server, User and password (shown as *******) and an unticked `save this password in your password list' box. Needless to say, ticking this does not prevent the event from recurring fairly frequently. Has anyone come across a solution to this? I have seen the problem on many sites but with no solutions. TIA Toddy ...

A SQL statement in VBA
On the main form is a subform in datasheet view and 5 command buttons. On the click of one button I have an event procedure that should run this query : SELECT DISTINCT [Client Extended].ID_client, [Client Extended].date_ouverture, [Client Extended].nom_client, [Client Extended].date_naissance, [Client Extended].couriel FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client = Produit.REF_client WHERE ((([La première date :])<=[date_livraison]) AND ((Produit.date_livraison)<=[ La seconde date])) GROUP BY [Client Extended].ID_client, [Client Extend...

Network Password entered each time Outlook starts
I have Office XP Pro with Outlook 2002 and EVERY time I start up Outlook it asks me to enter my Network Password. I do and check the box to remember my password but is shows up again next time. Anyone know how to get it to accept the password? I had Office 97 and Outlook 97 before and never had this problem. I have already installed Office XP Service Packs 1 & 2. brunyon@knoxy.net If you are still having problems after applying both the SP 1 & 2 patches, then see if this article addresses your problem OL2002: The Save Password Setting Is Not Saved When You Connect to a POP...

Does any have any sample code for Store Manager Addins
I'd like to jump start some development efforts and would like to see some examples of how ADDINs have been used. Anything that hits the PurchaseOrder Table would be perfect.... My understanding is that samples are availble to certified partners at the PartnerSource web site: https://mbs.microsoft.com/partnersource/products/rms/documentation/installationsetupguides It is not clear to me if customers have direct access to it or not. "Espo" wrote: > I'd like to jump start some development efforts and would like to see some > examples of how ADDINs have been used...

On error displayes default error code instead of the specifyed one
Hi, I have added error handling code to my combo box. Private Sub searchName_AfterUpdate() ' Find the record that matches the control. Dim rs As Object On Error GoTo ERR_Handler Set rs = Me.Recordset.Clone rs.FindFirst "[sdutentId] = " & Str(Nz(Me![searchName], 0)) On Error GoTo 0 Exit Sub ERR_Handler: MsgBox "Please empty search box before continuing!" End Sub But it doesn't display the message in msgbox, it displayed default ms access error code. which is "The text you have entered isn't an item in the ...

Excel VBA
Hi, Is there any way to freeze (prevent) a number of worksheets from recalculating when a VBA macro is running? I have a spreadsheet with about 10 worksheets, most of which are linked to each other in some way. One of my macros only needs to use the formulas (and related results) from about 7 of these, so the other 3 are updating all the time but since the results from these are not needed then they are slowing down my code. Is there some VBA function for temporarily disabling a worksheets calculation function? I would need my code to look something like this: Code: -------------------- ...

Escape codes embedded in XML
Hello, I have built an XMLDocument object instance and I get the following string when I examine the InnerXml property: <?xml version=\"1.0\"?><ROOT><UserData UserID=\"2282\"><Tag1 QID=\"55111\"><Tag2 AID=\"5511101\"></Tag2></Tag1><Tag1 QID=\"55112\"><Tag2 AID=\"5511217\"></Tag2></Tag1><Tag1 QID=\"5512282\"><Tag2 AID=\"551228206\"></Tag2></Tag1><Tag1 QID=\"55114\"><Tag2 AID=\"5511406\"></Ta...

AutoPublish code
Hi, I'm looking for an autopublish product for a customer of mine. I know of one, but I'm trying to create a list of options for my customer, so if you know of one, can you please reply to this post with details. -- Regards, Ben. I've seen a couple folks develop macros to publish all projects. Not sure if that's what you're looking for.... - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > Hi, I'm looking for an autopublish product for a customer of mine. I > know of one, but I'm trying to create a list of options for m...

Setting x-axis crosses with VBA
I have a chart that at the moment has a vertical range from -70 to -92. I'm trying to set the horizontal axis to cross at the minimum value, in this case -92. Using With ActiveChart.Axes(xlValue) .Crosses = xlMinimum It crosses at -70. If I use xlMaximum I get the same thing. Using .Crosses = xlCustom .CrossesAt = -92 it crosses at -92. however, I need it to be dynamic so the x-axis labels stay below the data field. Am I using xlMinimum in a place it cannot be used? Any help will be appreciated. In the format axis dialog, there is no way to specify tha...

Exchange/Outlook security problems Password What??
Hello everyone, I hope one of you Exchange/Outlook users out there can help solve this problem. This has all started out by needing to turn off the "Something's looking at your email addresses" nag box in Outlook 2003, in order to get a macro written in Outlook 2003 to work properly. According to what I have read and hopefully understood from Microsoft's support documents ("Customizing the Outlook Security Features Administrative Package" is that these nag boxes can be bypassed with the Outlook Security Features Administrative Package. I have set up a form ...