Sep 08, 2015 I am supposed to be able to set up permissions for this purpose by using the 'Allow users to edit ranges' feature. I can create the range name, I can assign users, I can establish passwords for ranges and it all looks good. I am saving the file to a fileshare so that any of the 'groups' can access the file at anytime for updates. Hi How to prevent users of a spreadsheet editing the content of the cells? Is there anything we can do in VBA? Thx a lot yuelin there's two parts to protecting the contents of cells. You need to set the locked property of the cells you want to protect to true (its true by default) or false if the cell can be edited. You need to protect the.
I need to protect my worksheet but allow certain cells to be modified. I found the option under Tools > Protection called 'Allow Users to Edit Ranges.' However, when I use this feature and select lets say C3:I14 and add that as an 'unprotected' area the users can change the text but not the font color, background color etc. Is there any way I can get excel to allow Font/Background changes ONLY in those cells I unlocked? Subscribe for Weekly Excel Tips and TricksHelpful tutorials delivered to your email!Similar Topics
I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color. The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks! Code:
Hello, I'm pretty new at VBA and was wondering if you could help me out on this: I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? if yes, would you be kind enough to share the code? MS Access is not an option for me so I was wondering if you could help me do this in excel. thank you so much and would really appreciate to hear from anyone soon.
How do I get the colors to change automatically when I use a drop down list. Per say here is what I am trying to do If a certain 'word' is used from the drop down list I want the background of that cell to change its color to 'green.' And if later I change the 'word' to another from the drop down list, it will change it's color to a specified color. The drop down list that I use was created from cells that have the colors already in the 'word', but I do not know how to make the list show the colors so it puts the word & color automatically in the drop down list to the cell with the drop down (if that makes any sense). If there is a way, please spell it out simple enough for me to understand, as I don't know fully the capabilities of formulas or vba. Hi I am using Excel 2007 and suddenly I can no longer filter by color. The option is greyed out and so is the sort by Color option. Does anyone have any idea why this would happen? Thanks in advance.
Is it possible to change the color of text using a formula, such as an IF statement? What I have in mind is something like this (stated simply): If cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4 red.
I have a large database with names, addresses etc. When I try to make changes to the email address, like change the font color or change on letter, Excel tries to email the address. If I click in the formula bar to make the changes, it automatically changes it back to all blue text and underlined. I have tried to format all of the cells as 'Text' to show as entered, but it doesnt work. PLEASE HELP Is there a way I can use conditional formatting or something to change the color of the cell once I enter a value or text into that cell? For example if I currently have the A2 cell color as a gray color, and I enter information into A2 can I have it set to automatically change to no fill or any other color without having to click out of the cell, back into it, and then clicking the button?? Does that make sense??? Can someone help me? Hi! I am new to Excel and I am creating fillable forms in excel which are protected so that users canonly input data in certain cells. I am using Excel 2003. The users told me that they do not want text to go beyond the edge of the page when they are inputting data. They also want the words to wrap when they reach the end of the sentence. I am also using check boxes(yes/no) and Ifind it difficult to make the adjacent cells fillable only Thanks for your help and correct me if I am wrong as I am new to this board.
I have an excel 2007 file sitting in a shared network folder. I only want one user to be able to make changes at a time (any other users would get a read-only). For some reason it currently does not do this, and I have multiple users with the same doc open. I'm concerned that changes will get over-written when 2 people are saving their changes. Can anyone help me with the settings for this.
Corporate edict. I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column 'MS96A'. If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*). What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, 'The cell or chart that you are trying to change is protected..' I think I am close, but I am getting an 'End If without block If' error on the If Clause. Sub Worksheet_Change(ByVal Target As Excel.Range) Dim MRange As Range Set MRange = Range('MS96A') ' If Not Intersect(Target, MRange) Is Nothing ThenFor Each cell In MRangeSheets('Sheet1').Unprotect Password:='temp' cell.Interior.ColorIndex = 3 cell.Font.Color = vbBlack Selection.Locked = True Selection.FormulaHidden = False Next cell ActiveSheet.Protect Password:='temp', _ DrawingObjects:=False, _ Contents:=True, _ Scenarios:=False ActiveSheet.EnableSelection = xlUnlockedCells End Sub
Hi all. I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy. I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them. I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine. Here's the code that does it. Code: By repeating this code for each worksheet, I can make each one be zoomed just right. However, the file contains 8 sheets that are all identically laid out, except the number of rows is different. What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout. I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance. I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes. Anyone know how to do this ?
Hi, I have the following code to put data from a VBA userform into Excel Dim Sh As Worksheet Dim Rng As Range Set Sh = ActiveSheet Set Rng = Sh.Range('A65536').End(xlUp).Offset(1, 0) With Rng .Cells(1, 1) = Surname.Text .Cells(1, 2) = forename.Text .Cells(1, 3) = datein.Text .Cells(1, 4) = origin.Text .Cells(1, 5) = Addressee.Value .Cells(1, 6) = usual.Value .Cells(1, 7) = dateto.Text .Cells(1, 8) = permission.Value .Cells(1, 9) = dateseen.Text .Cells(1, 10) = requestview.Value .Cells(1, 11) = Invoice.Value .Cells(1, 12) = notes.Text .Cells(1, 13) = datecompleted.Text .Cells(1, 14) = holdsend.Value .Cells(1, 15) = fee.Text .Cells(1, 16) = notes2.Text .Cells(1, 17) = dateseen.Text .Cells(1, 18) = invoicesent.Text .Cells(1, 19) = Paid.Text .Cells(1, 20) = Complete.Value End With What I want to do next is click on the surname on the speadsheet and call up the userform with the fields complete for that person. I would also like to be able to edit/update the form and update the information on the speadsheet accordingly. Any help would be greatly appreciated! Many thanks! I have cells in a column, some colored yellow, some not. I am trying to use the SUMIF function to sum only the cells in the range that are colored. Can this be done? Can I enter something in the 'criteria' part of the formula that can do this? =SUMIF(D3:D13,'criteria',D3:D13) I also tried to use the CELL function's color feature, but I couldn't get it to work right. I don't know how to get Excel to recognize if a cell is colored in a formula. =CELL('color',cell) It might just be that I don't know what this means in Help: 'color' --> 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero). Can anyone help? I am creating a data sheet to be completed by other users. I would like to format the text cells (name, etc) to have text entered as uppercase automatically although the user might use title or lower case. UPPER function cannot make cell look at itself and perform the function Excel 2003
I have an excel worksheet that adds two other worksheets in a data triangle. I copied it to create a new data set and used find & replace to change the worksheet references to the new ones. The cells still contain the result of the old formula referring to the previous worksheets. The only way I can get the formula to return the correct result is to edit (F2) each cell and press enter. Calc now (F9) does nothing. I've seen this before, but this time, I need to calculate many thousands of cells and don't have time for this workaround. Any ideas? Thanks. Don S
I am trying to sum all numbers <0 using the SUMIF formula in Excel 2003. FORMULA: =sumif(range,criteria,sum_range) I got it to work, but I have cell ranges to total that are not next to each other (such as A5:A15 and C5:C15). When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15 because of the comma (,) between the cell ranges Excel is reading the A5:A15 as the range and C5:C15 as the critera. How can I get this formula to allow me to select multiple cell ranges? All help is appreciated. Thank you.
We have a number of Excel users in our office who cannot copy and paste between Excel workbooks. They can copy and paste between worksheets. When you highlight the section to copy and then go to the new workbook both the paste and paste special are 'grayed out'. This is true whether you right-click the mouse, go to the edit menu, or use control keys. This occurs with any data type and the most simple workbooks. I have seen some suggestions here but none have worked for this particular problem. I have reset the menus and renamed the .xlb files and neither helps. You can open the clipboard and the paste will work, but there is no paste special option. Any help would be greatly appreciated. Thanks! I'm trying to freeze the widths of columns so that when users of the worksheet are using it, they can't change the width. It's for editors who write headlines for specific areas that can only be as long as the column is wide or the text won't fit.
Afternoon all, This is what I want to happen but I have no idea how to do it. If C14 = 'IV' THEN E14 = Locked AND F14 = Unlocked If C14 = 'RV' THEN E14 = Unlocked AND F14 = Locked If C14 = 'AJ' THEN E14 AND F14 = Unlocked Another thing I would like to make possible is for this to work throughout cells C14:C450, E14:E450 and F14:F450 Can anyone help me out on this?
Hello I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me. I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc. When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result. I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before. Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious. There's about 50+ cells that need referencing and I got to get this done for work. Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time. I'm going over a worksheet (prepared by someone else) where I can't see the text that has been typed in the worksheet within the cells. I can see it in the editing bar, but not in the actual cell. It doesn't appear to have anything to do with the color of the text.. suggestions?
Hello, I want to protect a cell that I have a drop down list in, but I still want the drop down list to work. When I try to protect it, the user is not able to use the drop down list to select other items. I didn't see an option on the protect sheet area. Thanks for any help. Nick
How to create a userform that will have button for search, edit, change, delete individual recordings. Also in case of listed results obtained records that could be printed? Thanks I cannot edit an excel spreadsheet that I had made. When I open up the file I can view everything but change nothing. The file is not saved as read-only. What can I do?
Hello, what I would like to do is take a portion of a sheet... So the cells I want to mirror are in the proposal sheet cells B32 x F51. And then mirror the exact duplicate copy to another sheet. The other sheet should have those same values at A20 X G39. What I am trying to do is have a sheet that has skus, prices, and so forth and then when I add,delete, merge, color, or do anything to the cell on the 'proposal creation' sheet I want it to mirror only a specific cells to another sheet that is 'salesman copy' that we will print to clients. So this way ont he proposal copy a salesman can insert rows or even change the color of the cells and then have it mirror exactly the same on the 'salesman copy' which is what we will print for clients. Thank you. |
Melodyne editor 2 keygen for mac. In this article, we will learn how to share and allow users to edit changes in Microsoft Excel 2010.
Microsoft Excel allows multiple users to modify the data in a worksheet simultaneously. You can create a shared workbook & place it on a shared location where many people can make the changes of the contents of the file at the same time. If an employee is working on multiple projects, and needs to update the status of the task as it finishes so that the information will be passed to the group members who are working along with. It will minimize any chance of two people doing the same task. All persons involved can enter the information for their projects in the same workbook.
The owner of a shared workbook can manage it by removing users from the shared workbook and resolving any conflicting changes. After doing all the changes, you can stop sharing the workbook.
Share a Workbook
1) Create a new workbook or open an existing workbook that you want to share to multiple users to modify any changes.
There are some limitations in a shared workbook & all the features are supported.
The following features are not working in a shared workbook:
- Conditional Formatting
- Data Validation
- Create or change Charts
- Pictures
- Merged cells
- Hyperlinks
- Objects including drawing objects
- Scenarios
- Outlines
- Pivot Table Report
- Subtotals
- Data Tables
- Workbook and Worksheet Protection
- Macros.
You cannot make changes to these features after sharing the workbook.
If you want to include any of the above listed features, then you should add them before you save the workbook as a shared workbook.
Steps to Share Workbook
- Click on Review ribbon
- In the Changes group, click on Share Workbook
If you see this information then
- You need to Click on File ribbon
- Select Trust Center
- Click on Trust Center Settings
- Uncheck the “Remove personal information from file properties on save”.
- Click on OK
- You will be able to share workbook
- Check the box “Allow changes by more than one user at a time. This also allows workbook merging”.
- Click on Advanced tab & select the options that you want to use for tracking & updating changes.
- Click on OK
- You will see the dialog box appearing & asking: Do you want to continue sharing the workbook?
- Click on OK.
- After that, you will notice that the file has been shared
Recent Articles