Excel 2007 :: Protect Template But Not The Children?
Jul 24, 2014
Using Excel 2007. When I put password protection on the template, the template child doc also gets the same password protection. I want to prevent tampering on the template, but I want the child doc to be fully editable for the end user. After all, isn't that the purpose of a template? I just don't want anyone to be able to edit the template itself, which also seems like the purpose of a template. Am I missing something about not seeing how to configure this obvious functionality?
(Excel 2007), I have a template that I need duplicated for every reference. My Worksheet lists all the references and the macro use to duplicate a worksheet for every reference in the worksheet. The problem is when duplicating now, it duplicates the name of the template. For example, when the macro is ran sheet1= Template(1), sheet2= Template(2) ect.
Here's the code- I think it has something to do with the named ranges
Code: Sub Macro1() For i = 1 To Application.WorksheetFunction.CountA(Worksheets("Worksheet").Range("A:A")) Sheets("Template").Select Sheets("Template").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = Worksheets("Worksheet").Cells(i, 1).Text Next i End Sub
I know how to protect a worksheet with a password so that no one can amend the contents or view the formulas in the cells. And then we can un-protect the worksheet by clicking on the "review" tab in excel 2007 and so on. But recently I came across an excel sheet which was password protected and my query is that the "Review" tab was greyed out. What kind of protection was this that clicking on "review" tab option is also unavailable. And how does these kind of sheets get unlocked then.
I'm using Excel 2007. I know how to protect specific cells within a worksheet, but I have not been able to find any information online pertaining to locking or protecting inserted header/footer data, even if I protect the worksheet.
I'm not concerned about setting specific VBA commands to ensure the header/footer prints, but rather preventing other users from being able to edit the data upon distribution of the worksheet.
I have a lot of formulas that I don't want to be changed or deleted, but I still want the user to be able to enter data into the cells.
If I use "Protect sheet" I can't select a cell or enter something into it. I sure there is some setting I have to change, but I'm not sure what to select.
I have an intern that is working with me, and I have a file that I need updated but one of the columns has confidential information so I would like to just hide & password protect this column this way he can't access/view this information but still have access to modify the rest of the spreadsheet.
I have a workbook that has a sheet called CustInvData, this sheet contains 4,421 rows of invoice transaction data for 178 customers starting on row 2 (headers on row 1). I need to split the transaction data for each customer out into a workbook template based on the customer name in column A. I need each workbook named by the customer name along with a month and year (example: Bellsouth-0911.xls), this should create 178 unique workbooks. And since we sometimes have to go back and rerun invoices for previous months, I'll need to control the month and year manually in the code.
The parsed data needs to be copied to a pre-formatted invoice template. This template has 2 sheets, Sheet1 is called 'Product Summary', this is a table that uses VLOOKUP functions to read the data in Sheet2 called 'Product Details', this is the sheet the parsed data needs to be copied to for each customer invoice. The 'Product Details' sheet has formatted rows 1 thru 11, row 11 being the header row for the data from CustInvData to be copied. So the parsed data needs to start at row 12.
Last, once the data has been copied into the 'Product Details' sheet, I need the data to be SubTotaled at each change in column J (Product) and use the 'Sum' function to add a Subtotal in column L (Retail Price) for each unique product category.
Example data below, I've simplified it (the actual data array spans from columns A to Y)
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
I have an Excel 2007 workbook which has five sheets in. I just wondered whether it is possible to hide all five of them when the workbook is first opened. Also I would like the user to be asked for a password when they wish to unhide a sheet, with the password being different for each of the sheets.
Is there also a way to do this which won't be affected by Excel disabling all Macros when the application initially opens.
I Have a number(30+) of excel files based off of one template. I have since updated the template, thus making the the old ones obsolete. Is there a way to update the older files to use the new template without having to copy and paste the addition's row by row column by column?
None of the additions to the template will change the placement of existing data, nor will it change the data itself. It will however add Values to cells that were previously empty.
In Pre Office 2007 versions I could drop an .xlt file (or shortcut to one) on my desktop, when double-clicked it would open a new workbook using the appropriate .xlt template. In 2007 versions double-clicking on the .xltm opens the actual template. Does anyone know a workaround for this, or more likely what setting in Excel I have wrong?
attempting to utilize the powers of Excel to generate a math worksheet for my children. I have no experience with macros and I have only used the basic functions and formulae.
Basically I need assistance in designing a worksheet where I can input the number of problems I want displayed, the type of problems (add or minus), and the number ranges for the values. With a subtraction problem, I want the formula to make certain the top number is not lower than the bottom number. Also, I would prefer that the worksheet maker does not generate duplicate problems. Once generated, I have the option to print the results for the kids to take with them or sit on the computer and enter the values - whereby Excel will instantly turn color the answer cell green for a correct answer, and red for an incorrect answer.
I'm currently working on a form to fill out in a study on different analyzers; the master form has gone through several revisions. To keep things looking neat, all the "children" forms have had to be altered, too. The amount of data is getting large and having to do this is getting annoying.
Is there a way to create a master form that is modifyable where the changes are spread through the children? i.e. if I insert a row with new information, I'd like the change to occur on multiple sheets. If I move a row to a different spot, similarly, I'd want all my forms with data to move around to follow suit.
My wife is a teacher and she has asked me if there was a way of randomly selecting a child's name from a pre-defind list and for that child not to be selected again during that session.
Some criteria:
Up to 35 children in the class (selected from a pre-defind list)A child can only by picked on once during the lessonA method needs to be in place for seelcting the child's name (either a button that can be pressed, or another option).
how to create a copy of my employee attendance template. Is there a way to create a copy of the template by entering an employee name in the "name" cell of the template and it automatically renames the sheet that employee name and also saves or recopies the template for use with the next employee?
I have a large flat hierarchy table. I am looking for a method to select a parent and return a list of my lowest level children. I am somewhat familiar with VBA but I don't know where to begin coding this.
This has been a great learning source over the years, but this is one of the few instances I cannot find my problem so I am now posting. Example below:
Child Parent
4 5
3 4
2 3
1 5
40 50
30 40
20 30
10 50
22 100
If I select 5, I want values 2 & 1 returned. If I select 50, I want values 20 & 10 returned. If I select 100, the value 100 should return. If I select 40, the value 20 should return.
Quick rundown, I have a sales background, so other parts (accounting, inventory, etc) are what I want to improve so Im not running around when an order comes in, where is it, do I have it in stock.
I know microsoft has some templates, any worth downloading and starting with, then move the data or add functions to an inventory and accounting template to start, any other templates recommended for an online business.
I have a listbox with a state and below it several cities. For example:
New York New York - New York City New York - Nassau County New York - Westchester New Jersey New Jersey - Newark New Jersey - Monroe County New Jersey - Passaic
I would like to select the state and have it auto select all of the related cities including the state. So if I select New York,
It would look like this, where all are highlighted (as if selected). I coudln't find the highlighter on the thread tool bar:
New York New York - New York City New York - Nassau County New York - Westchester
I have an excel sheet on which to address, cc address, subject , attachement location and file name is mapped. Can some one help me out but executing the macro it will pick the file from the location and send to the Recipient with cc and subject and attach the file accordingly. The excel sheet is attached for your reference.
I want to make a template in excel. Lets assume I have Opening Balance in A1 and closing balance in B1. How do I make template make new sheet and every time I make new sheets it should take the closing balance of the previous sheet as the opening balance and so on and so forth. But the very first sheet needs to have the opening balance as unlocked cell but the rest of them needs to be locked.
I am using Excel 2010. The template only has 5 working days in it, but I need to set 6 working days (Saturday is also working day). Wondering, how to get this working without spoiling the functions. Also the date format is in US , need to change to UK without disrupting the Formula/Functions.
I work for a company that has about 650 locations. Each location has a location manager that is responsible for submitting a Performance Review spreadsheet for all the employees at their location.
Currently I have an Excel file that has all employees/locations. One of the columns on my spreadsheet is "Location ID"...which is literately a number we've assigned to our Locations. I have an Excel template saved that I'd like each Location to open into.
Can someone help me? I need each location to be it's own spreadesheet...which I have a template created already. I'd prefer to find an automated way to do this...rather than manually creating 650 spreadsheets.
I'd prefer to do this in anoter application, such as MS Access, but senior management already made the decision this will be done in Excel...so I'm stuck with what I got.
Month Pay Tax Socia sec.tax Jan 10000 2000 2999 Feb 15000 3499 3333 March 3455 222 333
I have an excel document with sheets representing employees and within the sheets it shows wages taxes nd social security tax for each month.
I want to create word documents for each month which inserts all the values for that particular month, aswell as the name of the employee into th word document. How to do this?
Is it possible to make excel template work only on 1 computer by giving any vba password (on lines of license number).
Aim is to ensure it works on only client computer, even if template is shared outside it becomes useless.(We often see clients audit team getting access to templates using it for thier purpose & distributing).
In same spirit what are best practices one can take like protecting formulas with password etc
I've set myself a project to try and automate some repetitive filling in of word documents and would like a point in the right direction. I've done some research on the MSDN and some sites on the web. The way I was thinking of doing it would be,
make a template with either Fields or Bookmarks (which would be best?) for each piece of data on the paper work.Put the repeated data into an excel sheet (with a button for the macro to be assigned to).
write some VB script that will, define and "label" the data in the XL sheetopen the word templateenter the data into each Field/BookmarkSave as a new file in a "New" folder (name of file and folder taken from the Data)open the next template and repeat.
would this be the best way of approaching this problem?Whats the best way of defining points in a word template, Fields, Bookmarks?what objects would I need to use to rename the folder?
I have a .dot word template that has the little 'grey' boxes that is awaiting information to be filled in, this information is already stored on a master excel sheet and the doc is simply for 'archiving' and users benefit, from my point of view its pointless.
and I have an excel file which is A1 : Username B1 : Password C1 : Email how easy / hard is it to create a button macro to automatically go to the .dot file location, open it and then put the create data in.
