Code Locking Up

Apr 13, 2008

I'm trying to run the below code, but every time I do, it locks up excel. The code is running on 900 rows of data within the "loop to delete rows" section and in the main for loop there are 49 values to evaluate for the string strSA. how I can improve this code? When I step through it with F5 it works fine.

View 14 Replies


ADVERTISEMENT

Code For Locking A Row Of Headings

Nov 20, 2009

My questions is how to "freeze pane" a row of headings on the Results page and post the results onto row 2 and downward *without* deleted row 1.

If you look at the attached file, you will see a tab that I created called "My desired Results Page" that simply has a frozen row of cells. Instead of just displaying the rows from the Details page on the Results page, I'd like them there but with the headings at the top.

Is there code that can do this?

View 5 Replies View Related

Locking Cells Through Code

Feb 16, 2007

1. When I put data into one cell I need three other cells to be locked and not allow data to be inserted unless the data from the previous cell is deleted.

2. I have attached the Excel sheet for better understanding.

View 2 Replies View Related

Locking Different Cell Ranges Then Locking Whole Sheet With Macros

Jan 4, 2010

I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.

The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.

What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.

I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.

I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....

If anyone has any suggestions I would be eternaly greatful.

Passwords on the sheet for the different buttons are abc or cobra.

You can download my Spreadsheet here.

[url]

View 9 Replies View Related

VBA Code For Locking Cell Disabling Dropdown Validation Lists

Mar 28, 2012

I am currently working on spreadsheet which is using a mix of Conditional Formatting, Validation and a bit of VBA code.

The spreadsheet is a log of vacancies and applicants, When "vacancy" is selected in A I have introduced some VBA code to lock down cells S:AT on the same row.

The code is working but the issue is it is stopping you using the drop down validation throughout the sheet, if you click the arrow that appears on the cell nothing happens, However you can still access the dropdown by right clicking and choosing the 'Pick from Drop-Down List' Menu Item.

The validation is set up by ranges from another Worksheet.

The Code I am using is:

Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
Dim myrow
Dim StarLock, EnLock

[Code].....

View 2 Replies View Related

Locking One Cell If Another Had A Value In It (conditional Locking)

Oct 10, 2008

I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.

View 9 Replies View Related

Locking Column A Only

Dec 10, 2008

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 1) = Now
End If
Application.EnableEvents = True
End Sub

everytime i lock the column a by using the protect sheet, the vba does not work or debugs. is there a way for me to lock the cell in column a when data is inputted or changed in column b? i am using date and time for column a

View 4 Replies View Related

Locking Timestamp

Mar 27, 2009

=IF(D2<>"",IF(A2="",NOW(),A2),"")

Whenever I type into field D2 it populates current date and time in field A2 and down the A column as I write into current D columns. The next step I would like to do in which he was able to figure out so far is how to lock in the date and time after something has been typed into D2. If I make any changes to the D2 field it updates the date and time, which I dont want to happen.

View 3 Replies View Related

Locking Workbook

Mar 27, 2008

I have a user that is trying to lock certian cells within a spreadsheet but to allow certian users still to be able to edit these cells.

When this users is trying to do this and locks the cells it is locking for all users and not applying the "allowed" list of users to make changes.

View 9 Replies View Related

Locking Worksheets ...

Apr 1, 2008

is it possible to lock a worksheet. For example, i have a single worksheet in a workbook that i do not want people viewing. Is there any way that a password would have to be used to gain access to it? I have to keep the sheet inside this workbook because information is pulling from other sheets inside the workbook.

View 9 Replies View Related

Locking Cells

Oct 6, 2008

I did review some examples your members have already provided however I have a slight twist.

I have multiple users adding to a spreadsheet from which data is later copied to a master sheet. I need to lock ONLY cells which contain data when user chooses to save spreadsheet or saves speadsheet on exit. If the cells do not contain data, they should remain unlocked.

View 9 Replies View Related

Locking A Graph

May 25, 2006

how to lock a graph in an unprotected worksheet?

View 7 Replies View Related

Locking The Top Rows

Jul 28, 2006

top three or four rows so that scroilling down the screen leaves the title rows where they are .

I know that this is possible and have done it several times in the past, age however, dulls the memory and I have just forgot the procedure,

View 3 Replies View Related

Locking Numbers

Nov 9, 2006

my spreatsheet calculations work, but the overall thing is too large to print; i am running many numbers and formulas. I made them all finally refer to a small 'summary' box which i created...but when i try to transfer the box to a new worksheet so that i can print it and only it; it automatically has no references for the formulas on the new (blank) sheet, and comes up all errors. to the point, how do i lock the way a box appears (when it contains formulas) so that it will remain sensible after it is transferred elsewhere?

View 3 Replies View Related

Locking Cell If Value

Mar 29, 2007

I am trying to unlock a cell if another cell is a certain Value and if it is not that Value to lock again i have got as far as unlocking it but i can not get it to lock if the value is faulse. This is what i have so far!!

