How To Make Macro To Lock Certain Cells Connected To Another Cells Value Then Copy Daily

Mar 29, 2014

am 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

View 1 Replies


Daily Worksheet Macro - Make Copy Of Template For Each Day Of Month?

Dec 22, 2011

We have a workbook that we create each month that has one worksheet per day of the month, labeled 12_01_2011, 12_02_2011, 12_13_2011...etc. The pages are an empty template with formulas and fields in place that we simply copy and paste the results of an SQL query into. Presently, we are copying the page manually several times over, and then manually renaming the pages with the new dates for the upcoming month.

So, here is my question. Macro that I might use that would:

1) Make a copy of the template for each day of the month.

2) Label each page in sequence with the dates for the upcoming month.

View 4 Replies View Related

Auto Copying Cells Value That Changes Daily Without Affecting Daily Value?

Mar 20, 2014

I have the following scenario:

Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.

So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.

I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.

View 4 Replies View Related

Dynamic Macro To Lock Cells That Contain Any Value

Feb 10, 2014

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.

View 5 Replies View Related

Automate Macro And Lock Cells

Oct 6, 2008

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!

View 14 Replies View Related

Macro Filter And Then Lock Cells

Oct 18, 2012

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 Related

Macro To Lock Cells After Data Entered

Mar 22, 2007

I 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 Related

Macro To Lock All Non-Blank Cells After Saving Workbook

Jul 23, 2014

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".

View 4 Replies View Related

Macro To Lock A Number Cells After Changing A Cell

Jan 5, 2009

I 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)

View 9 Replies View Related

Lock Cells Automatically After Entering Via Macro Code

Apr 23, 2008

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 Related

Lock A Sheet But Still Make Some Edits/run Macro

Oct 18, 2009

Some of the sheets in my spreadsheet contain some data and graphs with a few macros that are run when a button is pressed. At the bottom I have some comments with a macro that is run to timestamp when these comments are updated or edited.

What I would like to do is lock the entire sheet so that the data and graphs can't be tampered with, apart from the comments part at the bottom because I'd like to be able to have the comments editable. So far I have managed to do that, but when I edit a comment and the timestamp code is run it creates a run-time error 1004. I could just remove the timestamp code I guess but I'd like to keep it there... figuring that there must be a solution.

How can I work around this problem ? Either by being able to keep the sheet locked and still have the timestamp code run, or have it temporarily unlock the sheet - run the code - and then re-lock the sheet.

View 10 Replies View Related

Macro To Lock Cells In Protected Sheet Depending On Criteria

Dec 13, 2007

I 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.

View 14 Replies View Related

Conditional Macro - Insert Text And Lock All Duplicate Cells Except For First One

Sep 16, 2012

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:


This is my macro:

Sub SetVoters()
' SetVoters Macro

Sub SetVoters()
' SetVoters Macro


[Code] ......

View 1 Replies View Related

Lock Cells To Prevent Predefined Range Macro Copying

Oct 30, 2012

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?

View 1 Replies View Related

Lock Cells Based On Value Of Other Cells - Code Simplification Query

Apr 22, 2009

I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.

It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).

Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?

View 7 Replies View Related

Lock Empty Cells In Range Based On Other Cells Content

Apr 18, 2008

I am trying to lock the unused cells in 32, 2 column by 7 row named ranges, based on whether or not two cells, above each range are equal or less than each other. In other words while one of the cells is less than or equal to the second cell all cells in the range below should be unlocked, as soon as that condition is no longer true the blank cells need to be locked.

I am trying to use this in the Workbook_Sheetcalculate so that the macro will run automatically.

View 3 Replies View Related

How To Make A Macro To Format All Cells

Jun 5, 2014

I would like to make a macro to format all my cells.

I need it to format my last digit to superscrift IF there is 4 digit after decimal point.

How to do this?

View 14 Replies View Related

Make Cell Result Copy Itself Into Subsequent Cells X Number Of Times?

May 9, 2013

Say I have (cell A1) 10 salespeople and they each sell a (A2) $100/month membership, so I have (A3) $1,000 monthly revenue.

(A4) is an assumption about how many months the average person will stay a member.

So if A4 = 6, (6 month membership assumption) how would I get Excel to output $1,000 (the result from A3) into cells B3 through B8

