Locking Specific Cells That Are Hidden / Shown Based On VBA?
Jan 13, 2014
I am having trouble with my VBA code, it hides groups of rows (projects) based on the contents of corresponding cells above. these have either 'yes' or 'no' in them (indicating if the person is active in that project). The Yes/No is formula based from another sheet.
what I am trying to do, is have particular cells, withing the projects rows, locked (as they are also populated by formulas) but have other cells open for editing.
when I try and do this i get an error with my VBA, i am assuming that as cells are locked, the VBA is prohibited form hiding inactive projects/rows. i tried an addition at the bottom but this hasn't worked
Code:
Sub Worksheet_Calculate()
Dim i As Long, StartRow As Long, EndRow As Long
StartRow = 13
[Code]....
View 1 Replies
ADVERTISEMENT
May 17, 2009
I want to have a few words shown based on figures in specific cells. I not quite sure how to explain, but i will try my best:
A1 = 5
A2 = 0
A3 = 7
The function i am using looks something like this:-
=IF(A1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
However, by using this function, if the cell is empty it still shows the first wording when i need it to show lets say between 1 & 6 shows the first wording and more than 6 shows the second wording! If you know what i mean... I know what i want to do but cant really explain it very well.
Maybe it should look something like this but it does not work the way i am doing it.
=IF(E156>1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
View 5 Replies
View Related
May 14, 2009
I'm creating a spreadsheet for work, which for security reasons requires certain information to be locked except to administrators. However the cells need to be open for inputing information for everyone until the administrator enters specific data.
Completed Date Time W/R No. Acc. No. Priority? Vunerable? Comments Closed Date/Time Yes 14/04/09 11:30:00 1234567890 8890976 Yes Yes N/A 14/04/2009 11:30
What I am looking to happen is once an adminstrator selects 'Yes' from the drop down menu on the Completed column, for that row of data to be locked for editing, unless unlocked later by an adminstrator.
View 9 Replies
View Related
Jan 4, 2008
I have a workbook with several sheets, but just on the sheet called “sales” I need a macro to do the following:
If F4 = CST
Then Lock down H4-S4 and AM4-AZ4
But if F4 is changed to something else I need H4-S4 and AM4-AZ4 to be unlocked
Is a password necessary for this? It is ou
I just want it to lock and unlock, not protect and unprotect.
View 10 Replies
View Related
Jun 28, 2006
I have a query in relation to locking cells to disallow editing based on a dynamic range. The VBA code I have thus far is this :
Sub PasteForecastWO()
rowarr = Array(10, 26, 28, 69, 72, 79, 81, 87, 89, 104, 106, 114, 116, 122)
For Each ce In Range("S8:AD8")
If ce = "ACTUAL" Then
For i = LBound(rowarr) To UBound(rowarr) Step 2
Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value = Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value
Next i
End If
Next ce
End Sub
What I would like to do is lock the range that applies as the variable "ACTUAL" moves or changes along the range S8:AD8. I have attached the relevant sheet so that you may get a clearer picture.
View 7 Replies
View Related
Apr 3, 2009
I am trying to figure out how to do the coding for a command button in a userform I've created. It has 5 textbox fields. I want the command button when clicked to transfer the data in those 5 fields to 5 specific cells in a hidden sheet. Then I also want that button to launch a word document.
Anyone know how I can go about this or where I can go that explains the specific coding?
I've tried searching with mixed confusing results & read through http://www.contextures.com/xlUserForm01.html#Top & http://www.theofficeexperts.com/down...ExcelDownloads examples.
View 7 Replies
View Related
Mar 4, 2014
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
Attachment 301726
View 4 Replies
View Related
Sep 23, 2013
Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.
View 5 Replies
View Related
Jul 21, 2009
=IF((P3<$Q$1),E3-S3,IF((G3="B"),"Not Changed",IF((G3="MT"),"Not Changed","Not Changed")))
Above is the formula I'm trying to get to work but it's not-a-happenin. It seems like the first IF overides all of the others. Basically I want the first IF to be ignored if column G contains any of the words shown. So basically what I think is happening is that P3 is less than Q1 so it's ignoring the rest of the IFs.
Originally my formula worked fine as:
=IF(P4<$Q$1,E4-S4,"Not Changed")
But I realized that i needed a certain category of product ignored. Products are labeled in column G.
View 6 Replies
View Related
Apr 17, 2014
I have a lookup that gives and RGB colour code in Cell A1; for example 186, 206, 140.
I'd then like Cells R10:V15 to fill with the RGB colour based on the result in A1.
View 2 Replies
View Related
May 7, 2014
When cross linking cells from one file to another, I sometimes lose the source link to rearranging cells on one of the other spreadsheets. Is there a way I can "lock" the source cell so that it isn't lost as a reference?
View 1 Replies
View Related
Feb 28, 2014
I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.
View 7 Replies
View Related
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
Feb 12, 2014
Need to autohide cells in sheet 2 and 3 based on sheet 1 cells that are hidden. (Example if i hide 150 switchgear on SOV tab it will hide 150 switchgear in Customer and Foreman sheets as well).
View 1 Replies
View Related
Sep 11, 2009
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc.
I added a new cell called Track Back on the employee search sheet.
What I want to do is only show variances for the amount of days back selected in the Track Back cell.
For example if I select the last 30 days, only the last 30 days would show up below in the sheet.
I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View 14 Replies
View Related
Apr 9, 2009
Basically a cell might have a number with 3 decimal places
For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.
Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.
I included an example of what i mean in this excel file which should clear up any confusion.
View 2 Replies
View Related
May 23, 2014
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
View 2 Replies
View Related
Mar 5, 2013
Locking text in cells but not the ability to change colour of cells
******** width="234" height="60" frameborder="0" marginwidth="0"
marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" id="aswift_0" name="aswift_0" style="left: 0px; position: absolute; top: 0px;">*********>
I have a spreadsheet where I can change the colour of a cell by clicking the mouse, I also have text in many of the cells.
What I need to do is protect (lock) the text so that no one can change the text in any of the cells, but I still want to be able to change the colour of the cells by clicking the mouse in that cell.
View 2 Replies
View Related
Jun 2, 2009
How can I make excel sum up a colum, and ignore cells shown as #I/T?
This error sign might not be the same in the english version of excel.
If I try to sum up cells with the error value, the feedback will also be #I/T.
Faulty formula is now; =(SUM(B307:B316))
View 4 Replies
View Related
Feb 12, 2007
I want to Freeze my Header Row (A16 to T16) on my spreadsheet but I can't seem to get it to freeze. I wanted to have it freeze when that row reached the Top of the page but all I can do is freeze it and the 15 rows above it. Can one row be made to be visible at all times? I don't think so as I asked this question before but thought I would start by asking it again...
Now my workaround for that was to put the header row at the top of the page as well and freeze it. This works but when you initially open the spreadsheet it look weird because you see a header row on top and one 15 rows below it.
My question is is there a way to hide row 1 until a specific cell becomes active and then the code can undide the row? I want row one to become visible when the user gets to cell A41. Does anyone know how I can accomplish this or do you have any other suggestions for me to try?
View 9 Replies
View Related
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
Jan 29, 2007
I have copied from another post (here I believe) and modified for my situtation. I simply want a range of cells K13:U13 to stay locked unless there is an X in H13. This will repeat on down the spreadsheet, ie. K14:U14 will stay locked unless there is a X in H14. Using the script I found it seems to be working unless you go back and change one of the X's to a blank. The range stays unlocked.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strName As String
Dim strRange As String
strName = Me.Range("H13")
If strName = "" Then
Me.Cells.Locked = True
Me.Range("H13").Locked = False
End If
strName = Me.Range("H14")
If strName = "" Then
Me.Cells.Locked = True
Me.Range("H14").Locked = False
End If..................................
View 6 Replies
View Related
Jan 19, 2003
find if there is a way to unhide in one step a specific column from hidden range of columns...
Assume columns D..H are hidden, now how can I unhide column G alone or column F alone and so on.
View 6 Replies
View Related
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
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
Jul 16, 2013
Is it possible for 2 different persons to lock different cells on the same worksheet?
View 3 Replies
View Related
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
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
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
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
