Protected Worksheet Macro To Insert Rows?
Feb 20, 2007
I have a protected worksheet, which I have 2 macros, 1 to spell check and 1 to insert rows, they both unprotect the sheet and re protect it again once they have completed. The problem I am having is that when I protect the work sheet first time round I tick the box to allow users to insert rows, once the Macros run they disable this functionality. Is there anyway I can include this in my Macros or do I need to add a new button!
View 9 Replies
ADVERTISEMENT
Oct 13, 2013
I have created a table where the first 6 columns are data entry and the next 6 columns are formulas to give desired results.
I have made all the data entry cells unlocked and the rest of the spreadsheet locked (cells with formulas etc.)
I would like to be able to insert rows to the protect sheet which I have managed to achieve but when I insert the row the formulas do not update in the row.
View 3 Replies
View Related
Apr 5, 2014
I 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.
View 2 Replies
View Related
Apr 7, 2009
Need to look at a number in a cell, inserts that many rows below that row, then repeats this for following rows that also have cells that indicate how many rows to insert.
Attached is a simple sample data sheet with how data looks before and how it should look after.
View 2 Replies
View Related
Sep 13, 2013
I need a VBA code for a worksheet to automatically insert cut paste a row from a range of unprotected rows to the range of protected rows once the user decides to do so.
I have a sheet called "Customer Stock" which has two tables called "Uncollected" and "Collected".
I need "Uncollected" table to be unprotected and "Collected" Table to be protected.
Once the a Customer collects the goods, the user can cut and insert the collected row from the unprotected "uncollected" table to just above the first row of the protected "collected" table,
so that the inserted cut row now should be protected.
The password should be 1234 and the user won't know the password. Only the administrator will be having it.
View 14 Replies
View Related
Jul 21, 2014
I was able to create a macro that allows a user to unhide rows in a protected worksheet without unprotecting the other locked fields (see below). My question is: I want to add to this command so that 1 row can not be "unhidden" if the previous row is still hidden. For example, row 25 can not be unhidden if row 24 is still hidden.
Macro command used:
Private Sub Commandbutton4_Click()
Sub Hide_Rows2h()
ActiveSheet.Unprotect Password:="xxx"
Rows("25").Hidden = Not Rows("25").Hidden
ActiveSheet.Protect Password:="xxx"
View 3 Replies
View Related
Jun 19, 2014
I got a worksheet here. I'd like to lock all the cell height and width using protected sheets function. I realized from time to time I have the need to hide them. How do I enable hiding sheets while maintaining cell integrity?
View 3 Replies
View Related
Apr 4, 2007
Is it possible to delete multiple rows when the worksheet is protected using vba? For Example Peps - Select a undetermined amount of rows and then use a macro to unprotect - delete the selected rows - protect.
View 5 Replies
View Related
Nov 15, 2013
Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part
Code:
Sub CountRows()
Dim Rng As Range, CountTrue As Long
Set Rng = Sheets("Checklist").Range("Work")
CountTrue = Application.WorksheetFunction.CountIf(Rng, "True")
End Sub
View 3 Replies
View Related
Mar 25, 2014
I want to run a macro in a worksheet which is proteced.
It's a simple macro which erases the content in (some) cells, nothing more. Even those celles are formatted as unlocked the macro fails.
View 4 Replies
View Related
Aug 16, 2007
I have the following macro and when it "reprotects" my worksheet I need it to also turn on these Protect Sheet properties:
Format cells
Format columns
Format rows
Insert hyperlinks
and I need it to leave on the defaults of Select locked cells and select unlocked cells
Sub Spell_Check()
ActiveSheet.Unprotect Password:="pmo"
Cells.CheckSpelling "SRdictionary.dic", SpellLang:=1033
ActiveSheet.Protect Password:="pmo", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub
View 4 Replies
View Related
May 18, 2009
I have incorporated print/print-preview command buttons/VBA into a workbook with protected sheets. To enable these command buttons to function when the relevant sheet is protected, I have had to add VBA code to unprotect the sheet before generating the print preview, and then to protect it again afterwards. However, the code I have used (see below) prompts the user to enter the protection password, is there any code I can use where I can write the password into the code itself to unprotect the worksheet without the using having to enter the password?
'Unprotect Sheet
ActiveSheet.Unprotect
'Print preview & cell formatting code
'Protect Sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True
View 2 Replies
View Related
Jun 8, 2008
I'm not fond with macros, but this is what i need help with.
I need two macros one to sort ascending (Ctrl+A), and one to sort descending (Ctrl+D).
This is what the macro to do. Unprotect, sort selected column, protect.
View 7 Replies
View Related
Mar 23, 2009
My workbook contains 10 worksheets. Some users will use all 10 worksheets; some will use only 5 worksheets. If a user is not using a worksheet, I would like to hide it but leave it in the workbook.
However, I want to keep all workbooks in sync even though a user may choose to not use a particular worksheet. When my macros encounter a hidden worksheet, they stall. Does coding exist that allows a Macro to run on a hidden worksheet without making the worksheet visible?
View 2 Replies
View Related
Feb 9, 2013
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....
View 1 Replies
View Related
Mar 15, 2014
I'm a macro novice and have been trying to teach myself how to write the correct one for a task I need to do, but I cannot seem to get it right. Basically, I have bunch of data and for one of the variables, different values are separated by commas. What I want is to create a row copying the info below for each piece of data after the comma.
Sheet1
A
B
C
D
[Code].....
I suspect there is a fairly easy way to do this, but I cannot figure it out from searching the forums (or rather, I can't get it to work right).
View 6 Replies
View Related
Jul 25, 2014
I have a macro that runs in Sheet 1 and enters the word "Complete" in cell P of the active row. The same cell that requires the word "Complete" in Sheet 2 is in cell T of the active row. Is there a way to have the same shortcut that will identify which worksheet it is in and go to the appropriate cell?
Code:
Range("P" & ActiveCell.Row).Select
ActiveCell.FormulaR1C1 = "Completed"
ActiveCell.Offset(0, 1).Range("A1").Select
View 4 Replies
View Related
Mar 9, 2007
Still struggling with the calculator. Is there anyway to get a macro to insert a set number of worksheets. The set number would be the sum of two fields on Sheet 1.
Example attached. I have searched the forum and can now use a macro to insert a worksheet but have no idea how to tailor it.
View 2 Replies
View Related
Dec 11, 2013
I am working on an excel table in my worksheet contains formulas and locked cells. lets say table range is from column "a" to "f" and there is formula on cell in columns "c" and "f". I just locked cells in column "c" and "f" to prevent people changing the formula accidentally. but when the sheet is protected i am not able to insert or delete rows as the rows has locked cells in columns "c" and "f".
I tried the below macro to unlock the sheet, insert row and lock the sheet again:
[Code] .....
So by running the macro it asks the password and do the job but the issue is it needs password and i do not want people to know my password. I don't want them able to unlock the workbook and make any undesired changes. I want them to be allowed insert or delete rows and input data in unlocked cell. I want the sheet to be locked all the time and only when people want to insert or delete a row it be unlocked and be locked again after inserting or deleting whit no need to insert password in this case only.
View 1 Replies
View Related
Nov 27, 2008
Pls help me do a macro that upon clicking a button it will copy and paste a column heading (located in Row1 of Sheet1) to all non-empty worksheets (Sheet 2 to Sheet N) in the workbook simultaneously. Note that each worksheet has no column heading and has the same number of columns as that of the column header in Sheet1. Worksheet 2 to Worksheet N contains data extracted daily.
View 7 Replies
View Related
Dec 13, 2011
Writing a macro to insert two columns with IF formulas into a Worksheet. The 2 columns to be added would be after Column G on Sheet 1.
The new Column H would be
"=IF(G1>=Q#,G#/Q#,0)"
And the new Column I would be
"=IF(G#>=Q#,MOD(G#,Q#),G#)",
Where # represents the row number.
View 3 Replies
View Related
Aug 21, 2012
I have a folder for each month, and in the folder there are worksheets for each day of the month. eg for January folder, it will contain 31 worksheets. They all have the same column headings. I want to know if there is any way i can use a macro to automatically insert a formula in each worksheet, using loop?
View 2 Replies
View Related
Apr 21, 2007
Is that possibe to use the "templeate" sheet and lookup the "Master" sheet to create a new worksheet called "result"
View 2 Replies
View Related
Oct 31, 2009
I have a worksheet that includes the list of buildings in an area and the number of households in each building. I want to list the households on separate rows and to number them from 1 to x according to the total number I have. Is it possible to do this using a macro, if so, how?
View 5 Replies
View Related
Jan 18, 2010
I've created a macro to assign to a button that inserts a row above say row 10, then another macro to assign to a different button that inserts a row above row 20. My problem is, having clicked the first button a row is inserted above row 10 but of course row 20 now becomes row 21, so now I need the second button to insert a row above row 21 instead of 20.
Is there any way I can write a macro that remembers the old row numbers?
View 14 Replies
View Related
Sep 18, 2005
Example:
My sheet needs to insert a blank row under a number everytime it changes.
The column "C" may have 20 or so rows with the number 10 for example, the
next five might be the number 8, I need a row between the last number ten and
the first number eight. This may be repeated many times through the
spreadsheet.
As well, the adjacent columns "D" & "E" are the same as described above.
all 3 columns are continuous ie: ....
View 12 Replies
View Related
May 25, 2007
I need a macro that will insert 11 rows before the word total. The word total will always be merged between col A&B but will not always be in the same row. It might be in row 5 one day and row 25 the next.
View 9 Replies
View Related
Dec 2, 2008
I have recorded a macro to insert six rows. I'd like to take this one step further and have the user enter the number of rows they want to insert. Right now the data is entered in A10:F33.
View 9 Replies
View Related
Feb 10, 2007
Workbook Password Protect 2 Levels. I have managed to write simple but effective code that allows only one password to be enetered to open a SS & offers 3 levels of protection, limited permission to enter data, read-only & data completely hidden. This is excellent for my needs however I want the user who has limited rw to be able to insert a pic file which is their signature but because the sheet has protected cells it won't allow user to insert picture jpg file.
I have just thought of a creative solution tho. What if the picture file is already saved in the workbook and by using code to ask to insert pic file it copies from one location in SS to the appropriate cell in the worksheets?
The scenario is that I have fortnightly timesheets in 12 calendar months in 12 separate sheets. When staff member fills in timesheet I want them to insert signature for that f/night indicating thatthey have filled it in & it is correct but because of protection it won't allow me to.
View 2 Replies
View Related
Apr 8, 2014
I've got a dataset of certain trades across different markets and traders, and on different days. The date of the trade is in column A, the contract ID is column D and the trader ID is column J.
Traders do not trade every day. I would like to insert a new row/rows for each date on which traders do not trade between their first and last trade, for each market in which they participate.
Is this possible using macros?
And, if so, then I would like to insert the high and low prices for the market on those dates from a different worksheet into the newly created blank rows. On this second workbook the contract ID is column A, the date is column C, the high price is column F and the low price is column E. I would like the low price to be inserted into column AJ on the original worksheet and the high price into column AK.
View 2 Replies
View Related
