Uncheck a checkbox if the (X)-Close Button of the Userform is pressed
I have a userform that is activated whenever I check a checkbox that is on the sheet, and if I uncheck it, it is hidden from the screen. The problem is that when I press the red close (X) button from the userform, the checkbox doesn't uncheck, but it should, since the userform is no longer on the screen. I don't know how to fix that.
excel vba excel-vba
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
asked Nov 24 '18 at 17:45
cristi mcristi m
101
add a comment |
I have a userform that is activated whenever I check a checkbox that is on the sheet, and if I uncheck it, it is hidden from the screen. The problem is that when I press the red close (X) button from the userform, the checkbox doesn't uncheck, but it should, since the userform is no longer on the screen. I don't know how to fix that.
excel vba excel-vba
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
asked Nov 24 '18 at 17:45
cristi mcristi m
101
Are you asking for code that unchecks a worksheet checkbox? If so, you need to give more detail, e.g., what kind of checkbox and perhaps a sample of your code. If you already have a Close button that works and you're asking how to make the X act like the close button then take a look at the QueryClose event.
– Doug Glancy
Nov 24 '18 at 17:58
Here is an example how to use the query close event.
– Storax
Nov 24 '18 at 18:34
I have on the sheet a form control checkbox that when checked it displays the userform, and when unchecked it removes the userform from the screen. What i'm asking is how to uncheck the checkbox when the userform is on the screen and its cancel (X) from top right corner is pressed, because after i press this button and the userform closes the checkbox is still checked.
– cristi m
Nov 24 '18 at 20:11
Why don't you show your code? I expect you have something like Userform1.show in your code. In this way you use the global default instance which yon can but shouldn't. Anyway, see below
– Storax
Nov 24 '18 at 20:56
add a comment |
I have a userform that is activated whenever I check a checkbox that is on the sheet, and if I uncheck it, it is hidden from the screen. The problem is that when I press the red close (X) button from the userform, the checkbox doesn't uncheck, but it should, since the userform is no longer on the screen. I don't know how to fix that.
excel vba excel-vba
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
asked Nov 24 '18 at 17:45
cristi mcristi m
101
I have a userform that is activated whenever I check a checkbox that is on the sheet, and if I uncheck it, it is hidden from the screen. The problem is that when I press the red close (X) button from the userform, the checkbox doesn't uncheck, but it should, since the userform is no longer on the screen. I don't know how to fix that.
excel vba excel-vba
excel vba excel-vba
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
asked Nov 24 '18 at 17:45
cristi mcristi m
101
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
asked Nov 24 '18 at 17:45
cristi mcristi m
101
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
23.6k62951
asked Nov 24 '18 at 17:45
cristi mcristi m
101
asked Nov 24 '18 at 17:45
cristi mcristi m
101
asked Nov 24 '18 at 17:45
cristi mcristi m
101
101
Are you asking for code that unchecks a worksheet checkbox? If so, you need to give more detail, e.g., what kind of checkbox and perhaps a sample of your code. If you already have a Close button that works and you're asking how to make the X act like the close button then take a look at the QueryClose event.
– Doug Glancy
Nov 24 '18 at 17:58
Here is an example how to use the query close event.
– Storax
Nov 24 '18 at 18:34
I have on the sheet a form control checkbox that when checked it displays the userform, and when unchecked it removes the userform from the screen. What i'm asking is how to uncheck the checkbox when the userform is on the screen and its cancel (X) from top right corner is pressed, because after i press this button and the userform closes the checkbox is still checked.
– cristi m
Nov 24 '18 at 20:11
Why don't you show your code? I expect you have something like Userform1.show in your code. In this way you use the global default instance which yon can but shouldn't. Anyway, see below
– Storax
Nov 24 '18 at 20:56
add a comment |
Are you asking for code that unchecks a worksheet checkbox? If so, you need to give more detail, e.g., what kind of checkbox and perhaps a sample of your code. If you already have a Close button that works and you're asking how to make the X act like the close button then take a look at the QueryClose event.
– Doug Glancy
Nov 24 '18 at 17:58
Here is an example how to use the query close event.
– Storax
Nov 24 '18 at 18:34
I have on the sheet a form control checkbox that when checked it displays the userform, and when unchecked it removes the userform from the screen. What i'm asking is how to uncheck the checkbox when the userform is on the screen and its cancel (X) from top right corner is pressed, because after i press this button and the userform closes the checkbox is still checked.
– cristi m
Nov 24 '18 at 20:11
Why don't you show your code? I expect you have something like Userform1.show in your code. In this way you use the global default instance which yon can but shouldn't. Anyway, see below
– Storax
Nov 24 '18 at 20:56
Are you asking for code that unchecks a worksheet checkbox? If so, you need to give more detail, e.g., what kind of checkbox and perhaps a sample of your code. If you already have a Close button that works and you're asking how to make the X act like the close button then take a look at the QueryClose event.
– Doug Glancy
Nov 24 '18 at 17:58
Are you asking for code that unchecks a worksheet checkbox? If so, you need to give more detail, e.g., what kind of checkbox and perhaps a sample of your code. If you already have a Close button that works and you're asking how to make the X act like the close button then take a look at the QueryClose event.
– Doug Glancy
Nov 24 '18 at 17:58
Here is an example how to use the query close event.
– Storax
Nov 24 '18 at 18:34
Here is an example how to use the query close event.
– Storax
Nov 24 '18 at 18:34
I have on the sheet a form control checkbox that when checked it displays the userform, and when unchecked it removes the userform from the screen. What i'm asking is how to uncheck the checkbox when the userform is on the screen and its cancel (X) from top right corner is pressed, because after i press this button and the userform closes the checkbox is still checked.
– cristi m
Nov 24 '18 at 20:11
I have on the sheet a form control checkbox that when checked it displays the userform, and when unchecked it removes the userform from the screen. What i'm asking is how to uncheck the checkbox when the userform is on the screen and its cancel (X) from top right corner is pressed, because after i press this button and the userform closes the checkbox is still checked.
– cristi m
Nov 24 '18 at 20:11
Why don't you show your code? I expect you have something like Userform1.show in your code. In this way you use the global default instance which yon can but shouldn't. Anyway, see below
– Storax
Nov 24 '18 at 20:56
Why don't you show your code? I expect you have something like Userform1.show in your code. In this way you use the global default instance which yon can but shouldn't. Anyway, see below
– Storax
Nov 24 '18 at 20:56
add a comment |
2 Answers
2
active
oldest
votes
Though it might not be best practice you could use the following code in the QueryClose event
Private Sub UserForm_QueryClose(Cancel As Integer _
, CloseMode As Integer)
' Prevent the form being unloaded
If CloseMode = vbFormControlMenu Then Cancel = True
' Hide the Userform
Hide
ThisWorkbook.Worksheets(1).Shapes("Checkbox1").OLEFormat.Object.Value = 0
End Sub
I assume you do not use an ActiceX-Control and the name of your checkbox is CheckBox1
A slightly better way might be to use the following code in a normal module for the checkbox instead of the above code in the userform module. In this way the form does not need to know about a checkbox which called it.
Sub Checkbox_code()
Dim f As UserForm1
Dim b As CheckBox
Set f = New UserForm1
Set b = ThisWorkbook.Worksheets(1).Shapes("Checkbox1").OLEFormat.Object
If b.Value = xlOn Then
f.Show
b.Value = xlOff
End If
End Sub
answered Nov 24 '18 at 20:56
StoraxStorax
4,2283518
add a comment |
i've solved it:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
ThisWorkbook.Worksheets("Sheet1").CheckBox1.Value = False
End If
End Sub
answered Nov 24 '18 at 21:12
cristi mcristi m
101
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%2f53460838%2funcheck-a-checkbox-if-the-x-close-button-of-the-userform-is-pressed%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
Though it might not be best practice you could use the following code in the QueryClose event
Private Sub UserForm_QueryClose(Cancel As Integer _
, CloseMode As Integer)
' Prevent the form being unloaded
If CloseMode = vbFormControlMenu Then Cancel = True
' Hide the Userform
Hide
ThisWorkbook.Worksheets(1).Shapes("Checkbox1").OLEFormat.Object.Value = 0
End Sub
I assume you do not use an ActiceX-Control and the name of your checkbox is CheckBox1
A slightly better way might be to use the following code in a normal module for the checkbox instead of the above code in the userform module. In this way the form does not need to know about a checkbox which called it.
Sub Checkbox_code()
Dim f As UserForm1
Dim b As CheckBox
Set f = New UserForm1
Set b = ThisWorkbook.Worksheets(1).Shapes("Checkbox1").OLEFormat.Object
If b.Value = xlOn Then
f.Show
b.Value = xlOff
End If
End Sub
answered Nov 24 '18 at 20:56
StoraxStorax
4,2283518
add a comment |
Though it might not be best practice you could use the following code in the QueryClose event
Private Sub UserForm_QueryClose(Cancel As Integer _
, CloseMode As Integer)
' Prevent the form being unloaded
If CloseMode = vbFormControlMenu Then Cancel = True
' Hide the Userform
Hide
ThisWorkbook.Worksheets(1).Shapes("Checkbox1").OLEFormat.Object.Value = 0
End Sub
I assume you do not use an ActiceX-Control and the name of your checkbox is CheckBox1
A slightly better way might be to use the following code in a normal module for the checkbox instead of the above code in the userform module. In this way the form does not need to know about a checkbox which called it.
Sub Checkbox_code()
Dim f As UserForm1
Dim b As CheckBox
Set f = New UserForm1
Set b = ThisWorkbook.Worksheets(1).Shapes("Checkbox1").OLEFormat.Object
If b.Value = xlOn Then
f.Show
b.Value = xlOff
End If
End Sub
answered Nov 24 '18 at 20:56
StoraxStorax
4,2283518
add a comment |
Though it might not be best practice you could use the following code in the QueryClose event
Private Sub UserForm_QueryClose(Cancel As Integer _
, CloseMode As Integer)
' Prevent the form being unloaded
If CloseMode = vbFormControlMenu Then Cancel = True
' Hide the Userform
Hide
ThisWorkbook.Worksheets(1).Shapes("Checkbox1").OLEFormat.Object.Value = 0
End Sub
I assume you do not use an ActiceX-Control and the name of your checkbox is CheckBox1
A slightly better way might be to use the following code in a normal module for the checkbox instead of the above code in the userform module. In this way the form does not need to know about a checkbox which called it.
Sub Checkbox_code()
Dim f As UserForm1
Dim b As CheckBox
Set f = New UserForm1
Set b = ThisWorkbook.Worksheets(1).Shapes("Checkbox1").OLEFormat.Object
If b.Value = xlOn Then
f.Show
b.Value = xlOff
End If
End Sub
answered Nov 24 '18 at 20:56
StoraxStorax
4,2283518
Though it might not be best practice you could use the following code in the QueryClose event
Private Sub UserForm_QueryClose(Cancel As Integer _
, CloseMode As Integer)
' Prevent the form being unloaded
If CloseMode = vbFormControlMenu Then Cancel = True
' Hide the Userform
Hide
ThisWorkbook.Worksheets(1).Shapes("Checkbox1").OLEFormat.Object.Value = 0
End Sub
I assume you do not use an ActiceX-Control and the name of your checkbox is CheckBox1
A slightly better way might be to use the following code in a normal module for the checkbox instead of the above code in the userform module. In this way the form does not need to know about a checkbox which called it.
Sub Checkbox_code()
Dim f As UserForm1
Dim b As CheckBox
Set f = New UserForm1
Set b = ThisWorkbook.Worksheets(1).Shapes("Checkbox1").OLEFormat.Object
If b.Value = xlOn Then
f.Show
b.Value = xlOff
End If
End Sub
answered Nov 24 '18 at 20:56
StoraxStorax
4,2283518
edited Nov 24 '18 at 21:03
answered Nov 24 '18 at 20:56
StoraxStorax
4,2283518
answered Nov 24 '18 at 20:56
StoraxStorax
4,2283518
answered Nov 24 '18 at 20:56
StoraxStorax
4,2283518
4,2283518
add a comment |
add a comment |
i've solved it:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
ThisWorkbook.Worksheets("Sheet1").CheckBox1.Value = False
End If
End Sub
answered Nov 24 '18 at 21:12
cristi mcristi m
101
add a comment |
i've solved it:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
ThisWorkbook.Worksheets("Sheet1").CheckBox1.Value = False
End If
End Sub
answered Nov 24 '18 at 21:12
cristi mcristi m
101
add a comment |
i've solved it:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
ThisWorkbook.Worksheets("Sheet1").CheckBox1.Value = False
End If
End Sub
answered Nov 24 '18 at 21:12
cristi mcristi m
101
i've solved it:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
ThisWorkbook.Worksheets("Sheet1").CheckBox1.Value = False
End If
End Sub
answered Nov 24 '18 at 21:12
cristi mcristi m
101
answered Nov 24 '18 at 21:12
cristi mcristi m
101
answered Nov 24 '18 at 21:12
cristi mcristi m
101
answered Nov 24 '18 at 21:12
cristi mcristi m
101
101
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%2f53460838%2funcheck-a-checkbox-if-the-x-close-button-of-the-userform-is-pressed%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
Are you asking for code that unchecks a worksheet checkbox? If so, you need to give more detail, e.g., what kind of checkbox and perhaps a sample of your code. If you already have a Close button that works and you're asking how to make the X act like the close button then take a look at the QueryClose event.
– Doug Glancy
Nov 24 '18 at 17:58
Here is an example how to use the query close event.
– Storax
Nov 24 '18 at 18:34
I have on the sheet a form control checkbox that when checked it displays the userform, and when unchecked it removes the userform from the screen. What i'm asking is how to uncheck the checkbox when the userform is on the screen and its cancel (X) from top right corner is pressed, because after i press this button and the userform closes the checkbox is still checked.
– cristi m
Nov 24 '18 at 20:11
Why don't you show your code? I expect you have something like Userform1.show in your code. In this way you use the global default instance which yon can but shouldn't. Anyway, see below
– Storax
Nov 24 '18 at 20:56