Restrict Editing To Certain Columns/Rows And Allow Some To Be Edited By Users
Oct 29, 2007how do I restrict editing to certain columns/Rows and allow some to be edited by users?
View 2 Replies
ADVERTISEMENT
Assigning Editing Permissions For Different Users Across Different Columns?
Aug 5, 2014I am putting together a corporate document which requires many different peoples access. It also requires these people to update/comment within specific columns to them.
I am really struggling with editing the columns to ensure only these specific people can access them.
Essentially I want to the document to be accessed by everyone, but column A can only be edited by Person A, column B can only be edited by Person B etc.
I've used 'Allow Users to Edit Ranges' and added a new range with a specified person - but this has not cured the problem, other people can still edit the column I'm trying to prevent them to.
Is it a case of just 'allowing' the specific person, or do I also need to 'deny' absolutely every other person to ensure that it works?
Editing In One Tab And Placing Edited Data In Queue In Another Tab?
Jun 24, 2014I have a simple Excel file composed of three tabs:
-EDIT
-QUEUE
-RESOLVED
On each tab, line 1 has the same headers: Status, Name, Surname, Issue, Details, Last Update
In the EDIT tab, line 2 is dedicated to editing the relevant information under the headers, and once done is validated with a "Done Editing" button: This button would cut line 2, and place it in line 2 of the QUEUE tab, moving down all the others in the list
The QUEUE tab lists all the issues keyed into line 2 of the EDIT tab. An "Edit Selected" button would cut the selected line (or the line in which a cell is selected), and copy it to line 2 of the EDIT tab for further editing, after which it would be placed on top of the queue once again as above.
The RESOLVED tab lists all of the issues marked "Resolved" on the Status column.
What I can't figure out are the macros for the two buttons:
- "Done editing" in EDIT tab adds today's date in the "Last Update" column, then cuts line 2, and pastes it in line 2 of the QUEUE tab pushing down all the lines in the list, and keeping any existing conditional formatting if possible (colours in the "Last Update" column, for ex.). If the status is set to "Resolved" before clicking the button, it does the same, but pastes it to line 2 of the RESOLVED tab instead of QUEUE.
- "Edit selected" in the QUEUE tab just cuts the selected line from the QUEUE tab and pastes it in line 2 of the EDIT tab.
Restrict Users To Only Editting Certain Cells?
Apr 6, 2009I need a way to protect my worksheet from any changes but allowing the users to edit certain cells (eg. allowing users to enter data only into columns A-G).
View 2 Replies View RelatedHow To Restrict View For Users Of Spreadsheet
Feb 9, 2014I've written a spreadsheet for others to use, protected it (except for cells where data input is required). Almost like an App for sales people to prepare quotes. I want it to appear fullscreen only with formula bars and headings permanently hidden so feels the experience for the User is similar to an App.
View 9 Replies View RelatedHow To Restrict Users From Logging More Than Once In Excel
Nov 7, 2011I am doing an excel survey using forms. I want to restrict users from completing the survey more than once. i am using below code to input user login details in to a spreadsheet but i am not sure how to restrict them. I want a msgbox or some sort and then it closes the excel active workbook.
code to get username:
Sub GetName
Dim r As Range
Set r = Sheets("UserNames").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
r.Value = Environ("username")
r.Offset(0, 1).Value = Date
r.Offset(0, 2).Value = Time
End Sub
After looking through various forums i found code to highlight duplicates but not sure how this can be modified to suit my need.
Code to find duplicates:
Sub Dups()
Application.ScreenUpdating = False
Sheets("UserNames").Range("A1:A500").Select
Rng = Selection.Rows.Count
[Code] .......
Restrict The Adding The Rows And Columns And Also Deletion
Jan 29, 2007I want to restrict the adding the rows and columns and also deletion of rows and columns in excel template, the same template is circulated to all the program owners to capture there project metrics. With that template through macro values are extracting to consolidated report. Some times program owners are adding extra columns/rows with that we are getting errors/incorrect reports.
View 14 Replies View RelatedHow To Intimate Other Users About Editing Record In Shared Excel Workbook
Jun 2, 2014i have an excel database regarding about 1000 different schemes having the details of their initiation date, budget allocated , up to date expenditure, this month expenditure and many others too. this is a shared workbook and relevant officers enter their data in their relevant columns and rows. the file is shared. now i want that any changes done by mr. A, for eg. at record No. 09 could be intimated to Mr. B who is working at record No. 560. may be via msg box or any other source.
View 1 Replies View RelatedHow To Allow Users To Add / Delete Rows / Columns In Sheets With Macros
Mar 11, 2014I have a sheet with macros that allows users to fill in data from columns F10 to O10 from row 10 to 54.
Column P will then make some computations based on the inputs from col F to O.
Row 55 will also make some computation based on inputs from row 10 to 54.
How to I modify the code below to allow users to either add/delete columns and rows such that it does not fowl up the computations?
The computation does not have to be at column P or at row 55, they can be dynamic and change depending on how many rows and columns the users delete/add.
A little background on what the code does. From Column F to O, the code UNLOCKS the columns only if user inputs date in Cell 3 of that particular column.
Example. Column F is locked from F10 to infinity at the start. Only when user inputs date into F3 does the cells F10:F54 is unlocked for user to input data.
This goes on until column O. I want users to be able to add more columns, or delete ones they don't need.
Allow Data To Be Edited But Not Format To Be Edited
May 5, 2006I have a cell that I am needing to allow users to edit the data but not the format.
The users edit the data by copying & pasting from their own personal spreadsheet. The source spreadsheet formatting can be varied. Is there a way to allow them to be able to edit the data inserted but not the format?
Greyed-out Columns Based On Cell From Specific Column In Active Row Being Edited
Jul 7, 2014I am making a spreadsheet in which I have been asked to have certain column greyed-out based on an answer that was given in a dropdown menu. Specifically, a dropdown menu in column D asks if the client was a Youth or an Adult. Depending on what the anwer is, other columns in the spreadsheet will be greyed-out; different ones for each answer. My problem is, I need this to occur for the active row being editted. For example: The first row that the user can input data into is row 4. If D4 is answered with "Adult" then certain other columns are greyed-out. However, if the next time the user updates the spreadsheet with information for a different client (now entering information in row 5) and they select "Youth" for column D5, then different columns get greyed-out. The columns that are greyed-out will depend upon the answer for the dropdown menu in column D for whatever row is actively being editted.
View 5 Replies View RelatedRestrict Change Event To Specified Rows
Jul 28, 2006I have a question on how to define fixed rows on making cell blank on new selection change.
The code below affects all rows under coloumn 1.
Is there any way to affect only eg row 1 to 20?
I attached a sample file for reference.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strName As String
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
strName = Target.Name.Name
On Error Goto 0
If ActiveCell.Column = 1 Then
Application.EnableEvents = False
ActiveCell.Offset(, 1).Value = vbNullString
Application.EnableEvents = True
End If
End Sub
Fill Columns Botom Up Fashion (editing Current Code)
Sep 8, 2008I have a piece of code that prompts me to choose which column to be filled bottom up, I would like to modify this and add it in as a macro so that it fills columns H and columns I in a bottom-up fashion
View 13 Replies View RelatedSearching And Editing - Add Information To Specific Rows Of Data In Worksheet
Jun 11, 2014I am working on a project that I feel should be relatively simple but I seem to be stuck. My Goal: To add information to specific rows of data in the worksheet. I want to use a user form, and eliminate as much typing for the user as possible. I have already designed my user form, and written the code to identify the row in which I want to edit. Nonetheless, I do not know how to code the insertion of the new data. The data I want to add will be in empty cells at the end(right) of the data table.
I have attached a portion of my data set.InsertQuote.jpg
Here is where I am. The words in red are just colloquial words that I cannot seem to put into code.
Private Sub CmdInsert_Click()
Dim Company As String
Dim PartNumber As Variant
Dim Condition As String
[Code] .....
Prevent The Users From Inserting Columns Within A Certain Range?
Jan 7, 2010I am instantiating Excel 2003 from vb.net and populating the spreadsheet from SQL. The workbook itself will be maintained even when the .Net program is inactive (so that the column headers are established and saved.) Is it possible to prevent the users from inserting columns within a certain range?
View 2 Replies View RelatedAllow My Users To Insert Rows
Dec 31, 2009I need to allow my users to insert rows, however I have Data valadation lists that would be affected by the insert.
Is there a way that I can lock the spreadsheet so that they have to highlight the cells and then insert the row but NOT allow them to highlight the entire row then insert?
Hide Rows So Users Cannot Tell
Aug 20, 2006Is there a way to hide rows in VBA a la "VeryHiddenSheet" so that the row disappears without the blue line, etc. showing as you would when done manually in Excel?
View 8 Replies View RelatedMacro To Unhide Columns Based On A Users Input
Apr 2, 2014I'd like for my spreadsheet to open with only Column A visible; I'd like all other collumns to stay hidden.
I want to create a marco to unhide column(s) based on the value of an individual cell.
For example:
if I enter the number 5 into cell A1, I would like Columns B:F to unhide.
if I enter the number 2 into cell A1, I would like Columns B:C to unhide. Is this possible?
Allow Users To Insert / Delete Rows On A Protected Worksheet?
Apr 5, 2014I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???
I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.
Protect Sheet Allowing Users To Format Rows/Edit Objects?
Nov 16, 2009I want my macro to protect my sheet again after it is done to allow editing objects and formatting rows. I am really stuck and have come up with the below, but it isn't working.
View 5 Replies View RelatedEditing Macro To Include Multiple Rows And Include Text Formatting For Leading Zeros
Apr 2, 2014I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
View 10 Replies View RelatedEdited Cell Always On Top?
Feb 9, 2013In long worksheets where I have to edit each cell, I would like it if the cell that is being edited is always on top of the screen. That way I wouldn't have to scroll as much.
View 1 Replies View RelatedPopup When Data Has Been Edited But Not After VBA Run
Apr 8, 2013I've got a data sheet (called "data") which contain a bunch of data, if someone edits the data manually then a msg boxpopups. which i've done using the code below in the code on that tab. However, I also have vba that places data on the tab that also triggers the popup message.
So how do i make it such that if the vba is run then the popup doesnt appear but if they edit the data manually then it does?
VB:
Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim IntersectRange As Range
[Code].....
Finding Last Edited Cell
Oct 25, 2013Is it possible to find the unsaved data in a spreadhseet/workbook? Or the last cell was edited?
After working on multiple workbooks when I try closing them excel confirms if the changes should be saved or not. I was wondering if there was a way to check what those changes were. If there was a way to find address of the cell that was edited last.
Excel - Search Userform That Can't Be Edited?
Apr 5, 2013I am looking to create a userform that submits data to my spreadsheet.The fields should be Reference Number, Title, forename, surname and DOB.The filling in of the reference number is compulsory, and should display a message box if a reference number has not been input.A button should be present on the spreadsheet that brings the userform up.
The search Userform should look the same as the input userform, except the fields are blanked out and uneditable.
The ability to search by any field is neccesary.
A list should be brought up containing the search results.
The user can then select the entry that they are looking for and then the uneditable userform comes up with the information that has be extracted from the existing entries.y impossible.
Macro Won't Run If Text Is Being Edited In Cell
May 27, 2014I have a log book that gets modified through out the day, and an auto run macro that runs at specific times to save the data to a database type sheet, and clear the form for the next shift. some of the operators are double clicking in the cell so they can type their comments. If they do not hit tab or enter when they are done, and the save time comes, the macro is not running, and I'm not collecting the data that I need. Is there a way to force the macro to run, or to force a tab/enter after 3 minutes of inactivity?
View 4 Replies View RelatedSlow Update Of Edited Links
Sep 26, 2008My business has a significant number of Excel sheets interlinked. One of them has about 10.000 links to about 60 files. Those 60 files are within a sub-folder and once a quarter the business needs to replace all 60 files with a new set. To establish this, they replace the sub-folder name in the link with the new name. To do this on 10.000 links takes about 72 minutes because Excel seems to open up a file for every updated cell. We tried to stop this by changing Excel to calculate manual, disabled "Update remote reference", but this didn't improve anything.
We have similiar issue when opening/updating the master file - it takes around 3 mins.
Besides that a proper database is certainly better for this requirement, is there anything we can do to improve the performance of the replace method. I was thnking about stopping Excel from validating/updating each single cell and after everything has been replaced to do it in one go without Excel opening and closing 10.000 times a file.
Userform Button To Update Edited Data?
Sep 13, 2013I have been working on a userform for entering in and editing data in a spreadsheet, but I'm stuck with the code for updating the edited data.
I have two pages on the Userform, one for adding a new entry, and one for editing an existing entry.
On the page for editing an existing entry, I have a combobox that displays information in the text boxes based the selection. What I need to be able to do is click save once I have made changes to the information in the text boxes and then have that information saved in the spreadsheet.
I have figured out all of the other buttons in the userform, but I am struggling with this last one. Here is my spreadsheet with some sample information entered in : ExcelFile.xls
Clear Cells When Another Cell Is Edited By Using A Loop
Oct 16, 2009I am making a workbook where the G column is drop-down menus that are populate a drop down on the H column. Whenever someone changes the selection of the G column I was to erase what is in the corresponding H column. I have this code that works, but I need to do this for 100 lines. Is there any way to put this into a loop instead of writing 100 if statements?
View 6 Replies View RelatedMake Column Unable To Be Edited Once Select Dropdown Value?
Jun 4, 2014Once an individual selects "NO" in column "O" I would like the rest of the columns to be grayed out so the no other information can be entered except for the last column for notation purposes.
View 3 Replies View Related