I have been searching for a macro that would lock all the non blank cells (cells that have any value/text) after the workbook is saved.
When the user clicks on save a message box should prompt stating that "The data you entered has been locked successfully".
I have a spreadsheet for recording and summarising particular transactions. Column B records the date and Col C details the transaction type (from a data valdation list). Using conditional formatting the cell (in the same row) to whch the transaction value is to be entered is highlighted.
Other cells in the row contain formulas and are locked and the sheet is password protected.
On saving or closing the workbook I want to lock all the cells (used or unused) in any row in which there is a date in column B. How can I do this?
I have used the following code to lock cells when saving.
I need to exclude Cell B2 when saving
let me know what changes i should make.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
if ActiveSheet.Name = "Summary" Then
On Error Resume Next
'Resume to next line if any error occurs
Dim Cell As Range
With ActiveSheet
'first of all unprotect the entire
'sheet and unlock all cells
.Unprotect Password:="pw"
[code]....
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010
[URL]
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.
I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...
Auto lock cells after data entered and SAVED.
Auto Lock Selected Range of Cells After Saving
I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.
This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.
i have a situation where i want cells c13:e13 to be locked if cell g5 is blank. and same cells will be unlocked only if g5 contains text only.
View 4 Replies View RelatedI need to lock cells or ranges in a worksheet which has value (any value)....
The cells which are blank should be unlocked so that the users can enter data.
I have spreadsheet of data, I need to extract any rows that have blanks cells in columns F or P or T.
If possible I would like a macro I could run that would cut all of the rows that meet the above criteria and paste them in to a separate sheet.
I Want A Code That Will Save My Workbook To A Specific File (different Form The One It Is Currently In) Using The Name That I Have Typed In Cell D13 In The Workbook
View 9 Replies View RelatedI am trying to lock any cell within a set range that contains data but I want this only to be activated when the user saves the file.
View 3 Replies View RelatedNeed the VBA code to close a workbook, and not save it? I need it to open a workbook, run a macro, and close without saving. The code I have thus far is:
View 3 Replies View Relatedi m designing a test for a school and want to add a macro that will be assigned to a button at the end of the test i want the macro to close down the the excel program running and not prompt the user to save the workbook is there a vb code that can do this i have tryed a few but they all come back object not found.
View 6 Replies View RelatedI have previously tried to save the whole original workbook with the following
ActiveWorkbook.SaveAs Filename:="C:UsersPhilDocumentsMy Excel DocumentsRostersIndividual Rosters" & StaffLastName & " " & StaffFirstName & " - Roster commencing " & Format(DateSerial(Range("X11").Value, Range("V11").Value, Range("T11").Value), "dd mmm yyyy") & ".xlsm
but receive a run-time error message:
Run-time error '1004': Microsoft Office Excel cannot access the file
My first request for help is how is the code wrong and how can it be corrected?
Secondly, instead of saving the whole workbook, is it possible to save just a given range of cells as a new workbook, using the format above to give it a personalised filename?
I've got a spreadsheet listing applications on PCs (assets), one line per application, and need to import that info into another document for each PC and save it with a file name which = the PC name (asset number). I've attached a couple of sample files to try to explain what i mean.For asset number PC037014 I need the corresponding fields filled and the list of applications pasted under where is says Additional Software List (inserting more rows if there are too many applications to fit in). Once the information has been populated, i'd like the file to be saved in a specific folder then move onto the next asset number in the list.
View 13 Replies View Relatedam making Excel for private use, i need the following makro to be active, here is the idea because i didnt yet work with makros at all:
If A1 = False
then Range B1:D1 will be locked cells
If A1 = True
then Range B1:D1 will be unlocked cells
Those rules apply to 1 day in the year.
the range A1:D1 will be copied about 400 so the makro should be active to each day separatly.
Check the picture attached for example of one day
I have been trying to create a Macro that can do the following.
Open a Excel Workbook, Copy the Worksheets that I need (ignoring ones that are not needed.), Save them in the correct location with a new folder of date and time and saving them as csv files.
I have all the elements but can't seem to get them to gel correctly.
Just wondering if anyone has anything like this that I could adapt or edit.
I'm currently using this macro:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng
Dim MyCell
Set Rng = Range("A1:AA7672")
For Each MyCell In Rng
[Code] ....
The problem is that it is not dynamic and it's super slow. The larger my range is the slower it operates and right now it's almost unusable.
The workbook I am using gets touched by 10 different people and I want to prevent tampering. I want for the workbook to be, when protected, have all the blank cells editable and once a value is inserted, it cannot be edited again and not work so unbelievably slow.
I have a macro in Module1 of workbook "BAU" called "ExtractToSheets" which exports data depending on the contents of a cell (Thanks royUK et al)
At the moment however, you must run the macro manually to export the data.
Now, i have put some simple validation in column K of worksheet "BAU Data" within the "BAU" workbook. Whenever a cell within a predefined range with column K is changed to "Yes" i want the macro to run AND i want the row the cell is in to be locked so it cannot be edited further by the user. I have had too many users mess up previous sheets i have made to collect my data, and i wish to make this one somewhat more idiot-proof!
Any pearls of wisdom folks?
I would have attached a representation of my sheet, unfortunately because there is now a macro in my spreadsheet, our trigger-happy security software won't let me post it!
I have a search form that will filter the result based on the requested category. Is it possible to lock the cells once it has been filtered and then remove the lock after they close the workbook?
View 2 Replies View RelatedI need a macro which will lock the cells after data is entered in the cell once. Cell should become non editable and should be only edited after a password is provided.
View 4 Replies View RelatedI have an excel sheet which is protected (mainly so that the users will not change any formulas) the excel sheet is basically a list
Just to put you in the picture
Column B Is PO Number
Column C Is Remarks
Column D Date
Column E Description
Column F Supplier
Column G Amount
I would like that when Column C changes to "OK Checked" column B C D E F G are protected and locked. But obviously for that particular row.
For example the input of Cell C15 changes to "OK Checked" then B15, C15,E15,F15,G15.
the macro must NOT be fixed to a particular row (in this case 15)
I have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.
View 2 Replies View RelatedI have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.
What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?
Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.
What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.
What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.
Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?
PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.
I am new to VBA but I am trying to insert text and lock all duplicate cells except for the first one. Below is the macro I am trying to make. I keep getting an error on the line:
If(COUNTIF($A$9:$A$76,A9)=1,FALSE,NOT(COUNTIF($A$9:A9,A9)=1))
This is my macro:
Sub SetVoters()
'
' SetVoters Macro
'
'
Sub SetVoters()
'
' SetVoters Macro
'
'
ActiveSheet.Unprotect
[Code] ......
I have a spreadsheet that allows users to paste set data from a PDF Image (using OCR) straight into Excel and then use the MID function to split the data accordingly.
Unfortunately, the OCR isn’t too intuitive and gets it wrong sometimes.
So to counter this, in another sheet (in the same workbook) I have a manual input section, and a simple macro button that pastes this data into the same fields where the OCR text would be, so that the main sheet works exactly the same way as before.
The problem is, and most likely due to the simplicity of the sheet, if a combination of OCR pasting and manual inputting is used, when I hit the paste button, it over rides the OCR data with blank cells
In the link below I have shown what is currently happening (1, 2, 3), and an example of what I would actually like it to do (4, 5, 6).
Example - Online Spreadsheets - EditGrid
So, in the 2nd scenario, I would like “5” to recognise that the respective cells in “4” already contain data and fill them ‘Grey’. This I have already achieved with basic conditional formatting.
However, I need to take it 1 step further and say that if the parent sheets cell (Auto OCR) contains data, as well as filling cells (in sheet Manual) lock these cells off and prevent the end user from adding data and/ or being copied over to the parent sheet.
Is this possible?
The result then being the parent sheet with both OCR text and copied text from the manual input sheet.
Both sheets are protected anyway and only allow for user input in certain areas, so is it even possible to apply further protection once the sheet is locked already?
I have excel sheet with filtered columns. I will do the filtering manually and after selecting a filter, I want the visible data on the sheet to be saved as PDF file.
Please note that I dont want any range to be saved as PDF but visible data because as the filter changes, so as the data will be either less or more.
Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies View RelatedI'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