Basically, if they dont enable macros it hides all other sheets.
when macro are enabled it adds an expiry date which when date is met workbook expires.
Heres the code in all its (weird) glory .....
I want to run a macro in a worksheet which is proteced.
It's a simple macro which erases the content in (some) cells, nothing more. Even those celles are formatted as unlocked the macro fails.
I have protected several sheets in my workbook to protect the forumlas and data for being tempered with by the users.
When I try to run my macro, I get an error message stating:
Runtime '1004'
You can not use this command on a protected sheet
And the debug is pointing to this part of my code
I have the following macro and when it "reprotects" my worksheet I need it to also turn on these Protect Sheet properties:
Format cells
Format columns
Format rows
Insert hyperlinks
and I need it to leave on the defaults of Select locked cells and select unlocked cells
Sub Spell_Check()
ActiveSheet.Unprotect Password:="pmo"
Cells.CheckSpelling "SRdictionary.dic", SpellLang:=1033
ActiveSheet.Protect Password:="pmo", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub
I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:
View 14 Replies View RelatedI have certain cells which I want the formulas hidden to the end user and protected so they can't update them.
I've locked and hidden these cells and protected the sheet however my Macro's fail to work thereafter.
Protect the sheet for the user interface only. This has to be done in VBA:
- Activate the sheet.
- Activate the Visual Basic Editor (Alt+F11).
- Activate the Immediate window (Ctrl+G).
- Type
ActiveSheet.Unprotect
- Press Enter.
- Type
ActiveSheet.Protect UserInterfaceOnly:=True
- Press Enter.
The sheet is now protected for the end user, but VBA code can freely manipulate it.This works well however when I go to another page and do the same it stops working on the original sheet.I would like to know if there is an easy way to:
protect/unprotect all sheets in one go and have the macros still work?
When I protect pages individually I want the following to be allowed:
Select unlocked/locked cells
Format
Sort
Use autofilter
use pivottable
I will first save which cells are un/locked & hidden.
I'd like my button-triggered macro to exit and quit IF the sheet it is on is protected when someone presses the button. Here's the idea:
IF the active sheet protected THEN
Display "Can't do this on protected sheet" (and exit macro after click OK)
Else
run the rest of the macro.
I have a sheet for data entry. On that sheet a button to run macro to scale the graphs on sheet 2. If I protect sheet 2 though because I am thinking of letting others use this program. It gives me an error 400.
View 9 Replies View RelatedI have incorporated print/print-preview command buttons/VBA into a workbook with protected sheets. To enable these command buttons to function when the relevant sheet is protected, I have had to add VBA code to unprotect the sheet before generating the print preview, and then to protect it again afterwards. However, the code I have used (see below) prompts the user to enter the protection password, is there any code I can use where I can write the password into the code itself to unprotect the worksheet without the using having to enter the password?
'Unprotect Sheet
ActiveSheet.Unprotect
'Print preview & cell formatting code
'Protect Sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True
I'm not fond with macros, but this is what i need help with.
I need two macros one to sort ascending (Ctrl+A), and one to sort descending (Ctrl+D).
This is what the macro to do. Unprotect, sort selected column, protect.
I am creating a series of macros for a workbook, and at the beginning of each macro I unprotect all sheets and at the end of each macro I protect the sheets once more. The issue I am having is that even with these macros in place, I continue to receive an Excel alert message saying:
"The cell or chart that you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password."
The macro will then run. Everything works as it should, but I do not want this error message to pop up. I have tried inserting
Application.DisplayAlerts = False
into the code, but it does not stop the message.
I cannot run a macro because I am encountering error 1004, intersect of object. i am using a protected workbook, and I dont have the password to unprotect it, but it needs to be fill out using a macro from a different workbook. Is there a way around this?
View 1 Replies View RelatedI have written a macro to select a range of cells one by one and open the excel files mentioned in them and execute a task. It works fine. But I want the system to see if the file exists in its folder or if the file is password protected (for opening). Either of the case the file should be skipped without a warning message and any attempt to open. I need a macro for that. Following is the way the cell contents are
I:ORDERSS-S 10ALIYAMARKET ORDERSADDADD-LADIES-LEGGINGS-13246.xls
I:ORDERSS-S 10ALIYAMARKET ORDERSADDADD-LADIES-PANTS-13247.xls
I:ORDERSS-S 10ALIYAMARKET ORDERSADDADD-LADIES-SHORTS-13244.xls
I:ORDERSS-S 10MICHELLEGAMESWORLDWIDE COMPANYWORLDWIDE COMPANY - GAMES - 10014.xls
And following is the VBA code I wrote. It it almost stops when reaching a non-existing file name
I tried to use the code that allows macro to run even when the sheet is protected.
View 3 Replies View RelatedI have a file used by numerous facilties. I would like code to track any time a protected sheet has been unprotected / protected. I would also like the stamp to show the user data if that can be done.
View 2 Replies View RelatedWhile my excel file is protected, I want to have a functionality that allows user to modify on certain areas (of the file), such as below:
1. After the user select number of years at the drop-down list (Main sheet), rows and columns at other related sheets will shift and hide certain values which corresponded from the drop-down list (e.g. if user selects 3 years, then at other sheets, rows from year 1 till 3 will be displayed, and rows for year 4 and year 5 will be hidden).
2. Users can add rows for entering new data at a new line and delete rows for deleting data at the correspond line.
This PC is using the Excel version 2000. At Tools > Protection > Protect Sheet, the available options are 'Content's, 'Objects' and 'Scenarios'. In order to allow users modifying rows/columns (while the file is protected), options such as 'Format Rows' and 'Format Columns' must be checked. But these options are only available in the later versions I believed.
After I have done macro recording for the protection properties at Excel version 2003 (my sister's PC), this is what I got for the .....
I have a macro in a workbook (good start) as follows:
Sub UnhideSheets()
' Set to Auto Calc mode
With Application
.Calculation = xlAutomatic
End With
' Asks for a password (must be "unhider")
Dim mypass, response As String
mypass = Application.InputBox("This macro requires you to enter a password to proceed." & vbCrLf & "Please enter password below.")
' If password is correct
If LCase(mypass) = "unhider" Then
' Unhides all sheets
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Visible = True
Next i
' Then tells you that all is OK
response = MsgBox("All sheets unhidden", 0, "Macro run")
' Otherwise
Else
' Tells you that there is a problem (Unauthorised to continue)
response = MsgBox("Sorry, you are not authorised to use this macro.", 16, "Macro stopped")
' hides the sheet again
Sheets("BSG Only!").Visible = xlHidden
Sheets("CC07").Activate
' And ends the "if"
End If
End Sub
This unhides the "very hidden" sheets that make the book work and is password protected because I want to limit the people who can see this data.
What I would like to do, however, is call this macro remotely from another procedure in a separate book, which can only be accessed by those who are authorised to see the hidden data.
Essentially, then, my question is: "How do I call the protected procedure without the user having to enter the password each time, as they must be authorised to have access to the procedure that calls the protected procedure?"
I am getting an error when I protect a worksheet and save it. Upon re-opening the worksheet, I try to use the cells that are being formatted by a macro, but I get the following error...
Run-time error '1004':
Unable to set the NumberFormat property of the Range Class.
If I open the worksheet and it is already protected from when I last closed it, then I get the error. If I unprotect the sheet, then close it and reopen it, then I don't get the error. Does anyone know what I might be missing or doing wrong.
Cycle through all sheets in a workbook performing the following:Store worksheet protection state (bSheetProtection)... execute code ...Restore worksheet protectionI cannot seem to locate a way to save a worksheet's protection state in a variable.
View 5 Replies View RelatedI have used a small macro to format the sheet to hide cells with no entries and then print. The problem is that when the sheet is protected, then macro wont work. The sheet needs to be protected by the end user. I am only really learning excel, I could add to the following so the sheet can remain protected?
Intersect(ActiveSheet.UsedRange, Range("O:O")).Offset(0, 1).Select
Range("O39:O155").Select
Selection.EntireRow.Hidden = True
Columns("O:O").Select
Range("O21").Activate
Selection.EntireColumn.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Range("A21").Activate
Selection.EntireRow.Hidden = False
Columns("N:P").Select
Range("N21").Activate
Selection.EntireColumn.Hidden = False
End Sub
I have a protected worksheet, which I have 2 macros, 1 to spell check and 1 to insert rows, they both unprotect the sheet and re protect it again once they have completed. The problem I am having is that when I protect the work sheet first time round I tick the box to allow users to insert rows, once the Macros run they disable this functionality. Is there anyway I can include this in my Macros or do I need to add a new button!
View 9 Replies View RelatedI have the following macro so that we can use the outline feature without having to unprotect a sheet.
Private Sub Workbook_Open()
With Worksheets("Schedule")
.Protect Password:="APQP", Userinterfaceonly:=True[code]....
However once the macro is run, filtering will not work without unprotecting the sheet first. If you choose not to run the macro, filtering will work without unprotecting the sheet however the outline feature will not. what I need to add and where to make both the outline and filter features to work without having to unptotect the sheet?
In my office, our colleague locked his macros before he left. Now, I would like to continue in that but I have no idea, how to get into macro - to study it and change it as needed now.
How to open it?
locked one.xlsm
I have a macro with "ActiveWorkbook.Protect Password:="password"" . It was working fine in excel 2003. In excel 2007, because of this I am not able to run any macro's in the workbook. I am getting a message "The macro may not be available in this workbook or all macros may be disabled. I can see a security warning in message bar saying macros have been disabled, but I dont have have a option to enable the macros.
View 9 Replies View RelatedI've got a work book with numourous password protected sheets which i quite often have to update (as i use around 10 of these with at least 10 sheets in each its time consuming). I've had several goes at creating 2 macros to unlock and then lock the sheets (with my password). I've managed to create on that unlocks the sheets using the password but i can't get a macro to lock the sheets up again using the password.
View 9 Replies View Relatedhow to unprotect the sheets in order to be able copying datas to to certain cells.
The unprotection works but only when I start the macro the second time. I do not understand why.
After I did the updates I want to reprotect the sheets but that seems not to work in my case.
ActiveSheet.Unprotect Password:="my_password_here"
'unprotect the sheet
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, Password:="my_password_here"
' Reprotect the Sheet
I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.
What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?
Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.
What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.
What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.
Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?
PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.
I am having an issue with a macro to open password protected workbooks. The macro works just fine in opening the files and whatnot, but for some reason it prompts me to enter the password again once the file has been opened. Funny thing is I can either hit OK or hit Cancel and it goes to the next file. All files open correctly, I was just curious as to why this is happening and how to correct it?
View 1 Replies View RelatedI 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.......................
I have a macro code for conditional formatting. The first 2 lines of the macro are
Private Sub Worksheet_Change(ByVal Target As Range) ....