Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkboxes and Captions Not Able to Return Value
Hi all.
I am currently working on a user form and want a cell to return a value based on the status of the checkbox(true or false). However, I have 30 Checkboxes to loop through (10 sets of Yes, No and NA) so I was wondering if I could do this dynamically. Here is the code I have so far, but it keeps getting stuck on the Controls("CheckBoxYes" & i).Caption: Private Sub CommandButton1_Click() Dim emptyRow As Long Dim CriteriaAnswerYes As Control Dim CriteriaAnswerNo As Control Dim CriteriaAnswerNA As Control Dim i As Long For i = 1 To 10 Set CriteriaAnswerYes = Controls("CheckBoxYes" & i) Set CriteriaAnswerNo = Controls("CheckBoxNo" & i) Set CriteriaAnswerNA = Controls("CheckBoxNA" & i) If CriteriaAnswerYes.Value = True And CriteriaAnswerNo.Value = True Then MsgBox "Please only select one value from the checkboxes!", vbCritical Exit Sub ElseIf CriteriaAnswerYes.Value = True And CriteriaAnswerNA.Value = True Then MsgBox "Please only select one value from the checkboxes!", vbCritical Exit Sub ElseIf CriteriaAnswerNo.Value = True And CriteriaAnswerNA.Value = True Then MsgBox "Please only select one value from the checkboxes!", vbCritical Exit Sub Else If CriteriaAnswerYes.Value = True Then Cells(emptyRow, i + 6).Value = CriteriaAnswerYes.Caption ElseIf CriteriaAnswerNo.Value = True Then Cells(emptyRow, i + 6).Value = CriteriaAnswerNo.Caption ElseIf CriteriaAnswerNA.Value = True Then Cells(emptyRow, i + 6).Value = CriteriaAnswerNA.Caption End If End If Next i End Sub Thank you in advance. Cheers. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkboxes and Captions Not Able to Return Value
Perhaps you could use 3 listbox controls and name them appropriately...
lstYes lstNo lstNa ...and set MultiSelect and ListStyle to 1. Then all you need to do is query each listbox listitem's value. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return value(s) based on selected checkboxes | Excel Worksheet Functions | |||
Return value based on checkboxes selected -- IF() LOOKUP() | Excel Worksheet Functions | |||
checkboxes' return value | Excel Programming | |||
Help:Programmatically Adding Checkboxes with Captions | Excel Programming | |||
"Reading" Excel VBA checkboxes and their captions | Excel Programming |