but then for instance if A4 = 3 (3 month membership assumption) it would only output the the $1,000 result from A3 into cells B3 through B5

So like A1 through A4 of...


would output $1,000 into B3 through B8



would output $1,000 only to B3 to B5

View 1 Replies View Related

Macro To Make Copying Cells Automate

May 3, 2014

I've got a problem with copying cells from table A to B.

A short insight of the situation:

Table A shows: ITEM_1 = 3, consequently ITEM_1 from table A is copied to table B for 3 times.

I've got a few huge information blocks to handle (up to 5000 items) obviously it's unreasonable to do this manually.

I've added an xlsx file with more detailed description.


View 8 Replies View Related

Macro Seems To Make Entire Text String In Cells Bold

Aug 1, 2014

I have the following code:

[Code] ..........

However, whenever I run the code, if there's even on word that is bold in the cell, then the entire text string in the cell turns bold. How can I stop this from happening? i want to keep the format of the text string the same, only remove and replace the items listed in the code.

View 4 Replies View Related

Lock Cells Based On Other Cells

Aug 29, 2007

I have created a spreadsheet which I am trying to set up to allow users to select only 1 cell in a range and lock the rest. Each cell D5 to I5 has an assigned number 0 to 5. What I am trying to achieve is when a user clicks on a cell, the value of that cell is displayed and the remaining cells are locked. e.g. if the user selects G6, 3 is displayed and cells D6,E6,F6 and I6 are locked. The code which I am using is giving strange and unpredictable results.

Private Sub Worksheet_Change(ByVal Target As Range)
If [D5] = "0" Then
ActiveSheet.Unprotect ("PASSWORD")
[I5].Locked = True
ActiveSheet.Protect ("PASSWORD")
'Remove locked property if D5's value is anything else or is deleted.
ActiveSheet.Unprotect ("PASSWORD")
[I5].Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If............................

View 2 Replies View Related

Macro To Copy Daily Data Into New Row

Jun 24, 2014

I am trying to create a macro that would copy three cell values - Date, Amount, Rate (from Sheet1) and paste them into Sheet2 as values.

In addition, this macro will be run for each business day's file, which only has the 3 values only as of that date; I want Sheet2 to be updated on a daily basis with historical data from past days (when the macro was run) and pasted as values.

For example, if i run the macro today, it should copy Date, Amount & Rate from Sheet1 ('Data' tab in sample.xls) and paste them as values in the next row after yesterday's data in Sheet2 ('Historic data' tab in sample.xls).

Sample attached; what is the best way to do this?

View 3 Replies View Related

Make Now () = 06/29/2009 (make To Cells With Dates Equal Each Other)

Jun 29, 2009

I need to figure a way to make to cells with dates equal each other if the
day,month and year are the same but are placed into a cell at different times during the day. "Making Date Now () = (06/29/09) In another cell". Therefore, A1= Now() and E11 = 06/29/09

View 2 Replies View Related

How To Make All Empty Cells As A Blank Cells

Sep 26, 2012


View 1 Replies View Related

How To Make Row Of Cells Equal A Column Of Cells

Feb 14, 2013

Say I have data in one tab that is cells

A9, B9, C9, D9, etc.

On the next tab, I want the cell in D5 to equal A9, D6 to equal B9, D7 to C9, etc.

I can't seem to lock the cell correctly to do this. If I use =A$9 in cell D5 and then copy down in cell D6 I'll get A$9 also, but if I use $A9, in D6 I'll get $A10.

View 10 Replies View Related

Lock Certain Cells By Vba

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

Make Sheet To Track Daily Changes

Jan 13, 2014

I am making a sheet to track the daily cycles of a machine. I need to get the information from the "meter reading" column to populate in the " cycles recorded" cell. I'm very new to this and need a formula to use to track the changes in column D on the attached sheet that will populate changes in cell D4.

Cycles counts.xlsx

View 4 Replies View Related

Lock Cells After Done With Formula?

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

Lock Dragging Of Cells

Oct 22, 2008

Is there a way to protect a sheet from a user dragging a cell?

View 6 Replies View Related

Lock Cells When Saving?

Aug 1, 2014

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"


View 2 Replies View Related

Copyrights 2005-15, All rights reserved