Help to convert VBA to Excel Online script

%3CLINGO-SUB%20id%3D%22lingo-sub-2912629%22%20slang%3D%22en-US%22%3EHelp%20to%20convert%20VBA%20to%20Excel%20Online%20script%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2912629%22%20slang%3D%22en-US%22%3EHi%2C%20I%20need%20help%20converting%20a%20VBA%20to%20a%20Script%20for%20Excel%20Online.%3CBR%20%2F%3ECan%20anyone%20help%20me%20with%20this%3F%3F%3CBR%20%2F%3EVBA%20code%20is%3A%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3EActiveSheet.Unprotect%20%22Brooke%22%3CBR%20%2F%3ERange(%22F1%22).Sort%20Key1%3A%3DRange(%22F2%22)%2C%20_%3CBR%20%2F%3EOrder1%3A%3DxlAscending%2C%20Header%3A%3DxlYes%2C%20_%3CBR%20%2F%3EOrderCustom%3A%3D1%2C%20MatchCase%3A%3DFalse%2C%20_%3CBR%20%2F%3EOrientation%3A%3DxlTopToBottom%3CBR%20%2F%3EActiveSheet.Protect%20%22Brooke%22%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2912629%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2913198%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20convert%20VBA%20to%20Excel%20Online%20script%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2913198%22%20slang%3D%22en-US%22%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1203242%22%20target%3D%22_blank%22%3E%40B_H33%3C%2FA%3E%3CBR%20%2F%3EOffice%20Script%20does%20not%20support%20event%20code%20like%20your%20Worksheet_Change%20event%2C%20you%20can%20only%20start%20scripts%20manually.%20Have%20you%20tried%20to%20record%20the%20steps%20using%20the%20script%20recorder%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2913545%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20convert%20VBA%20to%20Excel%20Online%20script%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2913545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20it%20somewhat%20frustrating.%26nbsp%3B%20The%20only%20things%20I%20tend%20to%20use%20VBA%20for%20are%20to%20create%20sketches%20to%20illustrate%20engineering%20data%20using%20shapes%20and%20for%20event%20handling%20to%20create%20interactive%20user%20interfaces.%26nbsp%3B%20Nothing%20of%20which%20works%20on-line.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20Lambda%20functions%20are%20fully%20functional%2C%20many%20of%20the%20process%20steps%20that%20used%20to%20be%20implemented%20using%20VBA%20could%20be%20achieved%20with%20worksheet%20formula.%26nbsp%3B%20In%20the%20present%20case%2C%20the%20data%20input%20table%20could%20be%20left%20in%20its%20original%20sequence%20whilst%20a%20formula%20would%20give%20the%20sorted%20representation%20on%20a%20separate%20worksheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
Hi, I need help converting a VBA to a Script for Excel Online.
Can anyone help me with this??
VBA code is:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
ActiveSheet.Unprotect "Brooke"
Range("F1").Sort Key1:=Range("F2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ActiveSheet.Protect "Brooke"
End Sub

6 Replies
Hi @B_H33
Office Script does not support event code like your Worksheet_Change event, you can only start scripts manually. Have you tried to record the steps using the script recorder?

@Jan Karel Pieterse 

I found it somewhat frustrating.  The only things I tend to use VBA for are to create sketches to illustrate engineering data using shapes and for event handling to create interactive user interfaces.  Nothing of which works on-line.

 

Once Lambda functions are fully functional, many of the process steps that used to be implemented using VBA could be achieved with worksheet formula.  In the present case, the data input table could be left in its original sequence whilst a formula would give the sorted representation on a separate worksheet.

Hi Jan,
I only want to auto sort column F.
Can you help me write a script?
@B_H33 Have you tried recording a script yet?
Yes I did today. But when I run it it only sorts column F and doesn't extend to other columns...
Please paste your current script result here, I'll see what I can do.