Ask Your Question

Protect sheet from Macro without Popup [closed]

asked 2016-10-29 23:05:10 +0100

TubaGuy gravatar image

updated 2016-10-29 23:31:31 +0100

mark_t gravatar image

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
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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-14 17:21:30.247319


Edited question to place the code in a code block.

mark_t gravatar imagemark_t ( 2016-10-29 23:32:13 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-10-29 23:30:23 +0100

mark_t gravatar image

updated 2016-10-30 22:49:59 +0100

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    '


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

edit flag offensive delete link more


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


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

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

TubaGuy gravatar imageTubaGuy ( 2016-10-30 21:02:49 +0100 )edit

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

mark_t gravatar imagemark_t ( 2016-10-30 22:51:09 +0100 )edit

Question Tools

1 follower


Asked: 2016-10-29 23:05:10 +0100

Seen: 1,029 times

Last updated: Oct 30 '16