Mixing
Loop to extract value from checkbox
A form I am working with has 10 checkboxes, with values 1 through 10, used to answer a multiple choice question.
Multiple values are technically possible (clicking on multiple boxes), but they are not allowed (while filling, only one value should be given). I cannot modify this form so I have to work with this setup.
I need to extract the given choice and paste it into a different worksheet.
Using this question I can extract the value of every single checkbox and develop a IF Loop.
If ExtractionSheet.Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 1
ElseIf ExtractionSheet.Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 2
ElseIf ExtractionSheet.Shapes("Check Box 3").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 3
...
However, this does not look very efficient (I have 3 sets of 1-10 checkboxes per form and 100+ forms). Given the setup, I cannot figure out a better way to do it.
How can I improve the extraction without using an IF loop?
EDIT A better description of the form, following comments
This is a simple excel worksheet, in which 3 groups of 10 check box elements were pasted.
Each form/worksheet relates to a single item. During the assessment, for each item we will assign a value between 1 and 10 to Property 1 (first 10 check boxes), a value between 1 and 10 to Property 2 (second 10 check boxes) and a value between 1 and 10 to Property 3 (third 10 check boxes).
I will do the filling (physically clicking the box) while in front of the client who is giving me data to fill it. The possibility of clicking multiple boxes naturally exists; I do not think it will be critical because many people will be looking at the screen while I do it, but I can always add a check later on.
excel vba excel-vba checkbox
asked Jan 2 at 9:23
laureapresalaureapresa
1499
|
show 2 more comments
A form I am working with has 10 checkboxes, with values 1 through 10, used to answer a multiple choice question.
Multiple values are technically possible (clicking on multiple boxes), but they are not allowed (while filling, only one value should be given). I cannot modify this form so I have to work with this setup.
I need to extract the given choice and paste it into a different worksheet.
Using this question I can extract the value of every single checkbox and develop a IF Loop.
If ExtractionSheet.Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 1
ElseIf ExtractionSheet.Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 2
ElseIf ExtractionSheet.Shapes("Check Box 3").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 3
...
However, this does not look very efficient (I have 3 sets of 1-10 checkboxes per form and 100+ forms). Given the setup, I cannot figure out a better way to do it.
How can I improve the extraction without using an IF loop?
EDIT A better description of the form, following comments
This is a simple excel worksheet, in which 3 groups of 10 check box elements were pasted.
Each form/worksheet relates to a single item. During the assessment, for each item we will assign a value between 1 and 10 to Property 1 (first 10 check boxes), a value between 1 and 10 to Property 2 (second 10 check boxes) and a value between 1 and 10 to Property 3 (third 10 check boxes).
I will do the filling (physically clicking the box) while in front of the client who is giving me data to fill it. The possibility of clicking multiple boxes naturally exists; I do not think it will be critical because many people will be looking at the screen while I do it, but I can always add a check later on.
excel vba excel-vba checkbox
asked Jan 2 at 9:23
laureapresalaureapresa
1499
2
AnIfis not a loop - It is a statement. AFororDois used for a loop
– Tom
Jan 2 at 9:30
Are these on a user form?
– SJR
Jan 2 at 9:34
Could you add an event on the form so that if another is selected the previous one is auto unselected?
– Tom
Jan 2 at 9:38
@Tom No. But I could check during the extraction if there are two or more variables selected and issue a warning.
– laureapresa
Jan 2 at 9:45
@SJR Yes, they are on an Excel user form that my client provided and that will fill out with me. I will actually do the filling (box clicking etc.)
– laureapresa
Jan 2 at 9:47
|
show 2 more comments
A form I am working with has 10 checkboxes, with values 1 through 10, used to answer a multiple choice question.
Multiple values are technically possible (clicking on multiple boxes), but they are not allowed (while filling, only one value should be given). I cannot modify this form so I have to work with this setup.
I need to extract the given choice and paste it into a different worksheet.
Using this question I can extract the value of every single checkbox and develop a IF Loop.
If ExtractionSheet.Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 1
ElseIf ExtractionSheet.Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 2
ElseIf ExtractionSheet.Shapes("Check Box 3").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 3
...
However, this does not look very efficient (I have 3 sets of 1-10 checkboxes per form and 100+ forms). Given the setup, I cannot figure out a better way to do it.
How can I improve the extraction without using an IF loop?
EDIT A better description of the form, following comments
This is a simple excel worksheet, in which 3 groups of 10 check box elements were pasted.
Each form/worksheet relates to a single item. During the assessment, for each item we will assign a value between 1 and 10 to Property 1 (first 10 check boxes), a value between 1 and 10 to Property 2 (second 10 check boxes) and a value between 1 and 10 to Property 3 (third 10 check boxes).
I will do the filling (physically clicking the box) while in front of the client who is giving me data to fill it. The possibility of clicking multiple boxes naturally exists; I do not think it will be critical because many people will be looking at the screen while I do it, but I can always add a check later on.
excel vba excel-vba checkbox
asked Jan 2 at 9:23
laureapresalaureapresa
1499
A form I am working with has 10 checkboxes, with values 1 through 10, used to answer a multiple choice question.
Multiple values are technically possible (clicking on multiple boxes), but they are not allowed (while filling, only one value should be given). I cannot modify this form so I have to work with this setup.
I need to extract the given choice and paste it into a different worksheet.
Using this question I can extract the value of every single checkbox and develop a IF Loop.
If ExtractionSheet.Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 1
ElseIf ExtractionSheet.Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 2
ElseIf ExtractionSheet.Shapes("Check Box 3").OLEFormat.Object.Value = 1 Then
Database.Cells(5, 9).Value = 3
...
However, this does not look very efficient (I have 3 sets of 1-10 checkboxes per form and 100+ forms). Given the setup, I cannot figure out a better way to do it.
How can I improve the extraction without using an IF loop?
EDIT A better description of the form, following comments
This is a simple excel worksheet, in which 3 groups of 10 check box elements were pasted.
Each form/worksheet relates to a single item. During the assessment, for each item we will assign a value between 1 and 10 to Property 1 (first 10 check boxes), a value between 1 and 10 to Property 2 (second 10 check boxes) and a value between 1 and 10 to Property 3 (third 10 check boxes).
I will do the filling (physically clicking the box) while in front of the client who is giving me data to fill it. The possibility of clicking multiple boxes naturally exists; I do not think it will be critical because many people will be looking at the screen while I do it, but I can always add a check later on.
excel vba excel-vba checkbox
excel vba excel-vba checkbox
asked Jan 2 at 9:23
laureapresalaureapresa
1499
asked Jan 2 at 9:23
laureapresalaureapresa
1499
edited Jan 2 at 10:02
laureapresa
asked Jan 2 at 9:23
laureapresalaureapresa
1499
asked Jan 2 at 9:23
laureapresalaureapresa
1499
asked Jan 2 at 9:23
laureapresalaureapresa
1499
1499
2
AnIfis not a loop - It is a statement. AFororDois used for a loop
– Tom
Jan 2 at 9:30
Are these on a user form?
– SJR
Jan 2 at 9:34
Could you add an event on the form so that if another is selected the previous one is auto unselected?
– Tom
Jan 2 at 9:38
@Tom No. But I could check during the extraction if there are two or more variables selected and issue a warning.
– laureapresa
Jan 2 at 9:45
@SJR Yes, they are on an Excel user form that my client provided and that will fill out with me. I will actually do the filling (box clicking etc.)
– laureapresa
Jan 2 at 9:47
|
show 2 more comments
2
AnIfis not a loop - It is a statement. AFororDois used for a loop
– Tom
Jan 2 at 9:30
Are these on a user form?
– SJR
Jan 2 at 9:34
Could you add an event on the form so that if another is selected the previous one is auto unselected?
– Tom
Jan 2 at 9:38
@Tom No. But I could check during the extraction if there are two or more variables selected and issue a warning.
– laureapresa
Jan 2 at 9:45
@SJR Yes, they are on an Excel user form that my client provided and that will fill out with me. I will actually do the filling (box clicking etc.)
– laureapresa
Jan 2 at 9:47
2
2
An
If is not a loop - It is a statement. A For or Do is used for a loop– Tom
Jan 2 at 9:30
An
If is not a loop - It is a statement. A For or Do is used for a loop– Tom
Jan 2 at 9:30
Are these on a user form?
– SJR
Jan 2 at 9:34
Are these on a user form?
– SJR
Jan 2 at 9:34
Could you add an event on the form so that if another is selected the previous one is auto unselected?
– Tom
Jan 2 at 9:38
Could you add an event on the form so that if another is selected the previous one is auto unselected?
– Tom
Jan 2 at 9:38
@Tom No. But I could check during the extraction if there are two or more variables selected and issue a warning.
– laureapresa
Jan 2 at 9:45
@Tom No. But I could check during the extraction if there are two or more variables selected and issue a warning.
– laureapresa
Jan 2 at 9:45
@SJR Yes, they are on an Excel user form that my client provided and that will fill out with me. I will actually do the filling (box clicking etc.)
– laureapresa
Jan 2 at 9:47
@SJR Yes, they are on an Excel user form that my client provided and that will fill out with me. I will actually do the filling (box clicking etc.)
– laureapresa
Jan 2 at 9:47
|
show 2 more comments
1 Answer
1
active
oldest
votes
Updated after comments:
I have used the following naming convention for the checkboxes (Using just e.g. A1 is a cell reference and could cause problems)
ChkBox_A1
Where the first part denotes that it is a checkbox (ChkBox), second the group A and third the position 1. With this naming convention and how the code is currently written you will be able to have a maximum of 26 groups (i.e. one for every letter of the alphabet)
I use the immediate window for the results which can be accessed in the VBA editor by going to View->Immediate Window or Ctrl+G
This code will handle single select per group. i.e. If a checkbox is selected in the group it will un-select all other ones
For a worksheet
This code goes in the worksheet object
Replace all of the click statements (e.g. ChkBox_A1_Click() with reference to your own. This can be easily done by calling the GenerateChkBoxClickStmt sub and copying and pasting the output in the immediate window into your code (replacing my ones)
Option Explicit
Dim ChkBoxChange As Boolean
Private Sub ChkBox_A1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub UnselectPreviousChkBox(selected As Object)
Dim ChkBox As OLEObject
ChkBoxChange = True
For Each ChkBox In Me.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
ChkBox.Object.Value = False
End If
End If
Next ChkBox
ChkBoxChange = False
End Sub
Private Sub GenerateChkBoxClickStmt()
Dim ChkBox As OLEObject
' Copy and paste output to immediate window into here
For Each ChkBox In Me.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
Debug.Print "End Sub"
End If
Next ChkBox
End Sub
Producing the following:
This code goes into a Module
Option Explicit
Private Function GetChkBoxValues(ChkBoxGroup As Variant) As Long
Dim ChkBox As OLEObject
' Update with your sheet reference
For Each ChkBox In ActiveSheet.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = ChkBoxGroup Then
GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
Exit For
End If
End If
Next ChkBox
End Function
Public Sub GetSelectedChkBoxes()
Dim ChkBoxGroups() As Variant
Dim Grp As Variant
ChkBoxGroups = Array("A", "B", "C")
For Each Grp In ChkBoxGroups
Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
Next Grp
End Sub
By running the GetSelectedChkBoxes the code will output to the immediate window:
For a UserForm
Similarly the statements for the click events can be generated by uncommenting the line in the Userform_Initalize sub
Option Explicit
Dim ChkBoxChange As Boolean
Private Function GetChkBoxValues(Group As Variant) As Long
Dim ChkBox As Control
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = Group Then
GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
Exit For
End If
End If
Next ChkBox
End Function
Private Sub UnselectPreviousChkBox(selected As Control)
Dim ChkBox As Control
ChkBoxChange = True
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
ChkBox.Value = False
End If
End If
Next ChkBox
ChkBoxChange = False
End Sub
Private Sub ChkBox_A1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub userform_initialize()
' Comment out once written
' GenerateChkBoxClickStmt
End Sub
Private Sub UserForm_Terminate()
Dim ChkBoxGroups() As Variant
Dim Grp As Variant
ChkBoxGroups = Array("A", "B", "C")
For Each Grp In ChkBoxGroups
Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
Next Grp
End Sub
Private Sub GenerateChkBoxClickStmt()
Dim ChkBox As Control
' Copy and paste output to immediate window into here
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
Debug.Print "End Sub"
End If
Next ChkBox
End Sub
Producing:
and outputting the following on exit:
answered Jan 2 at 9:42
TomTom
6,56511843
Upvoted, but unfortunately they do not correspond.
– laureapresa
Jan 2 at 9:45
Is there any logic between the two then?
– Tom
Jan 2 at 9:47
Not at all. I could rename them to a progressive number and then apply your solution. How would I approach the fact that I have 3 groups of 10 check boxes per form?
– laureapresa
Jan 2 at 9:51
1
How do you group them together? I'd recommend naming them with some sort of logic. If there is nothing grouping them, you could also apply a naming convention to depict which group the checkbox is related to. e.g. "Group A Checkbox 1" etc.
– Tom
Jan 2 at 9:58
I can rename my check boxes as A1, A2... A10, B1, B2...B10 and so on.How would I edit your code to run only for one group?
– laureapresa
Jan 2 at 10:17
|
show 1 more 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%2f54003873%2floop-to-extract-value-from-checkbox%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
Updated after comments:
I have used the following naming convention for the checkboxes (Using just e.g. A1 is a cell reference and could cause problems)
ChkBox_A1
Where the first part denotes that it is a checkbox (ChkBox), second the group A and third the position 1. With this naming convention and how the code is currently written you will be able to have a maximum of 26 groups (i.e. one for every letter of the alphabet)
I use the immediate window for the results which can be accessed in the VBA editor by going to View->Immediate Window or Ctrl+G
This code will handle single select per group. i.e. If a checkbox is selected in the group it will un-select all other ones
For a worksheet
This code goes in the worksheet object
Replace all of the click statements (e.g. ChkBox_A1_Click() with reference to your own. This can be easily done by calling the GenerateChkBoxClickStmt sub and copying and pasting the output in the immediate window into your code (replacing my ones)
Option Explicit
Dim ChkBoxChange As Boolean
Private Sub ChkBox_A1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub UnselectPreviousChkBox(selected As Object)
Dim ChkBox As OLEObject
ChkBoxChange = True
For Each ChkBox In Me.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
ChkBox.Object.Value = False
End If
End If
Next ChkBox
ChkBoxChange = False
End Sub
Private Sub GenerateChkBoxClickStmt()
Dim ChkBox As OLEObject
' Copy and paste output to immediate window into here
For Each ChkBox In Me.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
Debug.Print "End Sub"
End If
Next ChkBox
End Sub
Producing the following:
This code goes into a Module
Option Explicit
Private Function GetChkBoxValues(ChkBoxGroup As Variant) As Long
Dim ChkBox As OLEObject
' Update with your sheet reference
For Each ChkBox In ActiveSheet.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = ChkBoxGroup Then
GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
Exit For
End If
End If
Next ChkBox
End Function
Public Sub GetSelectedChkBoxes()
Dim ChkBoxGroups() As Variant
Dim Grp As Variant
ChkBoxGroups = Array("A", "B", "C")
For Each Grp In ChkBoxGroups
Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
Next Grp
End Sub
By running the GetSelectedChkBoxes the code will output to the immediate window:
For a UserForm
Similarly the statements for the click events can be generated by uncommenting the line in the Userform_Initalize sub
Option Explicit
Dim ChkBoxChange As Boolean
Private Function GetChkBoxValues(Group As Variant) As Long
Dim ChkBox As Control
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = Group Then
GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
Exit For
End If
End If
Next ChkBox
End Function
Private Sub UnselectPreviousChkBox(selected As Control)
Dim ChkBox As Control
ChkBoxChange = True
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
ChkBox.Value = False
End If
End If
Next ChkBox
ChkBoxChange = False
End Sub
Private Sub ChkBox_A1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub userform_initialize()
' Comment out once written
' GenerateChkBoxClickStmt
End Sub
Private Sub UserForm_Terminate()
Dim ChkBoxGroups() As Variant
Dim Grp As Variant
ChkBoxGroups = Array("A", "B", "C")
For Each Grp In ChkBoxGroups
Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
Next Grp
End Sub
Private Sub GenerateChkBoxClickStmt()
Dim ChkBox As Control
' Copy and paste output to immediate window into here
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
Debug.Print "End Sub"
End If
Next ChkBox
End Sub
Producing:
and outputting the following on exit:
answered Jan 2 at 9:42
TomTom
6,56511843
Upvoted, but unfortunately they do not correspond.
– laureapresa
Jan 2 at 9:45
Is there any logic between the two then?
– Tom
Jan 2 at 9:47
Not at all. I could rename them to a progressive number and then apply your solution. How would I approach the fact that I have 3 groups of 10 check boxes per form?
– laureapresa
Jan 2 at 9:51
1
How do you group them together? I'd recommend naming them with some sort of logic. If there is nothing grouping them, you could also apply a naming convention to depict which group the checkbox is related to. e.g. "Group A Checkbox 1" etc.
– Tom
Jan 2 at 9:58
I can rename my check boxes as A1, A2... A10, B1, B2...B10 and so on.How would I edit your code to run only for one group?
– laureapresa
Jan 2 at 10:17
|
show 1 more comment
Updated after comments:
I have used the following naming convention for the checkboxes (Using just e.g. A1 is a cell reference and could cause problems)
ChkBox_A1
Where the first part denotes that it is a checkbox (ChkBox), second the group A and third the position 1. With this naming convention and how the code is currently written you will be able to have a maximum of 26 groups (i.e. one for every letter of the alphabet)
I use the immediate window for the results which can be accessed in the VBA editor by going to View->Immediate Window or Ctrl+G
This code will handle single select per group. i.e. If a checkbox is selected in the group it will un-select all other ones
For a worksheet
This code goes in the worksheet object
Replace all of the click statements (e.g. ChkBox_A1_Click() with reference to your own. This can be easily done by calling the GenerateChkBoxClickStmt sub and copying and pasting the output in the immediate window into your code (replacing my ones)
Option Explicit
Dim ChkBoxChange As Boolean
Private Sub ChkBox_A1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub UnselectPreviousChkBox(selected As Object)
Dim ChkBox As OLEObject
ChkBoxChange = True
For Each ChkBox In Me.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
ChkBox.Object.Value = False
End If
End If
Next ChkBox
ChkBoxChange = False
End Sub
Private Sub GenerateChkBoxClickStmt()
Dim ChkBox As OLEObject
' Copy and paste output to immediate window into here
For Each ChkBox In Me.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
Debug.Print "End Sub"
End If
Next ChkBox
End Sub
Producing the following:
This code goes into a Module
Option Explicit
Private Function GetChkBoxValues(ChkBoxGroup As Variant) As Long
Dim ChkBox As OLEObject
' Update with your sheet reference
For Each ChkBox In ActiveSheet.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = ChkBoxGroup Then
GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
Exit For
End If
End If
Next ChkBox
End Function
Public Sub GetSelectedChkBoxes()
Dim ChkBoxGroups() As Variant
Dim Grp As Variant
ChkBoxGroups = Array("A", "B", "C")
For Each Grp In ChkBoxGroups
Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
Next Grp
End Sub
By running the GetSelectedChkBoxes the code will output to the immediate window:
For a UserForm
Similarly the statements for the click events can be generated by uncommenting the line in the Userform_Initalize sub
Option Explicit
Dim ChkBoxChange As Boolean
Private Function GetChkBoxValues(Group As Variant) As Long
Dim ChkBox As Control
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = Group Then
GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
Exit For
End If
End If
Next ChkBox
End Function
Private Sub UnselectPreviousChkBox(selected As Control)
Dim ChkBox As Control
ChkBoxChange = True
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
ChkBox.Value = False
End If
End If
Next ChkBox
ChkBoxChange = False
End Sub
Private Sub ChkBox_A1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub userform_initialize()
' Comment out once written
' GenerateChkBoxClickStmt
End Sub
Private Sub UserForm_Terminate()
Dim ChkBoxGroups() As Variant
Dim Grp As Variant
ChkBoxGroups = Array("A", "B", "C")
For Each Grp In ChkBoxGroups
Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
Next Grp
End Sub
Private Sub GenerateChkBoxClickStmt()
Dim ChkBox As Control
' Copy and paste output to immediate window into here
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
Debug.Print "End Sub"
End If
Next ChkBox
End Sub
Producing:
and outputting the following on exit:
answered Jan 2 at 9:42
TomTom
6,56511843
Upvoted, but unfortunately they do not correspond.
– laureapresa
Jan 2 at 9:45
Is there any logic between the two then?
– Tom
Jan 2 at 9:47
Not at all. I could rename them to a progressive number and then apply your solution. How would I approach the fact that I have 3 groups of 10 check boxes per form?
– laureapresa
Jan 2 at 9:51
1
How do you group them together? I'd recommend naming them with some sort of logic. If there is nothing grouping them, you could also apply a naming convention to depict which group the checkbox is related to. e.g. "Group A Checkbox 1" etc.
– Tom
Jan 2 at 9:58
I can rename my check boxes as A1, A2... A10, B1, B2...B10 and so on.How would I edit your code to run only for one group?
– laureapresa
Jan 2 at 10:17
|
show 1 more comment
Updated after comments:
I have used the following naming convention for the checkboxes (Using just e.g. A1 is a cell reference and could cause problems)
ChkBox_A1
Where the first part denotes that it is a checkbox (ChkBox), second the group A and third the position 1. With this naming convention and how the code is currently written you will be able to have a maximum of 26 groups (i.e. one for every letter of the alphabet)
I use the immediate window for the results which can be accessed in the VBA editor by going to View->Immediate Window or Ctrl+G
This code will handle single select per group. i.e. If a checkbox is selected in the group it will un-select all other ones
For a worksheet
This code goes in the worksheet object
Replace all of the click statements (e.g. ChkBox_A1_Click() with reference to your own. This can be easily done by calling the GenerateChkBoxClickStmt sub and copying and pasting the output in the immediate window into your code (replacing my ones)
Option Explicit
Dim ChkBoxChange As Boolean
Private Sub ChkBox_A1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub UnselectPreviousChkBox(selected As Object)
Dim ChkBox As OLEObject
ChkBoxChange = True
For Each ChkBox In Me.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
ChkBox.Object.Value = False
End If
End If
Next ChkBox
ChkBoxChange = False
End Sub
Private Sub GenerateChkBoxClickStmt()
Dim ChkBox As OLEObject
' Copy and paste output to immediate window into here
For Each ChkBox In Me.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
Debug.Print "End Sub"
End If
Next ChkBox
End Sub
Producing the following:
This code goes into a Module
Option Explicit
Private Function GetChkBoxValues(ChkBoxGroup As Variant) As Long
Dim ChkBox As OLEObject
' Update with your sheet reference
For Each ChkBox In ActiveSheet.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = ChkBoxGroup Then
GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
Exit For
End If
End If
Next ChkBox
End Function
Public Sub GetSelectedChkBoxes()
Dim ChkBoxGroups() As Variant
Dim Grp As Variant
ChkBoxGroups = Array("A", "B", "C")
For Each Grp In ChkBoxGroups
Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
Next Grp
End Sub
By running the GetSelectedChkBoxes the code will output to the immediate window:
For a UserForm
Similarly the statements for the click events can be generated by uncommenting the line in the Userform_Initalize sub
Option Explicit
Dim ChkBoxChange As Boolean
Private Function GetChkBoxValues(Group As Variant) As Long
Dim ChkBox As Control
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = Group Then
GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
Exit For
End If
End If
Next ChkBox
End Function
Private Sub UnselectPreviousChkBox(selected As Control)
Dim ChkBox As Control
ChkBoxChange = True
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
ChkBox.Value = False
End If
End If
Next ChkBox
ChkBoxChange = False
End Sub
Private Sub ChkBox_A1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub userform_initialize()
' Comment out once written
' GenerateChkBoxClickStmt
End Sub
Private Sub UserForm_Terminate()
Dim ChkBoxGroups() As Variant
Dim Grp As Variant
ChkBoxGroups = Array("A", "B", "C")
For Each Grp In ChkBoxGroups
Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
Next Grp
End Sub
Private Sub GenerateChkBoxClickStmt()
Dim ChkBox As Control
' Copy and paste output to immediate window into here
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
Debug.Print "End Sub"
End If
Next ChkBox
End Sub
Producing:
and outputting the following on exit:
answered Jan 2 at 9:42
TomTom
6,56511843
Updated after comments:
I have used the following naming convention for the checkboxes (Using just e.g. A1 is a cell reference and could cause problems)
ChkBox_A1
Where the first part denotes that it is a checkbox (ChkBox), second the group A and third the position 1. With this naming convention and how the code is currently written you will be able to have a maximum of 26 groups (i.e. one for every letter of the alphabet)
I use the immediate window for the results which can be accessed in the VBA editor by going to View->Immediate Window or Ctrl+G
This code will handle single select per group. i.e. If a checkbox is selected in the group it will un-select all other ones
For a worksheet
This code goes in the worksheet object
Replace all of the click statements (e.g. ChkBox_A1_Click() with reference to your own. This can be easily done by calling the GenerateChkBoxClickStmt sub and copying and pasting the output in the immediate window into your code (replacing my ones)
Option Explicit
Dim ChkBoxChange As Boolean
Private Sub ChkBox_A1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub UnselectPreviousChkBox(selected As Object)
Dim ChkBox As OLEObject
ChkBoxChange = True
For Each ChkBox In Me.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
ChkBox.Object.Value = False
End If
End If
Next ChkBox
ChkBoxChange = False
End Sub
Private Sub GenerateChkBoxClickStmt()
Dim ChkBox As OLEObject
' Copy and paste output to immediate window into here
For Each ChkBox In Me.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
Debug.Print "End Sub"
End If
Next ChkBox
End Sub
Producing the following:
This code goes into a Module
Option Explicit
Private Function GetChkBoxValues(ChkBoxGroup As Variant) As Long
Dim ChkBox As OLEObject
' Update with your sheet reference
For Each ChkBox In ActiveSheet.OLEObjects
If ChkBox.progID = "Forms.CheckBox.1" Then
If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = ChkBoxGroup Then
GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
Exit For
End If
End If
Next ChkBox
End Function
Public Sub GetSelectedChkBoxes()
Dim ChkBoxGroups() As Variant
Dim Grp As Variant
ChkBoxGroups = Array("A", "B", "C")
For Each Grp In ChkBoxGroups
Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
Next Grp
End Sub
By running the GetSelectedChkBoxes the code will output to the immediate window:
For a UserForm
Similarly the statements for the click events can be generated by uncommenting the line in the Userform_Initalize sub
Option Explicit
Dim ChkBoxChange As Boolean
Private Function GetChkBoxValues(Group As Variant) As Long
Dim ChkBox As Control
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = Group Then
GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
Exit For
End If
End If
Next ChkBox
End Function
Private Sub UnselectPreviousChkBox(selected As Control)
Dim ChkBox As Control
ChkBoxChange = True
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
ChkBox.Value = False
End If
End If
Next ChkBox
ChkBoxChange = False
End Sub
Private Sub ChkBox_A1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub userform_initialize()
' Comment out once written
' GenerateChkBoxClickStmt
End Sub
Private Sub UserForm_Terminate()
Dim ChkBoxGroups() As Variant
Dim Grp As Variant
ChkBoxGroups = Array("A", "B", "C")
For Each Grp In ChkBoxGroups
Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
Next Grp
End Sub
Private Sub GenerateChkBoxClickStmt()
Dim ChkBox As Control
' Copy and paste output to immediate window into here
For Each ChkBox In Me.Controls
If TypeName(ChkBox) = "CheckBox" Then
Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
Debug.Print "End Sub"
End If
Next ChkBox
End Sub
Producing:
and outputting the following on exit:
answered Jan 2 at 9:42
TomTom
6,56511843
edited Jan 2 at 12:09
answered Jan 2 at 9:42
TomTom
6,56511843
answered Jan 2 at 9:42
TomTom
6,56511843
answered Jan 2 at 9:42
TomTom
6,56511843
6,56511843
Upvoted, but unfortunately they do not correspond.
– laureapresa
Jan 2 at 9:45
Is there any logic between the two then?
– Tom
Jan 2 at 9:47
Not at all. I could rename them to a progressive number and then apply your solution. How would I approach the fact that I have 3 groups of 10 check boxes per form?
– laureapresa
Jan 2 at 9:51
1
How do you group them together? I'd recommend naming them with some sort of logic. If there is nothing grouping them, you could also apply a naming convention to depict which group the checkbox is related to. e.g. "Group A Checkbox 1" etc.
– Tom
Jan 2 at 9:58
I can rename my check boxes as A1, A2... A10, B1, B2...B10 and so on.How would I edit your code to run only for one group?
– laureapresa
Jan 2 at 10:17
|
show 1 more comment
Upvoted, but unfortunately they do not correspond.
– laureapresa
Jan 2 at 9:45
Is there any logic between the two then?
– Tom
Jan 2 at 9:47
Not at all. I could rename them to a progressive number and then apply your solution. How would I approach the fact that I have 3 groups of 10 check boxes per form?
– laureapresa
Jan 2 at 9:51
1
How do you group them together? I'd recommend naming them with some sort of logic. If there is nothing grouping them, you could also apply a naming convention to depict which group the checkbox is related to. e.g. "Group A Checkbox 1" etc.
– Tom
Jan 2 at 9:58
I can rename my check boxes as A1, A2... A10, B1, B2...B10 and so on.How would I edit your code to run only for one group?
– laureapresa
Jan 2 at 10:17
Upvoted, but unfortunately they do not correspond.
– laureapresa
Jan 2 at 9:45
Upvoted, but unfortunately they do not correspond.
– laureapresa
Jan 2 at 9:45
Is there any logic between the two then?
– Tom
Jan 2 at 9:47
Is there any logic between the two then?
– Tom
Jan 2 at 9:47
Not at all. I could rename them to a progressive number and then apply your solution. How would I approach the fact that I have 3 groups of 10 check boxes per form?
– laureapresa
Jan 2 at 9:51
Not at all. I could rename them to a progressive number and then apply your solution. How would I approach the fact that I have 3 groups of 10 check boxes per form?
– laureapresa
Jan 2 at 9:51
1
1
How do you group them together? I'd recommend naming them with some sort of logic. If there is nothing grouping them, you could also apply a naming convention to depict which group the checkbox is related to. e.g. "Group A Checkbox 1" etc.
– Tom
Jan 2 at 9:58
How do you group them together? I'd recommend naming them with some sort of logic. If there is nothing grouping them, you could also apply a naming convention to depict which group the checkbox is related to. e.g. "Group A Checkbox 1" etc.
– Tom
Jan 2 at 9:58
I can rename my check boxes as A1, A2... A10, B1, B2...B10 and so on.How would I edit your code to run only for one group?
– laureapresa
Jan 2 at 10:17
I can rename my check boxes as A1, A2... A10, B1, B2...B10 and so on.How would I edit your code to run only for one group?
– laureapresa
Jan 2 at 10:17
|
show 1 more 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%2f54003873%2floop-to-extract-value-from-checkbox%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
An
Ifis not a loop - It is a statement. AFororDois used for a loop– Tom
Jan 2 at 9:30
Are these on a user form?
– SJR
Jan 2 at 9:34
Could you add an event on the form so that if another is selected the previous one is auto unselected?
– Tom
Jan 2 at 9:38
@Tom No. But I could check during the extraction if there are two or more variables selected and issue a warning.
– laureapresa
Jan 2 at 9:45
@SJR Yes, they are on an Excel user form that my client provided and that will fill out with me. I will actually do the filling (box clicking etc.)
– laureapresa
Jan 2 at 9:47