Allow Users To Edit Ranges Not Working After Running Macro?
Feb 14, 2013
I have a sheet that I have added protection to (Tools / Protection / Protect Sheet) with a password (PostItNote). Before I protect the sheet I have allowed users to edit ranges (Tools / Protection / Allow Users To Edit Ranges). I have made my selection of the cells that users are allowed to edit and protected the sheet. I now want to run a macro that will un-protect the sheet (in order to allow the macro to edit the protected cells) make some changes and then re-protect the sheet. The macro runs as it should do but when it stops I am unable to edit the cells that I allowed users to edit? For some reason the areas that I enabled users to edit have been changed allowing users to only edit far less cells that I originally set!
I have a sheet which has many columns in it, all columns are protected except column B. When I want to enter data always I have to insert a column in column B. When I clicked insert button, my B column move to C and B column become protected and C become unprotected. My query is always I want B column should be protected (even if i clicked on insert column button too).
I'm trying to control the number of ways people can mess-up this worksheet. I have several buttons that copy one object from a worksheet "FISH PARTS" and paste them onto another "THE JUMPER FISHBONE"
My question is can I prevent people from copying objects from the first worksheet if they already exist on the second? Specific to the code below: how do I make sure "BONE_1" doesn't already exist on the worksheet "THE JUMPER FISHBONE" before allowing it to be copied from the other worksheet. And can I generate a message box that tells the user the object already exists on the page.
I used keypress code in one of forms, after running following command non of my keyboard is working in excel. When I am restarting excel everything is fine. what could be the reason?
I have a spreadsheet that is very large and formated a certain way. I want to make sure that a user cant put the cursor in the lower left corner and copy cells by dragging, copy & paste, or do anything other than input data and be able to highleght text and reinput data in case they mistyped or misspelled the data.
ex: user clicks on cell E33 and types in '1234568' and then move to next cell. The user can click back on E33 and can correct by retyping. That is all the user is allowed. The cells in question are also in ranges if this helps. EX: 'E33:AJ67'
I 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.
I did the huge macro that names a bunch of dynamic ranges, all similar to the following: Range("=OFFSET(BD!$C$32,0,1,1,COUNT(BD!$32:$32))").Name = "ChtBDLabel"
I tested it as I went along, so I know it was working. Then I worked on several other tasks (copying the range to another location on the sheet, making graphs from the ranges). Now when I go back to run this again, I get: Run-time error '1004': method 'Range' of object '_Global' failed. I went and looked what I thought were my named ranges, and they were wrong (not dynamic, just a cell reference). I deleted them and tried again with same error.
I have built an Application in Excel that is used by a userbase of 50+ people. Now, as standard, they are only able to access the document read only. The application writes data to an ".mdb" but the user cannot change the application directly istself.
Now, we've had the problem over the last few days that the program is running slowly when it is trying to write data back to the ".mdb" file.
I believe that it could be because connection to the sever that our application is running slowly (or the server is too highly trafficed) but our server team are telling me there is nothing wrong with the server and it's running slowly because there are too many people in one Excel application/
I have set up a basic sheet for a colleague, whereby the Team Leader can enter client names into a control sheet, and then the team can add the client names into the allocations sheet when they take on the care for that client, and Conditional Formatting colour codes the cells to show which program a client comes under.
My problem is, the conditional formatting works perfectly on my computer, and another colleague's computer, but for two other colleagues, only half of the colour coding works... leaving half of the clients cells white, indicating they are not allocated to a program.
I have checked that we are all using the same network pathway, macro security settings etc.
Heres a strange thing, My latest version of a spreadsheet i have design has got a small problem. When clicking on a cell, nothing appears in the edit window, and when you double click on the cell to edit the contents the data disappears.
This only happens on one sheet within a workbook of 7 pages. The werid thing is that it does not happen on the version before
Any ideas how this could of happened and is there a fix?
I copied (with 'Paste Link') rows from a master Excel file to a file that performs calculations. This file (and many others like it) need to be modifed by other people. My tester has informed me that the data is not updating between the files for her, although it does work for me.
When I check 'Data->Edit Links' it shows the status of the file as 'unknown'. If I click 'Check status' it is then OK, but the next time I look at it, it has reverted to 'unknown'.
1. How do I get the status to remain at OK?
2. Why do the links work for me, but not for my end users?
I try to edit comments in cells of a certain column and nothing happens. I am able to edit other comments throughout the spreadsheet, but when I select "edit comments" for these particular cells, no edit box is displayed for editing. It worked fine a couple of weeks ago.
If MonthView control can be set to allow users to select multiple ranges and enter different dates into those ranges? I know I was able to do with with the previous Datepicker control and I thought it was as easy as setting the Show Modal property to true.
I am trying to create a timekeeping workbook that will track the time I spend on certain tasks. I have created the attached worksheet including macros that correctly allows me switch between tasks but I cannot run this set of macros in one workbook and work in another.
I created a simply macro that will adjust the headings of a group of cells as follows.
But if I add a row/column to the sheet, it will then of course attempt to place the headings in the wrong cells.
I have now given that group of cells/range a Name of "AR_Buckets". But I'm not certain how to modify the macro to ensure it always places the information in the correct place.
I have a data set that is defined as a table (Ctrl + T). There are formulas so I locked those cells and Protected the sheet. If I am at the bottom right cell, and press Tab to add a new row, I get an error message: "Cannot use this table functionality on a protected sheet"
I really need to protect the cells with formulas so that the are not accidentally deleted. But I also want to retain the Table format.
I have an unwanted link and I can't remove it. I have tried through the Edit Links dialog box, searched for [ in formulas throughout the workbook and gone searching manually for the reference but to no avail.
I am getting a #REF error when using an INDIRECT function within a MATCH function to check against a dynamic named range. Basically, I am trying to get the row reference so that I can go back and extract other data from the row (in a table contained in another sheet) into the current worksheet.
I attach an example file for reference. The issue arises when a Dynamic Named Range is used. In the example file, if a value from a static range is chosen, the match with indirect function works, but it fails with the dynamic range.
I have a code in file A that opens several files (B,C,D&E), copies some data from them, then closes the files. That part of the code works fine, but each of the files that are opened (B,C,D,&E) have a Workbook Open event that causes the file to save automatically every 30 seconds. (I know this is not recommended, but this is what the user wants.) The files also have a Workbook Before Close event that is supposed to stop the timer so the file will close without reopening. These each run fine on their own.But if I run code A, the workbook Before Close event in file B (C,D, & E) does not seem to run and the files reopen after 30 seconds to save. When I step through the code it works fine and goes through the Before Close event in each file and the files remain closed.
I want to use it to get data from snmp and put it in excel cell. I managed to do this with this macro:
[Code] .....
Now I want to be able to change the IP address. For example in cell A4 to fill the IP. And the IP to change in the macro. So this macro to be use for different hosts.
I place a button for this macro and when I press it a cmd windows is popping for 1 second and hide. How can I make it not to show at all.
When saving a file, you get a popup with a suggested path. I need to find a macro that will either offer a suggested file name based on a cell's date value (a1) and a suggested path (to the desktop) for any user that uses the spreadsheet - or simply will save the file using the cell value as the name to the user's desktop.
Each user will have their own version of the file, to save to their own desktop. The macro will need to overcome the issues that each user's path to their desktop will be unique since each will have logged in to the window's session with their own profile.
I am in need of a macro that will allow users to delete an entry in a named range as well as delete a range named the same. I would like the macro to open a list showing the contents of a range named "Locations". Once opened the users may select a name from the list and click on a button to remove that item from the named range. the macro can then delete the cell that name is in and shift the remaining cells up, changing the range. Second the macro should delete a named range that matches the one the user selects. once delete I would like to have a pop say "Location Removed Sucessfully".
i have a spreadsheet that data is imported to from a different program. however the amount of rows of data will change from day to day. I need to be able to select this range of data before i run a macro on it.
I have a sheet which is protected but people are still able to change cell formats etc if they use the standard paste method. I want people to be able to paste data in but it forces paste special values to stop people pasting formats etc over already formatted and locked cells.
I know I could write a macro and run it using the shortcut keys ctrl-v but then the users lose the ability to undo if they make a mistake.
Is there a way to force paste special or allow users to run a macro but be able to do undo if they make a mistake?
I have a spreadsheet that has a button named Exit Requisition Program on it to close the application with. When the button is used everything is wonderful. All of the data on the sheet is saved and the application closes. I have a problem with some users clicking the red X without saving changes and that creates a big problem.
What I need to do is any one of several things. 1. Remove the red X completely. 2. Deactivate the red X. 3. Have a message box pop up instructing the user to use the Exit Button when the red X is used. Any one of the three methods listed above would work for me.