Private Sub Worksheet_Change1()
If ("K4") = "Daily" Then
ActiveSheet.Unprotect ("Password")
Range("F6").Locked = False
ActiveSheet.Protect ("Password")
End If
If ("K4") = "Monthly" Then
ActiveSheet.Unprotect ("Password")
Selection.Locked = True
ActiveSheet.Protect ("Password")
End If
End Sub

View 2 Replies View Related

Conditional Cell Locking?

Mar 8, 2013

Is it possible to have a formula in a cell, based on the value in another cell, but if the other cell value is not true then allow for data entry in the cell

So
J10 executes a formula if the value in I10 (a list field) is 'Cash and Shares' =IF(I10="Cash & Share",ROUND(IF(AND(K10<>0,O10>0),K10/O10,0),0)).

However if the value of I10 is anything other than 'Cash and Shares' then do not execute the formula in J10 but allow users to enter another value.

Note: the cell needs to be locked. The worksheet will be protected and will allow users to Select Locked Cells and Unlocked Cells.

View 3 Replies View Related

Locking A Certain Text Boxes, And Not Others

Feb 4, 2009

For example:

Locking a certain text boxes, and not others. Under review, protect sheet, the Edit Objects function is all or nothing

View 2 Replies View Related

Locking Rows Together When Sorting

Apr 30, 2009

I need to lock consecutive rows together before sorting. e.g. A1 contains a name and A2 is blank: B1 contains data related to name in A1 - so does B2, and so on. Therefore need to lock rows 1 and 2 together, then 3 and 4 together and so on, but sorting on the data in the first cell of the group e.g. A1

View 3 Replies View Related

Conditional Locking Of Cells

Jul 29, 2009

Lock cells based on conditions? For example, in my spreadsheet I need my cell J42 to stay locked at all times unless my cell C12 reads "Klongtoey".

View 10 Replies View Related

Locking Cells On Certain Columns

Jan 24, 2013

WILL IT BE possible to lock columns / cells for any user to avoid editing? Only the creator of the file should be able to edit?

View 2 Replies View Related

Two Different Persons Locking Cells?

Jul 16, 2013

Is it possible for 2 different persons to lock different cells on the same worksheet?

View 3 Replies View Related

Automatic Locking Down Cells After Use?

Oct 21, 2013

I am designing a car park rota on a spredsheet.Each person has a limited number of spaces. Is it possible to automatically stop people going in and deleting out entries from previous weeks (to stop any cheating!) or would I have to manually go in at the end of each week and protect.

View 7 Replies View Related

Conditional Locking Of Cells- More Than One Sub?

Aug 19, 2009

I am trying to create a form that has multiple drop downs, which are dependent on answers from other drop-downs. I'm trying to make it super user friendly and have the cells lock, so that the user can tab through only the items that they need.

I have it almost down, but I'm not sure how to do it for multiple cells. The closest I come is one big long if,else statement which runs through the whole thing on every cell change.

I have an example of the spreadsheet attached.
If F1 says "Other", I want H1 to be unlocked
If F2 says "Y", I want F3 and F4 to be unlocked
IF F4 says "Other", I want H4 to be unlocked

View 2 Replies View Related

Locking Cells Out With Macro

Sep 23, 2009

I'm trying to set up a macro that locks or unlocks a column depending on what is put in the column before it.

at the moment i've got the code written in the worksheet

View 2 Replies View Related

Locking Worksheets With A Macro

Dec 8, 2009

I found the following code which allows you to lock sheets 1 to 3 with the ActiveX control buttons located on Sheet4.

View 8 Replies View Related

Locking All Cells In A Workbook

Feb 4, 2010

I have been having problems protecting my worksheets, using the normal manner, mainly it does not seem to work, So i came up with another idea, but cant seem to work out how to do it.

I have a workbook with about 7 worksheets, this needs to be sent via email to our customers, and on two of the worksheets the customer can enter there comments and then email back the workbook, the problem is that i cant protect the data.

So i was thinking is it possible to use VBA to program a function i can enter in to the toolbar that the user can lock all the cells in the whole workbox except for two columns when they finish entering in data, maybe with a password for locking it and unlocking for the original user so that they can unlock the cells for editing.

View 14 Replies View Related

Conditionally Locking Cells

Feb 23, 2008

I need to lock a cell (F21) if J26 is less than 100. Can't work out how in conditional formatting. Is there a different way?

This is what I need to do:

F21=(IF,J26<100,Locked,Unlocked)

View 10 Replies View Related

Locking Hyperlink To A Cell?

Mar 30, 2012

I am working in a workbook that uses hyper-links to navigate between locations on various sheets.

When rows are added to a sheet above the destination cell, the hyper-link no longer points to the original data but has moves the corresponding number of rows that have been added.

Is there anyway the hyper-link can be locked to a cell and adjust automatically if new rows are added or existing rows deleted?

View 3 Replies View Related

Locking Sheet Tabs

Nov 28, 2006

Is there a way I can lock the sheet tabs so they cannot be viewed or turned on by anyone except me?

View 9 Replies View Related

Locking Cells That Contain Formulas

Jan 31, 2007

I currently have my template protected, and I've been getting complaints that they can't modify the width, fonts etc. The only reason I have it protected is because I don't want them to mess with my formulas. How can i protect ONLY the formulas, and have the rest editable.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved