ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   VBA auto sort multicolumn table when data is changed/deleted in protected sheet mode (https://www.excelbanter.com/excel-worksheet-functions/454276-vba-auto-sort-multicolumn-table-when-data-changed-deleted-protected-sheet-mode.html)

Roumen Roussev

VBA auto sort multicolumn table when data is changed/deleted in protected sheet mode
 
I use the code to automatically sort the table by attribute "Name" from column B.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
If Not Intersect(Target, Range("A:Z")) Is Nothing Then
Range("B5").Sort Key1:=Range("B6"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

The first problem with this code is that when I delete a record after the first line remains an empty cell.
The second problem is how to make the code work while sheet is password-protected.
Protected password can be found in sheet PASS.

This is the WeTransfer link to an Excel file: https://we.tl/t-00zqVQgx82

GS[_6_]

VBA auto sort multicolumn table when data is changed/deleted in protected sheet mode
 
Be careful that code line wrap doesn't occur after pasting. Split lines use...

" _"

...at the end. Otherwise all lines are single lines.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Roumen Roussev

Mistake
 
I'm sorry, but I realized that the opened post was not in the correct thread.


All times are GMT +1. The time now is 12:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com