PDA

View Full Version : Need one Sub for all CheckBoxes.



Learner123
12-03-2016, 10:55 AM
Hi,

I have run into a dead end and was hoping to receive some assistance....

The attached file produces a userform that utilizes code which dynamically adds checkboxes.

17748

When macro is initiated, the checkboxes appear and are checked off if their corresponding cells in column BA are tagged as True. This code executes the above:



Private Sub UserForm_Initialize()
Dim Rng As Integer
Dim N As Long 'N tab count
Dim tck As MSForms.CheckBox
Rng = 1

For N = 1 To ActiveWorkbook.Sheets.Count

Set tck = UserForm1.Controls.Add("Forms.CheckBox.1", Sheets(N).Name, True) 'adds a checkbox for each tab that exits
tck.Caption = Sheets(N).Name 'names checkbox caption
Range("BB1").offset(Rng, 0).Value = tck.Name 'stores tab name to Column BB
tck.Value = Range("BA1").offset(Rng, 0).Value 'selects/unselects dynamic checkboxes based on Column BA value
tck.Top = offset 'aligns checkbox
offset = offset + 15 'aligns checkbox
Rng = Rng + 1
Next N


End Sub

In addition to having the checkboxes populate with existing column BA entries, I am trying to do the reverse. I am hoping to feed specific cells within column BA when checkboxes are clicked (Feeding corresponding cells with True or False).

I believe I am supposed to utilize a class module but not quite sure how to implement.

I am desperate for some guidance - - please help!!

Thank you in advance

SamT
12-03-2016, 11:11 AM
Just one idea

Option Explicit

Dim Tcks As Collection


Private Sub UserForm_Initialize()
Dim Rng As Integer
Dim N As Long 'N tab count
Dim tck As MSForms.CheckBox
Rng = 1

For N = 1 To ActiveWorkbook.Sheets.Count
Set tck = UserForm1.Controls.Add("Forms.CheckBox.1", Sheets(N).Name, True) 'adds a checkbox for each tab that exits
tck.Caption = Sheets(N).Name 'names checkbox caption
Tcks.Add tck, tck.Name

Range("BB1").Offset(Rng, 0).Value = tck.Name 'stores tab name to Column BB
tck.Value = Range("BA1").Offset(Rng, 0).Value 'selects/unselects dynamic checkboxes based on Column BA value
tck.Top = Offset 'aligns checkbox
Offset = Offset + 15 'aligns checkbox
Rng = Rng + 1
Next N
End Sub

Sub X()
Dim tck As Object

For Each tck In Tcks
Sheets(tck.Name).Range(Etc) = tck.Value
Next
End Sub

Sub Y()
Dim sht As Object

For Each sht In Sheets
sht.Range(Etc) = Tcks(sht.Name).Value
Next
End Sub

Learner123
12-03-2016, 11:24 AM
Thank you for the response SamT, but I receive an error message when running your code. It breaks after this line:


Tcks.Add tck.Name, N

SamT
12-03-2016, 11:31 AM
I edited the code while you were trying it out. See the revised code

Tcks.Add tck, tckName

Learner123
12-03-2016, 11:45 AM
Sill breaks down at the same spot...

Also, why would there be a need for Sub X () and Sub Y() if we never call for them??

SamT
12-03-2016, 12:06 PM
I am desperate for some guidance - - please help!!

Learner123
12-03-2016, 01:43 PM
Are there any other excel gurus who'd like to take a stab at developing a solution? I really feel like the answer includes adding a class module but am unsure how to do that exactly...

SamT
12-03-2016, 02:01 PM
I will change the Title to reflect the issue.

p45cal
12-03-2016, 05:42 PM
See attached.
active sheet updates immediately a checkbox is changed.

Learner123
12-03-2016, 05:58 PM
You are awesome - thank you very much! :bow:

I was on the right path but had a difficult time executing. Thanks for the enlightenment!

Kenneth Hobs
12-03-2016, 06:00 PM
I would highly recommend that you add Option Explicit as the first line in all your Modules, Userforms, etc.

It is unclear to me just what you want. It is best to state goals, not how you want code done to achieve a goal.

