Mixing
Workbook Prompts for Changes Upon Closing When no Changes Were Made
I have an Excel file that runs some ActiveX components. The downside is that it asks to save each time I close the file, even when I didn't make any changes.
I've tried adding the following code to the ThisWorkbook object:
Private Sub Workbook_Open()
ActiveWorkbook.Saved = True
End Sub
However, this doesn't seem to work. It still asks me to save the file when I try to close it without making changes.
Any help?
excel vba excel-vba activex
edited Nov 22 '18 at 1:13
K.Dᴀᴠɪs
7,250112439
asked Nov 22 '18 at 0:28
knatknat
416
add a comment |
I have an Excel file that runs some ActiveX components. The downside is that it asks to save each time I close the file, even when I didn't make any changes.
I've tried adding the following code to the ThisWorkbook object:
Private Sub Workbook_Open()
ActiveWorkbook.Saved = True
End Sub
However, this doesn't seem to work. It still asks me to save the file when I try to close it without making changes.
Any help?
excel vba excel-vba activex
edited Nov 22 '18 at 1:13
K.Dᴀᴠɪs
7,250112439
asked Nov 22 '18 at 0:28
knatknat
416
Shouldn't it be ActiveWorkbook.Save, without "d"?
– Basher
Nov 22 '18 at 0:32
Check this link for some ideas to try.
– Portland Runner
Nov 22 '18 at 0:34
@Basher - No,Savewill save it,Savedwill set the flag. It should probably be on close event if the activex controls run after opening/interacting with workbook.
– Portland Runner
Nov 22 '18 at 0:36
add a comment |
I have an Excel file that runs some ActiveX components. The downside is that it asks to save each time I close the file, even when I didn't make any changes.
I've tried adding the following code to the ThisWorkbook object:
Private Sub Workbook_Open()
ActiveWorkbook.Saved = True
End Sub
However, this doesn't seem to work. It still asks me to save the file when I try to close it without making changes.
Any help?
excel vba excel-vba activex
edited Nov 22 '18 at 1:13
K.Dᴀᴠɪs
7,250112439
asked Nov 22 '18 at 0:28
knatknat
416
I have an Excel file that runs some ActiveX components. The downside is that it asks to save each time I close the file, even when I didn't make any changes.
I've tried adding the following code to the ThisWorkbook object:
Private Sub Workbook_Open()
ActiveWorkbook.Saved = True
End Sub
However, this doesn't seem to work. It still asks me to save the file when I try to close it without making changes.
Any help?
excel vba excel-vba activex
excel vba excel-vba activex
edited Nov 22 '18 at 1:13
K.Dᴀᴠɪs
7,250112439
asked Nov 22 '18 at 0:28
knatknat
416
edited Nov 22 '18 at 1:13
K.Dᴀᴠɪs
7,250112439
asked Nov 22 '18 at 0:28
knatknat
416
edited Nov 22 '18 at 1:13
K.Dᴀᴠɪs
7,250112439
edited Nov 22 '18 at 1:13
K.Dᴀᴠɪs
7,250112439
edited Nov 22 '18 at 1:13
K.Dᴀᴠɪs
7,250112439
7,250112439
asked Nov 22 '18 at 0:28
knatknat
416
asked Nov 22 '18 at 0:28
knatknat
416
asked Nov 22 '18 at 0:28
knatknat
416
416
Shouldn't it be ActiveWorkbook.Save, without "d"?
– Basher
Nov 22 '18 at 0:32
Check this link for some ideas to try.
– Portland Runner
Nov 22 '18 at 0:34
@Basher - No,Savewill save it,Savedwill set the flag. It should probably be on close event if the activex controls run after opening/interacting with workbook.
– Portland Runner
Nov 22 '18 at 0:36
add a comment |
Shouldn't it be ActiveWorkbook.Save, without "d"?
– Basher
Nov 22 '18 at 0:32
Check this link for some ideas to try.
– Portland Runner
Nov 22 '18 at 0:34
@Basher - No,Savewill save it,Savedwill set the flag. It should probably be on close event if the activex controls run after opening/interacting with workbook.
– Portland Runner
Nov 22 '18 at 0:36
Shouldn't it be ActiveWorkbook.Save, without "d"?
– Basher
Nov 22 '18 at 0:32
Shouldn't it be ActiveWorkbook.Save, without "d"?
– Basher
Nov 22 '18 at 0:32
Check this link for some ideas to try.
– Portland Runner
Nov 22 '18 at 0:34
Check this link for some ideas to try.
– Portland Runner
Nov 22 '18 at 0:34
@Basher - No,
Save will save it, Saved will set the flag. It should probably be on close event if the activex controls run after opening/interacting with workbook.– Portland Runner
Nov 22 '18 at 0:36
@Basher - No,
Save will save it, Saved will set the flag. It should probably be on close event if the activex controls run after opening/interacting with workbook.– Portland Runner
Nov 22 '18 at 0:36
add a comment |
1 Answer
1
active
oldest
votes
Ah, yes I have had to deal with this issue myself. Since these components run AFTER the workbook is opened (and after your Workbook_Open() event), you need to run your code when you're closing your workbook.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub
Should be placed in the ThisWorkbook code module.
To avoid accidentally not saving the workbook when you actually update it, you could try setting your own global variable flag - which should be located in a standard module and have the Public scope. Afterwards, you can use the Workbook_SheetChange event to set this flag when you actually change any content within the workbook:
Standard Module: Add Pub Flag
Public bPromptSave as Boolean
Workbook Module: Event Handlers
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
bPromptSave = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
if not bPromptSave then ThisWorkbook.Saved = True
End Sub
answered Nov 22 '18 at 0:50
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
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%2f53422305%2fworkbook-prompts-for-changes-upon-closing-when-no-changes-were-made%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
Ah, yes I have had to deal with this issue myself. Since these components run AFTER the workbook is opened (and after your Workbook_Open() event), you need to run your code when you're closing your workbook.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub
Should be placed in the ThisWorkbook code module.
To avoid accidentally not saving the workbook when you actually update it, you could try setting your own global variable flag - which should be located in a standard module and have the Public scope. Afterwards, you can use the Workbook_SheetChange event to set this flag when you actually change any content within the workbook:
Standard Module: Add Pub Flag
Public bPromptSave as Boolean
Workbook Module: Event Handlers
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
bPromptSave = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
if not bPromptSave then ThisWorkbook.Saved = True
End Sub
answered Nov 22 '18 at 0:50
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
add a comment |
Ah, yes I have had to deal with this issue myself. Since these components run AFTER the workbook is opened (and after your Workbook_Open() event), you need to run your code when you're closing your workbook.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub
Should be placed in the ThisWorkbook code module.
To avoid accidentally not saving the workbook when you actually update it, you could try setting your own global variable flag - which should be located in a standard module and have the Public scope. Afterwards, you can use the Workbook_SheetChange event to set this flag when you actually change any content within the workbook:
Standard Module: Add Pub Flag
Public bPromptSave as Boolean
Workbook Module: Event Handlers
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
bPromptSave = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
if not bPromptSave then ThisWorkbook.Saved = True
End Sub
answered Nov 22 '18 at 0:50
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
add a comment |
Ah, yes I have had to deal with this issue myself. Since these components run AFTER the workbook is opened (and after your Workbook_Open() event), you need to run your code when you're closing your workbook.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub
Should be placed in the ThisWorkbook code module.
To avoid accidentally not saving the workbook when you actually update it, you could try setting your own global variable flag - which should be located in a standard module and have the Public scope. Afterwards, you can use the Workbook_SheetChange event to set this flag when you actually change any content within the workbook:
Standard Module: Add Pub Flag
Public bPromptSave as Boolean
Workbook Module: Event Handlers
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
bPromptSave = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
if not bPromptSave then ThisWorkbook.Saved = True
End Sub
answered Nov 22 '18 at 0:50
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
Ah, yes I have had to deal with this issue myself. Since these components run AFTER the workbook is opened (and after your Workbook_Open() event), you need to run your code when you're closing your workbook.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub
Should be placed in the ThisWorkbook code module.
To avoid accidentally not saving the workbook when you actually update it, you could try setting your own global variable flag - which should be located in a standard module and have the Public scope. Afterwards, you can use the Workbook_SheetChange event to set this flag when you actually change any content within the workbook:
Standard Module: Add Pub Flag
Public bPromptSave as Boolean
Workbook Module: Event Handlers
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
bPromptSave = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
if not bPromptSave then ThisWorkbook.Saved = True
End Sub
answered Nov 22 '18 at 0:50
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
edited Nov 22 '18 at 1:00
answered Nov 22 '18 at 0:50
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
answered Nov 22 '18 at 0:50
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
answered Nov 22 '18 at 0:50
K.DᴀᴠɪsK.Dᴀᴠɪs
7,250112439
7,250112439
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%2f53422305%2fworkbook-prompts-for-changes-upon-closing-when-no-changes-were-made%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
Shouldn't it be ActiveWorkbook.Save, without "d"?
– Basher
Nov 22 '18 at 0:32
Check this link for some ideas to try.
– Portland Runner
Nov 22 '18 at 0:34
@Basher - No,
Savewill save it,Savedwill set the flag. It should probably be on close event if the activex controls run after opening/interacting with workbook.– Portland Runner
Nov 22 '18 at 0:36