I need to protect my worksheet and only allow users to edit certain ranges. I am allowing the use of autofilter which I can select when I apply protection. However, I am now aware I cannot sort protected cells..which is essential. I basically need to be able to sort a column titled 'surname'. The worksheet is a record of pupils attainment in my class.
Now, I have a macro which seems to do the job: ....
I want to print a worksheet where their are blank rows for future use between the main body of the data and the total row. I want to exclude the blank rows.
View 11 Replies View RelatedI'm not fond with macros, but this is what i need help with.
I need two macros one to sort ascending (Ctrl+A), and one to sort descending (Ctrl+D).
This is what the macro to do. Unprotect, sort selected column, protect.
I have a column containing 365 entries, one for each day of the year. This I intend to send out to Army bands, to have them fill in their forecast of engagements. I do not want them to be able to amend the original diary, but to scroll to the bottom of the list, add a new entry with the applicable date and then be able to 'sort' back to chronological order. This would then allow a separate entry for every engagement to allow me to interrogate the spreadsheet. As I have protected the sheet, the 'sort' facility does not function even though when protecting I ticked the box to allow users to 'sort'.
View 2 Replies View Relatedconditional formatting. I am using the code below to conditional format every Odd row with a background/border.
=MOD(ROW(),2)=1
I would like to know how to modify this, so that it only applies the format if the cell in column A on that specific row contain data and doesn't format blank cells.
I am currently working on a football spreadsheet ranking players based on their fitness performance scores. For example, there are 14 persons on Offensive Line. I need all their test scores ranked out of 14. However, if there is a blank, I still need the test scores to be ranked out of 14.
As of right now, I have cheated, and inputted a random number to bypass the blank:
=IF($G12>10, 0,RANK($G12, $G$12:$G$25,0))
But, this now affects my average of the fitness testing scores for that position. This could be avoided if I could find a way around my first problem.
Is there any way to allow autofiltering and sorting even with locked cells within the range to be sorted? I've conditionally locked some cells based on user input, and at the end of my vb code I have allowsorting and allowfiltering. But I want to sort cells that are locked even after that. Does anyone know if that's even possible with VB?
View 4 Replies View RelatedI have a workbook with a series of sheets that have tables for entering data. The table row and column labels and formulas in certain cells of each sheet are locked and some sheets are hidden.
When opening the file I want to clear all cell contents (interior color, comments, data, etc) in all the unlocked cells on each sheet that is not hidden.
I tried protecting the sheets first so only the unlocked cells would be accessible, but when I run the code below I get an error saying that the clear contents etc. cannot be performed because the sheet is protected. If it is not protected, everything gets wiped out.
How can I keep the locked stuff but clear the unlocked cells?
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
ws.Protect Password:="aaa" 'Protect each sheet
ws.Cells.ClearContents 'clear content of any unprotected cells
ws.Cells.ClearComments 'clear any cell comments
ws.Cells.Interior.ColorIndex = 0 'set background colour to no fill
Active.Cells.Range ("a1") 'make the active cell the top left
End If
Next ws
I know that there is a lot on locking cells but it is confusing to me. I am able to lock a worksheet but that is not what I need. I have an Excel document that has thirty to thiry-six names on it. I do not want anyone able to edit the names. But I do want them to be able to add a name.
Example:
There are 30 students in the classroom. A new student comes in. I want the teacher to be able to add that student to the roster but not able to edit the names above. What do I need to do?
Below is the code that I copied from another thread. But what I need is to enable this command when the sheet is protected.
View 9 Replies View RelatedWhen choosing ascending or decending, is it possible to always keep rows with the designated blank cells at the bottom? Trying to prevent the blank cells from popping on the top when i filter by a certain field.
View 4 Replies View RelatedI have been trying to sort the cells in a worksheet, but am unable to properly select the range of cells I need
For Each ws In Worksheets
If ws.Name <> "MySheet" Then Goto nxt
If ws.Name = "MySheet" Then Worksheets("MySheet").Activate
Range("A1:F" & totval).Select
Selection.Sort Key1:=Range("F1"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
nxt:
Next ws
the code mentioned above throws an error when it gets to the range.select line. I cannot simply say usedrange.select.
It keeps throwing error 1004.
Please ignore the "& totval" in above code. That was my attempt at specifying the last row number, which i specified in the totval variable
I dont know how exactly to specify the range of cells containing data in the activated worksheet above, and then select them for sorting.
I have a sheet protected and filters enabled. I use this code to enable sort and filters:
[Code] ........
Nevetherless, when I try to sort, I receive this message in excel: "The cell or chart you re trying to change is on a protected sheet.
To make changes, click Unprotect Sheet in the Review tab (you might need a password)."
What settings should I use in order to allow a user to sort data in a table when some of the cells are locked?
When I protected the sheet I ticked the option to allow user to sort data but it doesn't work...
Is it possible to keep the sort icon available on a worksheet which is protected? I have issued a spreadsheet to colleagues which contains formulas so I have protected it, but I have now been informed that they need to be able to sort the data according to a ref number.
I thought of using code (which I'm not very good at) and used some from another excel document, but couldn't get it to work...the code was ....
I have data in some of the cells within range A26:A39
These cells are populated via an IF function on another worksheet. Even though the cells appear blank (as in the value returned is ""), there is a formula in these cells. I think it's called formula blank?
I am looking for a way to copy the data from the cells within the range which are not blank (ie: not = "") and paste this data elsewhere on the sheet in a list with no blank spaces in between.
I anticipate that there will be 4 non blank cells within this range.
Ideally I would have data from the nonblank cells copied and pasted to cells
A40
A41
A42
A43
how to exclude a blanck cell from logic?
I have tried what you see below but neither works as desired.
If Not vData(n, 9) = 0 Then
If Not vData(n, 9)Is null Then
have corrected code error
When I either manually check the box for allowing sorting and autofiltering, or I do it through vba with:
Code:
With Sheets("Inventory")
.Protect Password:="###", AllowSorting:=True, AllowFiltering:=True, AllowUserInterface:=True
.EnableAutoFilter = True
End With
I still am unable to use the icons. The icons are clickable to filter or sort, but every time they say that the cells I am selecting are protected. I want them to be protected from editing the data, but want to allow them to be sorted or filtered.
Is there a way I can sort multiple hidden protected worksheets with a password “Protect”
I have 12 worksheets “Jan”, “Feb”. Etc
“The Range on each worksheet is the same ("A11:CR200") ....
If I protect a sheet but tick the boxes to allow sorting and autofilter I still get an erro when I try to use the sort buttons on the autofilter.
It allows me to use the filters but as soon as I sort I get told to unprotect the cells im trying to sort.
Is there a way around this where I can keep formulas from being touched but allow sorting of data through autofilter?
One of my work colleagues need to add a worksheet to an existing Excel Spreadsheet which has VBA behind it, but the worksheet is password protected. The developer who wrote the application has now left, so we have no idea what the password is. Is there anyway of getting round it, like cracking into the spreadsheet to find out the password, or another way?
View 2 Replies View RelatedAlthough Copy + Paste and Copy to New Row are fairly common questions I find, I am having trouble getting it to fit to what I want.
In the attached example, I have three sheets: 'TBC', 'Bland Anna' and 'Coates Beth'.
I would like to search in columns E and F (no dates will be present until row 5, but as rows 1 through 4 will not contain dates, it may be easier/involve less code to just search the columns) for a specific month, e.g. for this example search for any dates that are in January.
I would then like to copy the data in columns D through G that correlate to said date, and then paste onto sheet TBC on the next available row, not to overwrite.
The macro in question would need to go through every sheet and perform the above action, although there are only 2 sheets in this example, the live workbook will likely contain 100+ sheets containing employee sick leave.
Furthermore, and this is something I'm not sure would be feasible, everytime it copies data, I need it to pull across the name of the individual. This part is slightly more complicated as the names on the sick leave sheets are based at E2 and G2, whereas it needs to copy the names to B and C. Moving the name information from E2 and G2 would make sense to make copying easier, there are other sheets that I have excluded as they are not relevant here, but which are used elsewhere.
If this is not possible, I will have to look into reworking the layout of the information to make it feasible.
month copy test.xlsm
I am looking to remove all blanks from a table I have and move all the data left. Right now the data is by date, and some dates are blank for some players (it is a basketball sheet) and filled for others, but I want to see it by game. So I have what is in the first table below, and I want it to look like what is in the second.
2-22-2014
2-21-2014
2-20-2014
2-19-2014
2-18-2014
[code].....
I have a table of data 300 rows long with protected formulas in adjacent columns and at the bottom of the column. Some of the data cells are blank but the non-blank cells are contiguous and start at the top of the range (see Before Example).
a macro that would allow users to insert a blank cell at any point in the data table without losing any of the other data or disrupting the formulas. In other words I want to move all the data from that point to the bottom of the range down by 1 row. A before and after example for a smaller 10 row data set is shown below.
Before
AB1data aformula 12data bformula 23data cformula 34data dformula 45data eformula 56data fformula 67Â formula 78Â formula 89Â formula 910Â formula 1011formula Aformula B
After
AB14data aformula 115data bformula 216data cformula 317data dformula 418data eformula 519Â formula 620data fformula 721Â formula 822Â formula 923Â formula 1024formula Aformula B
What I want to do is Average out a range of cells "F4:F343". That's the easy part =average(F4:F343). However I want to exclude any numbers for that average if data in coloumn "Q" = "FMC"
Would that look anything like:
=average(F4:F343)/countif(Q4:Q343, "<>FMC")
I'm also displaying it as minutes in a day so i added *24*60 to the end of the formula and formatted to Number/.00. I've entered the formula and it will calculate but when i enter FMC to the appropriate area the average wont change.
I have three columns that i've called 'active period' and 'Date/time' and 'value'
The the value of the cells in the 'active period' column depend on the value and time.
I want to create a cell that; when i can enter a date, this date will be excluded from the 'active period' column
I have this workbook to keep track of current and new work for the team - each individual inputs information on their own tab.
In turn, the current information is displayed on a summary page so we can all see who's doing what.
I want to protect that summary page and its formulas from accidental amendment.
However, while the summary page (when unprotected) updates as soon as someone enters new information on their own tab, the summary page won't update when I have it protected.
Is there any way of doing this - preferably without VBA as it's a work situation and the employer doesn't like VBA code running?
I want to run a macro in a worksheet which is proteced.
It's a simple macro which erases the content in (some) cells, nothing more. Even those celles are formatted as unlocked the macro fails.
I have the following code which works fine when Sheet1 is unprotected (The code is placed in Sheet3)
however if I protect sheet1 then the code does not work
I have tried it with
Code:
ActiveSheet.Unprotect
Code:
ActiveSheet.Protect
it still will not work on Sheet1
here is the code
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error GoTo Handler
With Sheet1.Range("B6:B10045")
[Code] ....
I have a protected worksheet where I allow all users of the worksheet to filter yet when I filter, Excel gives a run time error 1004 - you cannot use this command in a protected worksheet. Could someone let me know what am I doing wrong?
View 9 Replies View Related