Unprotect Then Reinstate The Protection Query
Jul 8, 2008
I am attempting to include protect & unprotect in a macro to allow a specific function to be carried out, then return to the sheet with the protection on. I use the following code to protect the sheet on closing the sheet.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.Protect Password:="fred", DrawingObjects:=True, _
Contents:=True, Scenarios:=TrueEnd Sub
My problem is when I run the macro to unprotect the sheet prior to caring out the required function it requests the password. Using this option would mean that I would have to issue the sheet passwords to al the users. Is there a workaround to this.
View 9 Replies
ADVERTISEMENT
Feb 21, 2007
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 2 Replies
View Related
Apr 25, 2009
My current headache is that I want to be able to reinstate excel formula when the delete key is hit. However there this is a slight catch. I don't just want to be able to do this for one cell but reinstate the same excel forumla in an entire cloumn (ie for multiple rows). On top of this I also need to find a way of being able to do this for multiple columns (ie column 1 has formula 1, column 2 has formula 2 etc.....)
I have found this but I am having trouble adapting it to suit my purpose. This code just reinstates the formula for 1 cell.
Worksheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address "$A$1" Then
Application.OnKey "{del}"
Else
Application.OnKey "{del}", "myFormula"
End If
End Sub
Sub myFormula()
Worksheets("Sheet1").Range("A1").Formula = "=RC[1]+RC[2]"
End Sub
View 9 Replies
View Related
Aug 18, 2004
I recieve an error :
Run-time error '50289':
Can't perform operation since the project is protected.
When i try and run my code.
The code does add parts to modules and workbook events, but I need the code to be protected.
View 9 Replies
View Related
Nov 26, 2012
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
View 3 Replies
View Related
Mar 29, 2007
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
View 2 Replies
View Related
Sep 6, 2008
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
View 9 Replies
View Related
Jul 8, 2008
I'm trying to query a query in Access 2003, from Excel 2003.
The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]
The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]
I use the following VBA code in Excel to excecute the query:
With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
View 9 Replies
View Related
Apr 25, 2006
I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.
My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..
View 2 Replies
View Related
Feb 24, 2009
Spreadsheet was created by me and worksheets locked by me with password. Data is maintained by another on the network. Since acquiring a new PC with xl 2003 replacing 2000, none of the worksheets will allow me to unprotect. The "unprotect" menu option is not available (shaded out), and it says "shared" at the top.
I have numerous spreadsheets that work this way, but this is the only one that has this problem. How can I unprotect it to make modifications?
View 2 Replies
View Related
Dec 25, 2008
When I protect the worksheet with the spinbutton, the spinbutton is no longer accessible. How can I modify the code below (provided by Leith Ross) to make the spinbutton accessible in a protected sheet?
View 3 Replies
View Related
Nov 23, 2006
I need a macro code to unprotect all sheets in a book. The sheets may range from 3 to 20 sheets, with a common password. I want to keep this as contol. Only one person will be using this.
View 9 Replies
View Related
Oct 16, 2009
does anyone know if I can do something like this:
Sub warehouse()
Dim Flag As Boolean
If .user = "martin.lucas" then _
Range("H:H").unprotect
Range("K:K").unprotect
Range("N:N").unprotect
Set Flag = True
End if
If Flag = False then _
Msgbox("you are not permittedd to do this. See Martin Lucas")
Set Flag = False
End sub.....
View 9 Replies
View Related
Oct 11, 2006
I have a workbook that has several macros. When the workbook is saved, it cycles through and kicks off several of them.
This works fine, no errors. I have another macro that saves and then emails the workbook. When this macro runs, the same set of macros is launched due to the save method however it errors during one of the macros.
Basically what's happening is that the one macro (derivecountry) unprotects a sheet and inputs a data value. This macro works fine when run independantly but when it runs as a result of the email macr, the sheet does not get unprotected and the error results.
Here is the code line that runs to unprotect but doesn't always work
ThisWorkbook.Sheets(strShtName).Activate
ActiveSheet.Unprotect (strPassword)
I've also tried
ActiveSheet.Unprotect password:=strPassword
View 4 Replies
View Related
May 3, 2007
I'm working with this workbook and suddenly the worksheets protected! Although I didn't make any password. I checked it but, I coulden't find any password. see code maybe there is a mistake.
View 7 Replies
View Related
Nov 17, 2009
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
View 4 Replies
View Related
Jan 23, 2013
I am using the following code to protect/unprotect a sheet in a tool that i am working on, which seems to work great. It unprotects the sheet runs the code inbetween the two liens of code and then protects the sheet again.
VB:
ActiveSheet.Unprotect Password = "mypassword"
ActiveSheet.Protect Password = "mypassword"
The issue I am having is if i want to go in and edit the sheet without running the code it will not let me. When I go in and type the password to unprotect it says it is invalid. I have typed it exactly how the code reads. I have even tried including the quotes but no luck.
View 4 Replies
View Related
Jun 16, 2014
We have a macro that opens a spreadsheet and pastes data from another sheet into it.
Is there a macro statement that will unprotect the spreadsheet if it is saved with protection (it is not password protected)?
View 1 Replies
View Related
Feb 2, 2014
It gives me a error and says my password is wrong. I have user names set to return the password so that it is unlocked for certain people. It works for unprotecting the sheets. I modified it and added it back in to try to do it for the workbook.
[Code] .........
I realize that they " and () around the password are differant, but that's how I last left them. I have tried several combinations and it just gives me more errors saying expected end of statement, etc.
View 8 Replies
View Related
Jul 10, 2012
I have a spread sheet that is protected. I would like to have cell G5 unprotected when a checkbox is checked. I am able to unprotect the entire sheet by checking the box but I only want G5 unprotected. My current macro that unprotects the entire sheet is
Sub Checkbox_Clear2()
Sheets("Input_Personnel").Unprotect ("1")
If Range("J12") = False Then Range("G5").ClearContents
End Sub
Is there a way to change the highlighted line where it only unprotects cell "G5".
View 1 Replies
View Related
May 5, 2014
I have a code that is supposed to go through a folder, open the xls file, replace all of the formulas with values only, then save as a xlsx file to get rid of the macros, however when we get into the next workbook it will not unprotect the sheet. instead it unprotects the origional sheet I sweat this worked last week when i was writing the sections and testing but maybe it didnt. The problem is without unprotecting the sheet i can not paste to it
Workbook 1 opens up, find that the date is expired, then goes through the folder and opens a workbook, is then supposed to unprotect the sheets ( this is where the problem comes in) save all the formulas as values and then as XLS in another folder.
I turned the text red in the offending area, everythign up to this point seems to work fine.
Private Sub Workbook_Open()
Dim printquotes As String
Dim OrigDate As Date
letmesave = True
OrigDate = ActiveSheet.Range("n2").Value
[Code] ......
View 3 Replies
View Related
Mar 20, 2007
I have a protected worksheet showing results of users maths tests. When users try to add data to this worksheet, a Windows Office window appears, advising it is a protected sheet, then gives instructions on how to Unprotect it.
I do not want users unprotecting this particular worksheet.
View 9 Replies
View Related
Jan 2, 2009
I have a worksheet where the users need to enter time. I have a macro that captures the time when the user hits Ctrl + t
What I want now is a code which protects the cell after entering the time with Ctrl + t.
I tried to do it, but my macro only works when I don't set any passwords to protect the sheet. How can I pass the password to the code and protect the sheet after the code to capture the time is being executed.
View 14 Replies
View Related
Sep 3, 2009
way for other staff to do the following...
Open an exiting protected spreadsheet,
SaveAs [newfilename]
Unprotect new sheet
Edit new sheet
Protect new sheet
Save it.
Hence we how have two protected sheets with different filenames and different contents.
Then, next time, the teacher will start again with the new spreadsheet file resulting in yet another version of the same data. etc etc.
View 9 Replies
View Related
Oct 9, 2009
I am working on a worksheet that has been protected off and on. I unprotected this AM, but XL will not allow changes and instructs to unprotect the WS, but it is already unprotected. The WB is not protected and never was. I am at a loss as to how to work around this problem.
View 9 Replies
View Related
Jun 1, 2006
We have created a spreadsheet that needs to be protected as it is viewed by several users. We would like, if possible, to update this daily with as little human intervention as possible.
Can we use VBE and the on worksheet open event to unlock the password protection, update the sheet and then lock it again? I know that you can read who opens the sheet using code similar to the following but don't know whether the password protection can be "unlocked" utilising this method.
View 6 Replies
View Related
Nov 9, 2006
I have an educational workbook which I am not familiar with. I cannot open vb project in vbe. When I try to open it says "project is unviewable". As far as I am concerned it should ask a password. But it doesn't. Can somebody tell me why. I am not asking somebody to crack it for me. I just want to learn why vbe doesn't ask a password. I think the only possibility is the creator of the program want nobody to open the code forever.
View 4 Replies
View Related
Dec 20, 2007
I need to be able to protect the sheets in this workbook but then I need them to be unprotected when I click the Save File As button and then have them saved again whenever the file is opened back up.
View 9 Replies
View Related
Jun 22, 2014
Is there is any way we can protect sheet in such a way that; if user - Mr. A inputs his password he can only go and is able to input his data in sheet 1.
I've a file which have multiple sheets say about 80 tabs are present. My colleague only works on one of the tab (sheet) enters data, that file is placed on our general sharing folder I want to know if it is possible that I can assign protection in such a way that when she enters her password she is able to input data only on the sheet in which she works and cannot roam around to other sheets?
Or is it possible that on first sheet there is username or password can appear? And if the data inputting person opens that file and enter her password option appear which can take her to that particular sheet? And if a guest open that file he/she can only view particular sheets which contain reports?
View 1 Replies
View Related
Mar 10, 2014
I have a workbook that is protected by password. I have to unprotect sheets.
How can I unprotect sheets and workbooks while I am updating or changing?
View 13 Replies
View Related
