Lock All Rows Which Have A "Yes"
Oct 28, 2008
I have the following code which runs a macro when "Yes" is placed in column K. However, i also want a macro to lock all rows which have a "Yes" in column J after the macro [ExtractToSheets] is run. How do i add that into here?? I've tried lots of possibilities but normally only the row which the macro is run from actually locks or the macro doesn't run at all.
View 9 Replies
ADVERTISEMENT
Sep 8, 2009
Basically I have recently started my own personal music chart and I'm having problems with a Year To Date page.
Basically all I want to know how to do is how to lock some cells together and rank many rows of those cells with 1 column where the total is?
I have attached the spreadsheet as a zip files as I was having problems uploading it as a xls file.
View 13 Replies
View Related
Feb 18, 2009
This (unprotects the sheet first, then) unhids rows (starting at row 1500, until it reaches row 4). Now my most current vexation- How do I lock/protect the past rows?
Say a co-worker enters information in row 1500 and walks away. The next co-worker inserts a new row (which would be row 1499) to place data in there, how can I get row 1500 to lock (or become protected) as the new row (which in this case 1499) would become unhidden?
It would be very important that the information in the previous rows remain locked so that nobody can change any client information (unless they had supervisory/administration access).
If it is not possible to protect or lock the whole row, it would also work if I could just lock one of the cells in the previous row.
I am using the following
Sub Row_Unhiding()
'
' Row_Unhiding Macro
' Macro recorded 2/12/2009 by gr8_big_geek
'
' Keyboard Shortcut: Ctrl+i
'
ActiveSheet.Unprotect Password:="secret"
R = 1500
Do Until Rows(R).Hidden = True
R = R - 1
If R < 4 Then
MsgBox "No more rows to add!", vbCritical, "Error"
Exit Sub
End If
Loop
Rows(R).Hidden = False
ActiveSheet.Protect Password:="secret"
End Sub
View 9 Replies
View Related
Jul 15, 2014
The small attached "Demo2" file indicates my problem. In the "Scorecard" sheet I'm using macros driven from listbox menus to hide or unhide rows on the "Tasks" sheet. It is VITAL that the rows on the "Tasks" sheet not be deleted or have new rows inserted. But, if I Protect them then the list box macros won't function. Is there a macro that will allow these macros to hide or unhide rows without allowing a user to add or delete these rows in the "Tasks" sheet? If possible a simple "global" solution is preferred as I have 100+ If/Then entries to contend with...
Attached File : DEMO2.xlsm
View 1 Replies
View Related
Jun 6, 2012
I have a SS that puls data from another sheet using the following formula
=IF(OR('Site Visit ASSESSMENT'!D9={"PARTIAL COMPLIANCE","NOT COMPLIANT","NOT ASSESSED","ADDITIONAL INFO NEEDED"}),'Site Visit ASSESSMENT'!A9,"")
I also have VB Code in teh sheet as follows to hide rows. If I protect colum A and protect sheet I get an error. How can I protect the sheet and still have the following commnad work?
Private Sub CommandButton1_Click()
'Toggle rows hidden where cells in column D are blank
If ActiveSheet.AutoFilterMode Then
[Code].....
View 6 Replies
View Related
Dec 8, 2012
I have dedicated rows and column units.I do not want these to be changed from there current setting.Can just rows and columns be locked and password protected? If yes,where and how do i process this feature?
Excel 2010
View 9 Replies
View Related
Mar 5, 2008
I have a workbook list of activities that I am hoping will visualise the scheduling of routine maintenance tasks in my engineering department. The date of the most recent check is entered in row 6 and the interval in row 5. Hence the number 7 denotes a weekly check. The conditional format is such that the reds indicate that a check needs to be carried out. If the check is done on the scheduled day then a "Y" is entered into the square. This will then turn the square green. If "Y" is entered on any other day then the square turns orange to indicate the check is done, if not on time. The red squares do not change colour unless a yes is entered. This is designed to show the history of checks and give us the opportunity to show compliance to routines. What I need to do is lock the rows that have passed the current date, to prevent unscupulous modification. Thus the macro would look down column A and if the date is less than today() would lock the row. I have truncated the file as much as I can. A nice to have would be the facility for an adminstrator to go in (perhaps password protected) to make changes if necessary.
View 3 Replies
View Related
Jun 16, 2008
Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?
View 6 Replies
View Related
Jul 30, 2014
I am new to Excel programming, haven't done any before and not familiar with creating/editing macros. Using Excel 2007.
I would like to automatically lock all the cells in a row if the date in column A in that row is >= 7 days old. Data is entered once a week (some time Sunday - Saturday) and I would like historical data (rows) to be locked so that they can't be edited inadvertently.
If possible could I also highlight the current working week somehow?
I have attached the worksheet : testworksheet.xlsx
View 3 Replies
View Related
Jul 2, 2009
do you know if there is vba code to lock formulas so someone sees but does not touch and the same for values see but cant change??
my sheets with formulas are SUMMARY and Sheet2
sheet with value is TABLE
View 9 Replies
View Related
Dec 18, 2007
I have a spreadsheet that requires the user to use ALL CAPS when they fill it out. Is there a way to have VBA turn on the CAP Lock regardless of the persons keyboard setting? I have asked that the users use ALL CAPS but of course that leave a lot of room for error.
View 9 Replies
View Related
Apr 15, 2014
I'm looking at creating some templates that people will submit to me on a monthly basis but i'd like to 'lock' the workbook name, is this possible?
View 2 Replies
View Related
Jul 25, 2007
=IF(A4='Exported Data'!B2,'Exported Data'!C2,"")
How can I lock A so when I copy over to the right the formula doesn't change that reference to B4?
Is there a way to have the True value (Exported Data'!C2) reference one cell over instead of selecting the cell to reference from?
View 9 Replies
View Related
Nov 20, 2008
Is there a way to lock the title of the workbook so if someone else has it, they can't rename it even if they move it to their personal computer?
View 2 Replies
View Related
Apr 4, 2009
Having a problem locking the sheet and still have it work. Not sure if their is a easy work around for this. Just lock sheet and see what I get.
View 7 Replies
View Related
Nov 21, 2009
Would like to set CAPS-LOCK On and Off using VBA. Can someone help please?
View 5 Replies
View Related
Oct 25, 2011
I have a excel document with 5 tabs (sheets) and, because different people work with a different tab (sheet), I want to lock each tab (sheet). I want the tab to be visible on the tab bar but when you click on it you will not be able to see the content of that tab (sheet) until you provide the right password (each tab with a different pasword).
View 2 Replies
View Related
Nov 16, 2011
Is there a way in excel to prevent someone from "re-ordering" tabs in a workbook without using a macro?
I am using a sum formula that goes across multiple tabs, but noted when I rearranged the order of the tabs, that the results of the formula changed. After some minor research, I noted that I had moved a tab outside the "sum range" inadvertently.
View 1 Replies
View Related
Nov 1, 2002
How do you programatically enable the CAPSLOCK key in VBA? I've tried:
Sendkeys "{CAPSLOCK}"
When I run this, Excel's status bar flashes "CAPS" for about 1 second then disappears. It seems to have no impact on the keyboard for case-sensitivity - which is what I'm shooting for.
View 9 Replies
View Related
Jan 12, 2008
Need code that would make cell J2 be populated with U no matter what was placed in cell J2 either manually or externally.
View 9 Replies
View Related
May 14, 2009
how to lock a sheet in Excel 2007 so that no one can navigate to that sheet.
View 9 Replies
View Related
Feb 24, 2004
I can have amacro which will lock each cell where a "Y" or "N" is inserted........ eg the netire sheet is unprotected but the moment Y is inserted in C 5 the cell gets locked, next entry in D 10 has a N entry and that too upon entry get's locked
The idea being once Y or N is typed that cell can no longer be changed
View 9 Replies
View Related
Nov 7, 2006
I am aware of setting protection to certain cells by the Excel Options. which is unlocking the by defalut locked cells and then setting the sheet protect in tools->protection. I want this to be done through VBA code.
i have a template which has text in some cells and some cells are blank, i need to lock the cells which has text and keep the blank cells unlocked for user input. As this is a template it will be copied to another sheet, so i need to retain the protection properties in all new sheets (copied from the template). Attaching the template for which the protection needs to be done.As of now the sheet1 has protection property set through the toolbar properties.
View 2 Replies
View Related
May 1, 2007
How it is possible to lock the VBA coding and Modules? I mean when Some one want to view VBA coding the Password window should not appear, istead of that a message box appears with message something like "Not viewable"
View 9 Replies
View Related
Feb 8, 2014
I use the attached workbook to keep tack of feed use in turkeys. when a new flock is added to a farm (Airport View or Home farm) A template is copied and pasted from the "templates" sheet depending on what type of diet they will be on. (Ralco ABF or Ralco Standard) that template refers to the Ration sheet to calculate how much corn is in each diet. After a flock is complete I will never change the numbers again, it is just for record keeping. I want to be able to change the Corn/ton numbers in a ration (on the rations tab) if need be and not affect all the older completed records.
View 1 Replies
View Related
Apr 11, 2013
I want lock a picture in excel like logo, if i copy that logo and past in another sheet the position should be same as parent sheet.
View 1 Replies
View Related
Oct 28, 2013
How to lock a formula column that need a password in editing the formula
View 3 Replies
View Related
Feb 28, 2014
Can we lock or disable text box editing in excel without protecting sheets?
View 1 Replies
View Related
Jul 2, 2014
I have a multisheet excel workbook that I am programming. I was wondering if there is a way to lock a cell on one sheet after the initial value was put in, allowing me to change it later. This may not be so clear so here is the problem....
I have Workbook1. The input from cell A3 on Sheet1 is 5. I have made a second sheet called Order Form. In cell B5 of Order form I have made it ='Sheet1'!A3. I am looking for a way to be able to change cell A3 on Sheet1, but keep the 5 on Order Form.
View 1 Replies
View Related
May 29, 2008
I have an sheet(see attached) with some dummy data, now i want to lock (with password) only those cells depending on the dropdown (highlighted in sheet).For eg,if we select A from dropdown of col c and then locked from dropdown of col D , it should lock the complete row(s) of value A in col H and rows with value B shall remain editable.
View 14 Replies
View Related