Protect sheet from Macro without Popup

[TubaGuy]

I have a macro that unprotects the calc sheet, runs a sort, the reprotects the sheet.

This is to prevent the non-computer savvy user who will be ultimately using this spreadsheet from accidentally modifying the contents of certain cells.

The macro works perfectly, except for one small glitch. When protecting the sheet, the popup appears asking for a password. I would just as soon this didn’t happen. I do not want my technically challenged user to have to deal with this popup. The relevant code looks like this.

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Protect"
args3(0).Value = true

dispatcher.executeDispatch(document, ".uno:Protect", "", 0, args3())

How can I change this code to not bring up the popup?

[mark_t]

Edited question to place the code in a code block.

[mark_t]

Instead of using dispatch try to use use the protect or unprotect methods of the sheet object.

Edit to give a bit more detail:-

Dim oSheet As Variant 
Dim sPass As String

oSheet = ThisComponent.CurrentController.getActiveSheet    ' Select the Activesheet of your workbook    '

sPass = "1234"      '    Password to unprotect and protect your worksheet    '

oSheet.Unprotect(sPass)

'    Code for changes to your worksheet should be inserted here '

oSheet.Protect(sPass)

[TubaGuy]

I have to admit my coding skills are a bit limited. I replaced recorded code with

oSheet.Unprotect(sPass)

I get the message: BASIC runtime error. Object variable not set.

neither google nor help is giving me anything to go on.

[mark_t]

Modified code to use active worksheet and define the password, which you probably need to change to suit your own document.