I am guessing that you want some sheet activex textbox controls added to each sheet checked in the userform? I guess if you add the userform checkbox controls dynamically that a class may be needed for running a Sub to take an action on check or uncheck events. But even then, a run by commandbutton would work just as well or better.

If you have just a few sheets, it would be best to just code the checkbox options in to begin with for the userform. You can always iterate by sheet index and hide the unneeded sheets. I don't really see a need for checkboxes for each worksheet. It would be easier to maintain to just use a control like a listbox and use Selection or a checkbox option to make the list. Again, a commandbutton would be best to take action on selections or not.

For sheet activex controls, here is an example to add one to show the concept. Note how it sets a linked cell to hold the true/false values for check or not. In production, one would have to get a list of all checkbox controls and check their topleft cell locations and maybe names to make sure that added or deleted ones do not overlap.


Sub MakeOneActiveXTextBoxAndLink()
Dim c As Range, s As OLEObject
With Worksheets("NY")
Set c = .Range("BA2")
Set s = .OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=True, _
DisplayAsIcon:=False, Left:=c.Left, Top:=c.Top, Width:= _
c.Width, Height:=c.Height)
s.LinkedCell = c.Address(external:=True)
End With
End Sub

There are better ways to handle the sheet checkbox issues.
1. Data Validation with True,False list.
2. A sheet selection event, to move a checkbox control there.
3. A sheet selection event to toggle the true/false values.

If you need the userform checkbox class, here is an example. There are many out there. Userform code:

'http://www.mrexcel.com/forum/excel-questions/272752-checkbox-controlsource-visual-basic-applications.htmlDim CheckBoxes() As New Class1


Private Sub UserForm_Initialize()
Dim CCount As Long, c As Control
'place here the code that creates your checkboxes, or elsewhere,
'but surelly before the following procedure
CCount = 0
For Each c In Me.Controls
If TypeOf c Is MSForms.CheckBox Then
CCount = CCount + 1
ReDim Preserve CheckBoxes(1 To CCount)
Set CheckBoxes(CCount).CheckGroup = c
End If
Next c


End Sub

Module:

Option Explicit

Public WithEvents CheckGroup As MSForms.CheckBox


Private Sub CheckGroup_Change()
MsgBox CheckGroup.Value
End Sub

p45cal
12-03-2016, 06:39 PM
It would be easier to maintain to just use a control like a listbox and use Selection or a checkbox option to make the list.
Absolutely! Or to stress the point with infixation: Abso-effin-lutely.

Kenneth Hobs
12-03-2016, 08:51 PM
Here is an example for doing checkboxes in a listbox. Set the commented options manually in the listbox control or uncomment those two property settings.

Private Sub UserForm_Initialize()
Dim a, i&
ReDim a(1 To Worksheets.Count)
For i = 1 To Worksheets.Count
a(i) = Worksheets(i).Name
Next i
'ListBox1.ListStyle = fmListStyleOption
'ListBox1.MultiSelect = fmMultiSelectExtended
ListBox1.List = a
End Sub


Private Sub CommandButton1_Click()
ActOnSelectedListboxItems
Unload Me
End Sub


Private Sub ActOnSelectedListboxItems()
Dim a, i%, j%

On Error Resume Next
If Not ListBox1.Selected(ListBox1.ListIndex) = True Then Exit Sub
'or
'If ListBox1.Selected(0) = False And ListBox1.ListIndex = 0 Then Exit Sub

ReDim a(1 To 1)
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
j = j + 1
ReDim Preserve a(1 To j)
a(j) = .List(i, 0)
End If
Next i
End With


MsgBox Join(a, vbLf)
End Sub

mikerickson
12-03-2016, 11:52 PM
Rather than adding checkboxes at run-time, it would be easier to use a multi-select ListBox with .ListStyle = fmListStyleOption.


With ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
End With

For N = 1 To ActiveWorkbook.Sheets.Count
ListBox1.AddItem Sheets(N).Name

ListBox1.Selected(ListBox1.ListCount - 1) = Range("BA1").offset(Rng, 0).Value

Rng = Rng + 1
Next N