Run-time error '1004' Unable to set the NumberFormat property of Range Class
I installed a very simple command button to timestamp a selected cell. The command button works completely fine until I protect the sheet, then it throws the error mentioned in the title. I have already checked that the cells' format is not selected to 'locked'.
Private Sub CommandButton1_Click()
Dim ts As Date
With Selection
.Value = Now
.NumberFormat = "h:mm AM/PM"
End With
End Sub
excel vba excel-vba ms-error-1004
asked Nov 21 '18 at 17:59
mdavis0510mdavis0510
43
add a comment |
I installed a very simple command button to timestamp a selected cell. The command button works completely fine until I protect the sheet, then it throws the error mentioned in the title. I have already checked that the cells' format is not selected to 'locked'.
Private Sub CommandButton1_Click()
Dim ts As Date
With Selection
.Value = Now
.NumberFormat = "h:mm AM/PM"
End With
End Sub
excel vba excel-vba ms-error-1004
asked Nov 21 '18 at 17:59
mdavis0510mdavis0510
43
Even if the cell is not locked, you'll get a RTE 1004 if you don't allow for formatting of cells when you protect the sheet. Protecting the sheet withUserOnlyInterface:= Truemight be what you're looking for. See here for more detail.
– BigBen
Nov 21 '18 at 18:11
add a comment |
I installed a very simple command button to timestamp a selected cell. The command button works completely fine until I protect the sheet, then it throws the error mentioned in the title. I have already checked that the cells' format is not selected to 'locked'.
Private Sub CommandButton1_Click()
Dim ts As Date
With Selection
.Value = Now
.NumberFormat = "h:mm AM/PM"
End With
End Sub
excel vba excel-vba ms-error-1004
asked Nov 21 '18 at 17:59
mdavis0510mdavis0510
43
I installed a very simple command button to timestamp a selected cell. The command button works completely fine until I protect the sheet, then it throws the error mentioned in the title. I have already checked that the cells' format is not selected to 'locked'.
Private Sub CommandButton1_Click()
Dim ts As Date
With Selection
.Value = Now
.NumberFormat = "h:mm AM/PM"
End With
End Sub
excel vba excel-vba ms-error-1004
excel vba excel-vba ms-error-1004
asked Nov 21 '18 at 17:59
mdavis0510mdavis0510
43
asked Nov 21 '18 at 17:59
mdavis0510mdavis0510
43
asked Nov 21 '18 at 17:59
mdavis0510mdavis0510
43
asked Nov 21 '18 at 17:59
mdavis0510mdavis0510
43
asked Nov 21 '18 at 17:59
mdavis0510mdavis0510
43
43
Even if the cell is not locked, you'll get a RTE 1004 if you don't allow for formatting of cells when you protect the sheet. Protecting the sheet withUserOnlyInterface:= Truemight be what you're looking for. See here for more detail.
– BigBen
Nov 21 '18 at 18:11
add a comment |
Even if the cell is not locked, you'll get a RTE 1004 if you don't allow for formatting of cells when you protect the sheet. Protecting the sheet withUserOnlyInterface:= Truemight be what you're looking for. See here for more detail.
– BigBen
Nov 21 '18 at 18:11
Even if the cell is not locked, you'll get a RTE 1004 if you don't allow for formatting of cells when you protect the sheet. Protecting the sheet with
UserOnlyInterface:= True might be what you're looking for. See here for more detail.– BigBen
Nov 21 '18 at 18:11
Even if the cell is not locked, you'll get a RTE 1004 if you don't allow for formatting of cells when you protect the sheet. Protecting the sheet with
UserOnlyInterface:= True might be what you're looking for. See here for more detail.– BigBen
Nov 21 '18 at 18:11
add a comment |
2 Answers
2
active
oldest
votes
AllowFormattingCells
You have to allow Formatting of cells.
or in VBA
ActiveSheet.Protect AllowFormattingCells:=True
answered Nov 21 '18 at 18:13
VBasic2008VBasic2008
2,3362314
That worked, thanks!
– mdavis0510
Nov 21 '18 at 18:40
add a comment |
Formatting a sheet may be considered a worksheet change. You can format the value though.
With Selection
.Value = Format(Now, "h:mm AM/PM")
End With
answered Nov 21 '18 at 18:10
DavesexcelDavesexcel
5,07921936
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%2f53418038%2frun-time-error-1004-unable-to-set-the-numberformat-property-of-range-class%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
AllowFormattingCells
You have to allow Formatting of cells.
or in VBA
ActiveSheet.Protect AllowFormattingCells:=True
answered Nov 21 '18 at 18:13
VBasic2008VBasic2008
2,3362314
That worked, thanks!
– mdavis0510
Nov 21 '18 at 18:40
add a comment |
AllowFormattingCells
You have to allow Formatting of cells.
or in VBA
ActiveSheet.Protect AllowFormattingCells:=True
answered Nov 21 '18 at 18:13
VBasic2008VBasic2008
2,3362314
That worked, thanks!
– mdavis0510
Nov 21 '18 at 18:40
add a comment |
AllowFormattingCells
You have to allow Formatting of cells.
or in VBA
ActiveSheet.Protect AllowFormattingCells:=True
answered Nov 21 '18 at 18:13
VBasic2008VBasic2008
2,3362314
AllowFormattingCells
You have to allow Formatting of cells.
or in VBA
ActiveSheet.Protect AllowFormattingCells:=True
answered Nov 21 '18 at 18:13
VBasic2008VBasic2008
2,3362314
edited Nov 25 '18 at 9:17
answered Nov 21 '18 at 18:13
VBasic2008VBasic2008
2,3362314
answered Nov 21 '18 at 18:13
VBasic2008VBasic2008
2,3362314
answered Nov 21 '18 at 18:13
VBasic2008VBasic2008
2,3362314
2,3362314
That worked, thanks!
– mdavis0510
Nov 21 '18 at 18:40
add a comment |
That worked, thanks!
– mdavis0510
Nov 21 '18 at 18:40
That worked, thanks!
– mdavis0510
Nov 21 '18 at 18:40
That worked, thanks!
– mdavis0510
Nov 21 '18 at 18:40
add a comment |
Formatting a sheet may be considered a worksheet change. You can format the value though.
With Selection
.Value = Format(Now, "h:mm AM/PM")
End With
answered Nov 21 '18 at 18:10
DavesexcelDavesexcel
5,07921936
add a comment |
Formatting a sheet may be considered a worksheet change. You can format the value though.
With Selection
.Value = Format(Now, "h:mm AM/PM")
End With
answered Nov 21 '18 at 18:10
DavesexcelDavesexcel
5,07921936
add a comment |
Formatting a sheet may be considered a worksheet change. You can format the value though.
With Selection
.Value = Format(Now, "h:mm AM/PM")
End With
answered Nov 21 '18 at 18:10
DavesexcelDavesexcel
5,07921936
Formatting a sheet may be considered a worksheet change. You can format the value though.
With Selection
.Value = Format(Now, "h:mm AM/PM")
End With
answered Nov 21 '18 at 18:10
DavesexcelDavesexcel
5,07921936
answered Nov 21 '18 at 18:10
DavesexcelDavesexcel
5,07921936
answered Nov 21 '18 at 18:10
DavesexcelDavesexcel
5,07921936
answered Nov 21 '18 at 18:10
DavesexcelDavesexcel
5,07921936
5,07921936
add a comment |
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%2f53418038%2frun-time-error-1004-unable-to-set-the-numberformat-property-of-range-class%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
Even if the cell is not locked, you'll get a RTE 1004 if you don't allow for formatting of cells when you protect the sheet. Protecting the sheet with
UserOnlyInterface:= Truemight be what you're looking for. See here for more detail.– BigBen
Nov 21 '18 at 18:11