When Protect The Sheet - Delete And Shift Up Disable
Aug 8, 2013
I have a sheet,i want the users unable to select any cells,but a column is free to select an change and enter the inputs. I want to delete and shift up zero cells on that free column,but this property is disable when i protect the sheet.
How I protect other cells from selection and change,and enable delete and shift up for the column that not protected.
For example: I have the attachment file,every column is protected except column "H". I want a macro to when I clear content of any cell in this column,every cells shift up and every non zero cell put from row 1 to... and after them zero cells put.
I have a problem in making a delete function/sub and i'm applying it to a listbox in userform ... i'm trying to delete a row of data in the listbox which refers to cells (A12:D12) ... and at the same row (row=12), there are other data cells (E12:H12) ... after deleting the cells all other data below the deleted cells will shift up taking over the deleted cells ...
Search for "CAP_ACTV_LN_SEQ" keyword in column A of Sheet1 and when found go two rows down and do the following
Delete cell D128 and E128 ( if more than 1 rows then do the same for all rows till "selected" keyword detected below it eg C131 in this case ) and Shift cells in the right to two column left as shown in figure below -
I am trying to delete a group of four cells and have the remaining block shift up. My code is below and crashes on the red. What is the right code to get this to work?
Code:
Do ActiveCell.Offset(1, 0).Select If ActiveCell.Value = Range("G1").Value Then Range(ActiveCell:ActiveCell.Resize(0, 3)).Delete Shift:=xlUp Exit Do End If Loop
I am looking for a macro that can delete whole rows (shift cells up then). How i would like it to perform is if there is nothing in cell(s) B then delete the whole row?
I'm trying to do is check each Row and move the last 3 Columns of data in each Row to the left so they fall under the headings NHA2, NHA1, and OEM PN (Columns U, V, and W). Columns A and B are temporary. Column A utilizes the formula "= COUNTA(B2:AG2)" to count the number of cells in the row that contain data. Column B utilizes the formula "=COUNTA(C:C)" to count the total rows of data in the active worksheet. Columns A and B will be deleted at the end of the Macro.
The GOAL (END RESULT) is for Columns A through W (and ONLY these Columns) to contain data (keeping in mind that Columns A and B will be deleted at the end). If some rows have Column W (and beyond) blank, then I want to MOVE data from Columns U & V over to V & W and then COPY data from Column T into the [currently] blank Column U.
I've been trying to get the Macro to start in the last row and, using CASE Statements, delete the proper range of cells and SHIFT LEFT as it counts backwards towards the first row. I'm not too sure this is the best approach and could really use some advice from the experts! I can provide a "test" file if necessary ... the test file I've been working with is approximately 6.5MB, but I can delete most of the 14,287 rows and still give a good representation of how the data varies.
Sub b_DeleteCellsShiftLeft() ' DeleteShiftLeft Macro ' Macro recorded 3/14/2007 by George Nicholaou
' Need to assign variable for current row (?) ' r=ROW()
' Columns A and B are temporary ' Column A utilizes the formula "=COUNTA(B2:AG2)" to count the number of cells in ' the row that contain data ' Column B utilizes the formula "=COUNTA(C:C)" to count the total rows of data ' in the active worksheet ' They will be deleted at the end of the Macro ' What I want the Macro to do from this point is: ' 1. Check each Row and move the last 3 Columns of data in each Row to the left so ' they fall under the headings NHA2, NHA1, and OEM PN (Columns U, V, and W) ' 2. The GOAL (END/RESULT) is for Columns A through W (and ONLY these Columns) ' to contain data (keep in mind, Columns A and B will be deleted at the end) ' 3. If some rows have Column W blank, then MOVE data from Columns U & V over to V & W............
I have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.
Private Sub CommandButton1_Click() ActiveSheet.Unprotect "rainforest" Columns("O:O").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Selection.AutoFilter Field:=1 Selection.AutoFilter ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowSorting:=True, AllowFiltering:=True End Sub
I am trying to find a method for deleting empty cells in a range and shift cells left upon deletion automatically. I could do this manually, but with 3000 rows of data it could be a long task. I have attched a small spreadsheet to show what I am looking for.
I am fitting 2 sets of data to match eachother. I want to have as much data as possible but only for the same dates of each dataset.
I have 4 coloumns. Dataset 1: A is date B is data Dataset 2: C is date D is data
I want to compare A vs B one row at a time. If they are not similar it should delete and shift up the values in A+B or C+D according to the lowest value (date) of A vs B.
Remember that if the macro just deleted and shifted up it should compare the same (undeleted) value against the new value that just shifted up in the other coloumn.
I am trying to create a macro that will add and delete individual cells depending on an IF statement. The code that I created deletes/adds cells regardless of the IF statement.
If Sheet1.Range("F3").Value = Sheet1.Range("G2").Value Then Range("G2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End
ElseIf Sheet1.Range("F3").Value = Sheet1.Range("G4").Value Then Range("G3").Delete Shift:=xlUp End End If
Also, what is the easiest way to have this code apply to all rows within column F, while adjusting the corresponding cells referenced in column G? The last row within the spreadsheet changes.
I am looking for a method which would delete empty cells in a row, and then shift the remaining data to the left (filling the cells with data). I know that this could be done manually, but I need it completed for 3000 rows! I have attached a simple sheet to show what I'm Looking for.
I have a spread sheet that has RX codes as field headers and ones or zeros as values. I want to find all cells which contain "0" and delete those cells while shifting the remaining cells left. The range of cells is aj1:dr17365 and the sheet name is "May 07 Rx HCCs"
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column. For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double Dim shour As Integer Dim smin As Integer Dim ehour As Integer Dim emin As Integer Dim stod As String Dim etod As String pday = 8 pnight = 12 shour = Hour(st) smin = Minute(st) + shour * 60 If (shour >= 8 & shour < 20) Then stod = "day" Else stod = "night" End If ehour = Hour(en) emin = Minute(en) + ehour * 60 If (ehour >= 8 & ehour < 20) Then.................
I am creating a fool proof, well as near as I can get it, spreadsheet for them to work in and want them to follow the rules I set out!!
I am able to disable cut, copy, paste etc but was wondering if it was possible for to disable to use of the "Delete" function from the menu when you right click on a cell and also disable to the Delete key on the keyboard?
Months Jan to Dec 09 are there and shifts are divided up to ABCDE. I need another sheet to return the value of the shift time i.e. 1330-2130, probably via a lookup?? In my second sheet I have the Day number, the month and the shift letter.
I would like to disable cut/copy/paste and maybe delete inside a worksheet. Reading earlier threads on this subject provided the code to put in the ThisWorkbook module, where it works great for all my worksheets. However, there is one worksheet where it is necessary to have copy/paste, either with control c or right click/copy. Here is part of that code provided by Tom Urtis:
Private Sub Workbook_Activate() Application.CutCopyMode = False Application.OnKey "^c", "" Application.CellDragAndDrop = False End Sub
What do I replace Application with to work on just the worksheet level?
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
I'm trying to hide a few columns on my spreadsheet. I'm getting a "cannot shift objects off sheet" error message 2010 Excel program. I never had trouble with my 2003 excel.
There are no comments on this sheet, 'Display options for this workbook' already says all. I do have macro buttons on the top... but changed them all to move and size with cells.
Cell D7 - data validation reads as such: It is a List and the source is I6:I7. I have In-Cell dropdown selected. Ignore Blank is not checked.
I now password protect the sheet and only D7 is unlocked. real simple, the person using it can only use D7 and select one of two in the drop down menu.
The problem is, if the user clicks on D7 and hits delete, the cell goes blank. How can I avoid this?
I have very old file which contains lot of sheets. And I am unable to insert rows in any of the sheets in the file. It throws an error "Cannot shift object off sheet".
I am running excel 2007 and I have 23 worksheets in a workbook. I have no links or hidden rows or colums. I have no merged cells or comments. My biggest sheet is A1:
AW8096. I use VB macros to add lines and move data from one set of workbooks to this one.
I have built several sheets using this proceedure.
All of a sudden I get the error "Cannot shift objects off sheet" when I run a routine I have 50,000 times. This even happens on new blank sheet so I know its' not on the sheet.
I have checked to make sure there are no hidden comments, row or columns. I have deleted most of the worksheets and I still get the error. I have made copies of the workbook and used different file types to save it. I can create a new black sheet in the workbook that is empty and the same thing happens.
The only way I can get it to work is to creat a new blank workbook and copy and paste the sheets into the new workbook. Yes I have serched the FAQ and knowledge base and tried all of those fixes as well.
I have a very wide sheet in which I have hidden and saved seven views.I am using it for a very long time but when i did some edit work in the 'full' view,the other views are not showing. In stead I am getting the messages "cannot shift objects off sheet" and "some view settings could not be applied".This is very frustrating since a lot of patience was tested while making this wide data.
I am looking for a macro that can do one of two things. I am not sure which way I want to go with this yet and how automated it should be. Either scenario will result in a significant time save.
Scenario One 1. Copy & Paste Special Values on the selected/active worksheet 2. Protect Selected Worksheet with password "1234" 3. Delete ALL worksheets except the selected/active worksheet from the file. Names of other sheets can be various and are not static
For example: A workbook has the following: sheets1, sheet2, sheet3, sheet_unknown_name If you are working in sheet1 the macro would copy paste special values the entire sheet, protect sheet1 with the password "1234" and delete any and all other sheets.
Scenario Two 1. Copy & Paste Special Values on the selected/active worksheet into a new workbook 2. Protect the new workbook with password "1234" 3. Rename the new workbook with the original workbook name adding either "em_" to the front or "_em" to the end of the filename 4. File, Send To, Mail Recipient as attachment.
I want a sheet to copy and paste into another worksheet, I have that code and its working fine, but what I also want to do is for the sheet to be protected,so the code would be, copy, unlock, paste, lock, save.
Here is the code I have so far. I know i have to include ActiveSheet.Unprotect "passowrd" ActiveSheet.Protect "password", but I dont know where to include that last part of the code.
