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 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 RelatedWe are needing to do a two tier sort in a protected spreadsheet. Is this possible?
The two columns that need to be sorted have protected cells between them.
If there a way to ad a button that will automatically do this sort for the user... as in can I add a button some how in one of the empty cells that will do it?
Here is the sheet. [URL] ..........
Looks like I need to sort column A thru Z, except W.
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...
I'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.
As of right now these are the steps i do to sort...i click custom sort choose My data has headers and then i select from the drop down list the word FRNAME.
is there any way i can setup a macro to do this for me? i tried recording the macro but it just is recording me choosing the column FRNAME is in. This does not work for me since FRNAME end up being in different columns all the time but will always be in row 1.
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 RelatedWhen 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") ....
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 have read that there is a VBA macro in F11, but I also read that it would only sort the workshhet names, but not the data. I have Excel 2010.
View 2 Replies View RelatedIf 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?
I Need the outline/group feature to work on a protected workbook with over 200 sheets, meaning a macro to cover the entire workbook not just a single sheet. This macro should travel with the workbook as it will be on many different peoples PC's
Private Sub Workbook_Open()
Dim cSheet As Integer
Dim tSheet As Worksheet
On Error Resume Next
cSheet = 1
Set tSheet = Worksheets(cSheet)
While Err.Number = 0
Call wbProtect(tSheet)
cSheet = cSheet + 1
Set tSheet = Worksheets(cSheet)
Wend
Err.Number = 0
End Sub..........................
I have data being fed from an application where it's difficult to sort the rows into ascending order. So is there a way that VBA can sort them and produce a value according to the order?
For example, Column A contains 5 rows.
Row 1's value is 4
Row 2's value is 1
Row 3's value is 2
Row 4's value is 3
Row 5's value is 5
If I were to sort these into ascending order, I'd get 1, 2, 3, 4, 5 in Column A. But I can't sort the rows so I need code to tell me which is the lowest number (1), which is the second lowest number (2), etc.
The purpose of the code will be to put values in Column B according to what's found in Column A. For the the lowest number, 1, the value in the cell next to it in Column B would be "a". For number 2, it would be "b", etc.
Cycle through all sheets in a workbook performing the following:Store worksheet protection state (bSheetProtection)... execute code ...Restore worksheet protectionI cannot seem to locate a way to save a worksheet's protection state in a variable.
View 5 Replies View RelatedI want to sort columns A,C:P and use column C as the column that I sort and the other rows will move with column C. I want column B to stay and not move with the sort. How can I do this.
View 2 Replies View RelatedIn the attached workbook, sorting the dates in column M results in absolutely nothing happening. The dates are formatted as dates (dd/mm/yyyy). The dates in column M are arrived at by adding a number of days (formatted as Number) to another date, the value of which was determined by an array formula. When I retype the actual date into another column and sort that, I get it sorted. Why does the other sort not work? BTW - I actually need to sort column M with column N.
View 2 Replies View RelatedI have the following code that should open all EXCEL workbooks in a
specified path, and unprotoect any password-protected worksheets to allow
for Link Updates, then close the workbook after password protecting it.
sub UpdateAllLinks()
Dim vLinkSources
Dim iLinkSource As Integer
Dim AnySheet As Worksheet
sPath = " C:Documents and SettingsShaneMy DocumentsHarcourt
Assessmentspassword"
sName = Dir(sPath & "*.xls")
do while sName <> ""...............
I've looked through this wonderful sight and haven't found one close to my project. I am a bit rusty on VB programming. I am working on workbook that contains multiple worksheets. Each worksheet is hidden and is accessible with a login and password so that only a specific user can access. My question is: How do I modify this so that the manager can open the workbook and view ALL worksheets without having to enter the login name and password for all of his users? Any help would be great. Thank you!!
Below is what I have so far: ....
Is there a way to password protect a document AND still allow for a user to expand or collapse columns?
Currently, it appear that when a worksheet is password protected, one cannot expand or collapse a column.
I have a sheet that I protect but i use the below code so that I can still use my gouping '+' or '-' symbols to hide/unhide rows.
ws.Protect Password:="PASSWORD", userinterfaceonly:=True
ws.EnableOutlining = True
This is ok but When close the workbook and reopen it, my sheet becomes fully protected and i cant use the group icons. Can anyone either suggest better code or a way to initilise the workbook to act in this way when it loads up. For Reference Below is my full
Sub ProtectAll()
Dim ws As Worksheet
sSheet = Control.Name
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case sSheet1
Case Else
ws.Protect Password:="PASSWORD", userinterfaceonly:=True
ws.EnableOutlining = True
End Select
Next ws
End Sub
I'm using the excel built in function to sort columns with my macro. However since my columns have 64,000 entries it takes a while for the computer to sort it.
View 14 Replies View RelatedI have this module that is supposed to look in column A and if it sees duplicates it looks in column B (with the date) and erases the entire row where the date is older in column B. I am attaching a sample to better illustrate it.
The highlighted in yellow rows are were I have duplicates and I want the row where the date is in red border to remain.
The module I have so far erases the newer records instead of the older.
This is the file:
Sample sorting.xls
Here is the
In my example file are 4 columns. I placed auto filter to columns B and C. If column B sorted to ascending then this changes formulas in column D. I attached workbook also to understand my problem. If you try to sort column B to ascending you will see the problem in column D
View 4 Replies View RelatedI have two slicers that are in a hierarchy. These are attached to a Pivot table whose data source is an OLAP cube. Every Partner Parent is part of a Partner Group:
Partner Parents Slicers.png
My problem is that when I click SI Alliance in Partner Group, the Partner Parent slicer does not re-sort in any way. The corresponding selected Partner Parents are scattered throughout the alphabetical list. However, in a different document, I have slicers in a similar situation, except their Pivot table’s data source is a SQL Server database. When I click on a member of the higher up group (Accenture Global Client) the lower level group (Microsoft Account Name) sorts to show only the selected values at the top of the list:
This is with all selected - Accenture Slicers All.png
This is with just one Accenture Global Client selected. Note how the selected Microsoft Account Names have moved to the top of the list - Accenture Slicers Selected.png
How I can configure my Partner Parent/Partner Group slicers to behave like these Accenture/Microsoft slicers? Is there something that I can change in Excel or in the OLAP cube to make this happen? I have already tried right-clicking the slicer and going to Slicer Settings. The settings on the Parent/Partner Group slicers mimic those of the Accenture/Microsoft slicers exactly.
I am putting together multiple worksheets with dumped data that should sort themselves by the press of a button. Each entry has a 'code' and a value and they are sorted by the 'code'.
At the moment i have the first worksheet sorting correctly and i am trying to program the second worksheet to sort data into the existing worksheets if they exist or create a new worksheet if the data doesn't have it's own worksheet.
Here is the code i am working with.
I have been looking at Macros that sort worksheets alphabetically, but there are sheets within my workbook that I want to leave static. I have two sheets, names 'Start' and 'Spacer', what I would ideally like to have is a marco that will alphabetically arrange the sheets between these sheets, leaving the others where they are and obviously leaving 'Start' and 'Spacer' at the front and end of the range respectivly!
View 5 Replies View RelatedIs there a formula I can use that would sort information from a master
sheet into up to 4 different worksheets in the same workbook? Right
now I'm engineering IF statements, but I've come across the problem of
blank rows. To get rid of this, I fixed the false value as "zzzz" and
then sorted (because when sorting with " " as the false value, the
values end up on the bottom of the worksheet) but when using the Find
and Replace option, it replaced the zzzz values in the formulas, which
defeats the purpose.
I have a workbook with two worksheets.
Worksheet one is "Company ID" info, in it are the columns:"Company Name", "street add", "city", "state".
Worksheet two is "Company Contact Person" in it are the columns: a reference to worksheet one column one "Company Name", and column two is 'Contact Person name'.
I want to be able to sort worksheet one at will, By city, or state, or any of the various columns. When sorting worksheet one I want the relationships in worksheet to to remain intact.
"Company Name" - "Company Contact Person".