PDA

View Full Version : Aktivate/ deactivate check box



joshua1990
02-26-2018, 09:44 AM
Hey guys!


I need your support again.


It's about Userforms / ComboBox / Check Box.


I have a table with 10 columns.
Each column is a combobox


These are uploaded / initialized like this:



Dim sheetOne As Worksheet
Set sheetOne = Sheets("SheetOne")
Row = 2
While (sheetOne.Cells(Row, 2) <> "")
ComboBox_AA.AddItem (sheetOne.Cells(Row, 2))
Row = Row + 1
Wend





So I have 10 combo boxes where the user can select an entry.
Now I have a checkbox. However, this should not appear or be editable until one of 10 specific selections has been made.
For this I like to make a last column, which lists the entries.
Now I just do not know how I implement a suitable loop, which checks whether one of the entries was selected, etc.


Does anyone have an idea or even an approach?
Google could not help me yet.




I hope my words are enough.




Best regards

SamT
02-26-2018, 12:50 PM
Are these controls on a UserForm or on a Worksheet.

If these are on a Worksheet, are they Control Toolbox controls or Form controls?

Note that if they are on a UserForm, then they will be UserForm controls

Control Toolbox controls and UserForm controls are almost identical, with only subtle differences in their usage. Form controls are completely different from the other two type of controls and must be treated differently.

joshua1990
02-26-2018, 02:22 PM
Thanks for your answer!

The controls are all on a UserForm.


Best regards

SamT
02-26-2018, 03:55 PM
One way:

USerForm Code

Option Explicit

Dim SelectedEntry as String

'Same Code for each Combobox. Only the Name changes

Private Sub ComboBoxName_Change()
SelectedEntry = Me.ComboBoxName.Value
With Me.CheckBoxName
.Visible = True
.SetFocus
End With
End Sub

joshua1990
02-27-2018, 12:32 AM
Thank you very much for your reply, SamT!

COuld you please explain further?

Ok, the first part is the declaration for the variable.

Is the second part an additional part for the code above?
How can I choose one of the defined values/ entrys to enable the check box?

SamT
02-27-2018, 09:49 AM
How can I choose one of the defined values/ entrys to enable the check box?
I am assuming that all values in all Combo boxes are valid to enable the Check box.


Is the second part an additional part for the code above?
That is the code I would put in all ComboBox Change Event Subs.

joshua1990
02-27-2018, 10:11 AM
[QUOTE=SamT;377122]I am assuming that all values in all Combo boxes are valid to enable the Check box.

Thanks for your reply!
Nope, not every value in all Comboboxes are valid to enable the Check box.
Only the Values from Column I:I are able/ valid, to activate/ enable the check box.
A part of the values is also in the combo boxes.


Example:
Combobox; Values= Column 2, row till end.
Choose Value= "***"

If this is also in Column I:I, then the check box should be disabled.

Do you need a more detailed explanation?

Here is also an draft:
21711

SamT
02-27-2018, 02:47 PM
Only the Values from Column I:I are able/ valid... If this is also in Column I:I, then the check box should be disabled.

?

You can select which column in a Combobox can be used.

Dim Valids as Variant
Valids = Intersect(Sheets("SomeSheet").UsedRange, Sheets("SomeSheet").Columns("I"))


For the first situation you described
For i = Lbound(Valids) to Ubound(Valids)
If Selection= Valids(i) then CheckBox.Visible = true
Continue with rest of code

joshua1990
02-28-2018, 12:22 AM
Hey SamT,
Thanks!

How can I add the Value (Valids) to the Combobox?
This approach doesn't works:


Dim Valids As Variant


Valids = Intersect(Sheets("TblOne").UsedRange, Sheets("TblOne").Columns("I"))
ComboBox_A.Value = Valids


Is it critical, that every column have an individual UsedRange/ row quantity?

SamT
02-28-2018, 02:29 AM
Valids is an array, a "list." It contains every value in Column I. You need to pick one value. For instance the third value in Column I, in the Valids Array would be, in this particular situation, Valids(3)


Is it critical, that every column have an individual UsedRange/ row quantity? Is it critical that the sky is full of stars? Nope, it just is what it is.