Run VBA with MSGBox on a password protected File
I am trying to run the code as described here:
Private Sub Worksheet_Calculate()
Dim rr As Range, r As Range
Dim bNo As Boolean
Set rr = Range("AU17:AU300").Cells.SpecialCells(xlCellTypeFormulas)
bNo = False
For Each r In rr
If r.Value = "No" Then
bNo = True
End If
Next r
If bNo = True Then
MsgBox "The HS Number entered is not recorded. Please call Marcelo", vbExclamation
End If
End Sub
on a password protected file.
I get
runtime error 1004: You cannot use this command on a protected sheet.
in Line: Set rr = Range...
Is there a way to bypass this as I can't leave the worksheet unprotected.
excel vba
edited Nov 27 '18 at 15:55
Pᴇʜ
25.1k63052
asked Nov 26 '18 at 12:58
MDSMDS
11
add a comment |
I am trying to run the code as described here:
Private Sub Worksheet_Calculate()
Dim rr As Range, r As Range
Dim bNo As Boolean
Set rr = Range("AU17:AU300").Cells.SpecialCells(xlCellTypeFormulas)
bNo = False
For Each r In rr
If r.Value = "No" Then
bNo = True
End If
Next r
If bNo = True Then
MsgBox "The HS Number entered is not recorded. Please call Marcelo", vbExclamation
End If
End Sub
on a password protected file.
I get
runtime error 1004: You cannot use this command on a protected sheet.
in Line: Set rr = Range...
Is there a way to bypass this as I can't leave the worksheet unprotected.
excel vba
edited Nov 27 '18 at 15:55
Pᴇʜ
25.1k63052
asked Nov 26 '18 at 12:58
MDSMDS
11
2
Linking to a different code is not sufficient here. Please include the exact code you actually use within your question (edit) and tell in which code line you get the error.
– Pᴇʜ
Nov 26 '18 at 13:01
Which line is flagged when the error occurs?
– Tim Williams
Nov 26 '18 at 18:44
add a comment |
I am trying to run the code as described here:
Private Sub Worksheet_Calculate()
Dim rr As Range, r As Range
Dim bNo As Boolean
Set rr = Range("AU17:AU300").Cells.SpecialCells(xlCellTypeFormulas)
bNo = False
For Each r In rr
If r.Value = "No" Then
bNo = True
End If
Next r
If bNo = True Then
MsgBox "The HS Number entered is not recorded. Please call Marcelo", vbExclamation
End If
End Sub
on a password protected file.
I get
runtime error 1004: You cannot use this command on a protected sheet.
in Line: Set rr = Range...
Is there a way to bypass this as I can't leave the worksheet unprotected.
excel vba
edited Nov 27 '18 at 15:55
Pᴇʜ
25.1k63052
asked Nov 26 '18 at 12:58
MDSMDS
11
I am trying to run the code as described here:
Private Sub Worksheet_Calculate()
Dim rr As Range, r As Range
Dim bNo As Boolean
Set rr = Range("AU17:AU300").Cells.SpecialCells(xlCellTypeFormulas)
bNo = False
For Each r In rr
If r.Value = "No" Then
bNo = True
End If
Next r
If bNo = True Then
MsgBox "The HS Number entered is not recorded. Please call Marcelo", vbExclamation
End If
End Sub
on a password protected file.
I get
runtime error 1004: You cannot use this command on a protected sheet.
in Line: Set rr = Range...
Is there a way to bypass this as I can't leave the worksheet unprotected.
excel vba
excel vba
edited Nov 27 '18 at 15:55
Pᴇʜ
25.1k63052
asked Nov 26 '18 at 12:58
MDSMDS
11
edited Nov 27 '18 at 15:55
Pᴇʜ
25.1k63052
asked Nov 26 '18 at 12:58
MDSMDS
11
edited Nov 27 '18 at 15:55
Pᴇʜ
25.1k63052
edited Nov 27 '18 at 15:55
Pᴇʜ
25.1k63052
edited Nov 27 '18 at 15:55
Pᴇʜ
25.1k63052
25.1k63052
asked Nov 26 '18 at 12:58
MDSMDS
11
asked Nov 26 '18 at 12:58
MDSMDS
11
asked Nov 26 '18 at 12:58
MDSMDS
11
11
2
Linking to a different code is not sufficient here. Please include the exact code you actually use within your question (edit) and tell in which code line you get the error.
– Pᴇʜ
Nov 26 '18 at 13:01
Which line is flagged when the error occurs?
– Tim Williams
Nov 26 '18 at 18:44
add a comment |
2
Linking to a different code is not sufficient here. Please include the exact code you actually use within your question (edit) and tell in which code line you get the error.
– Pᴇʜ
Nov 26 '18 at 13:01
Which line is flagged when the error occurs?
– Tim Williams
Nov 26 '18 at 18:44
2
2
Linking to a different code is not sufficient here. Please include the exact code you actually use within your question (edit) and tell in which code line you get the error.
– Pᴇʜ
Nov 26 '18 at 13:01
Linking to a different code is not sufficient here. Please include the exact code you actually use within your question (edit) and tell in which code line you get the error.
– Pᴇʜ
Nov 26 '18 at 13:01
Which line is flagged when the error occurs?
– Tim Williams
Nov 26 '18 at 18:44
Which line is flagged when the error occurs?
– Tim Williams
Nov 26 '18 at 18:44
add a comment |
1 Answer
1
active
oldest
votes
Don't protect your worksheets manually. Instead protect them with VBA using UserInterfaceOnly:=True. This way your worksheets are protected for the user interface but VBA can still access them:
.Protect Password:="myPassword", UserInterfaceOnly:=True
Note that you need to reapply UserInterfaceOnly:=True after reopening the workbook.
answered Nov 27 '18 at 16:00
PᴇʜPᴇʜ
25.1k63052
Hi Peh, thank you for your answer. I'll try it and let you know if it solved my Problem.
– MDS
Nov 30 '18 at 9:21
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53481633%2frun-vba-with-msgbox-on-a-password-protected-file%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Don't protect your worksheets manually. Instead protect them with VBA using UserInterfaceOnly:=True. This way your worksheets are protected for the user interface but VBA can still access them:
.Protect Password:="myPassword", UserInterfaceOnly:=True
Note that you need to reapply UserInterfaceOnly:=True after reopening the workbook.
answered Nov 27 '18 at 16:00
PᴇʜPᴇʜ
25.1k63052
Hi Peh, thank you for your answer. I'll try it and let you know if it solved my Problem.
– MDS
Nov 30 '18 at 9:21
add a comment |
Don't protect your worksheets manually. Instead protect them with VBA using UserInterfaceOnly:=True. This way your worksheets are protected for the user interface but VBA can still access them:
.Protect Password:="myPassword", UserInterfaceOnly:=True
Note that you need to reapply UserInterfaceOnly:=True after reopening the workbook.
answered Nov 27 '18 at 16:00
PᴇʜPᴇʜ
25.1k63052
Hi Peh, thank you for your answer. I'll try it and let you know if it solved my Problem.
– MDS
Nov 30 '18 at 9:21
add a comment |
Don't protect your worksheets manually. Instead protect them with VBA using UserInterfaceOnly:=True. This way your worksheets are protected for the user interface but VBA can still access them:
.Protect Password:="myPassword", UserInterfaceOnly:=True
Note that you need to reapply UserInterfaceOnly:=True after reopening the workbook.
answered Nov 27 '18 at 16:00
PᴇʜPᴇʜ
25.1k63052
Don't protect your worksheets manually. Instead protect them with VBA using UserInterfaceOnly:=True. This way your worksheets are protected for the user interface but VBA can still access them:
.Protect Password:="myPassword", UserInterfaceOnly:=True
Note that you need to reapply UserInterfaceOnly:=True after reopening the workbook.
answered Nov 27 '18 at 16:00
PᴇʜPᴇʜ
25.1k63052
answered Nov 27 '18 at 16:00
PᴇʜPᴇʜ
25.1k63052
answered Nov 27 '18 at 16:00
PᴇʜPᴇʜ
25.1k63052
answered Nov 27 '18 at 16:00
PᴇʜPᴇʜ
25.1k63052
25.1k63052
Hi Peh, thank you for your answer. I'll try it and let you know if it solved my Problem.
– MDS
Nov 30 '18 at 9:21
add a comment |
Hi Peh, thank you for your answer. I'll try it and let you know if it solved my Problem.
– MDS
Nov 30 '18 at 9:21
Hi Peh, thank you for your answer. I'll try it and let you know if it solved my Problem.
– MDS
Nov 30 '18 at 9:21
Hi Peh, thank you for your answer. I'll try it and let you know if it solved my Problem.
– MDS
Nov 30 '18 at 9:21
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53481633%2frun-vba-with-msgbox-on-a-password-protected-file%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Linking to a different code is not sufficient here. Please include the exact code you actually use within your question (edit) and tell in which code line you get the error.
– Pᴇʜ
Nov 26 '18 at 13:01
Which line is flagged when the error occurs?
– Tim Williams
Nov 26 '18 at 18:44