ERROR 400 from clicking Macro Button

[davehow87]

Hi I have created a button- which i want to click and allow the rows to sort into a custom order- i have applied VBA and assigned the macro to the button but when I click it comes up with an error that just says "400" it worked for a while when i first put the code in so seems right but now it's not working!

this is the code i have used.... can you help please

Sub sortData()

Dim sh1 As Worksheet
Dim rSort As Range
Dim rKey As Range
Dim customSort(1 To 3) As String
Set sh1 = ActiveWorkbook.Worksheets("OVERVIEW")

dataCell = "B10"

Set rSort = sh1.Range(dataCell).CurrentRegion
Set rKey = sh1.Range(dataCell)

customSort(1) = "STATUS"
customSort(2) = "IN PROGRESS"
customSort(3) = "COMPLETE"


Application.AddCustomList ListArray:=customSort
rSort.Sort Key1:=rKey, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

End Sub



thanks

[davehow87]

actually figured out that it works when the sheet is unprotected!! But when I protect it it comes up with the error?? Any Help?

Thanks

[rabbita]

Hello,

When you protect your sheet you can't change cells unless you allow users what activities they can do. I use 2013 Excel but I think you will get the same window (when you try to protect your worksheet) as me where you can mark changes that will be allowed. One of them is "Sort". If you mark it, you should be able to run your macro without getting an error. If it doesn't work, you might unprotect a sheet using VBA code, then use your sorting and finally protect you worksheet again

[davehow87]

anyone know VBA code to unprotect, run my sort code, then protect again?

Thanks

[rabbita]

These methods are as follows:

Please Login or Register  to view this content.

[gmr4evr1]

Maybe

Please Login or Register  to view this content.

then

Please Login or Register  to view this content.

??

[rabbita]

Yes. Of course. Sorry, it's late

[gmr4evr1]

Sorry rabbit, didn't see that you had already posted to this.