Automatically create and organise controls

[djburnheim]

Hi,

I'm trying to automatically create a number of checkboxex on a form when it initialises based on data in sheet. The number of checkboxex will vary depending on how many cells in the sheet have values in them.

The only way I can think to do it is to create a whole heap of genric checkboxex and then loop through them changing the caption to match the cell value and then hide/delete ones that aren't changed but I'd really like to do it dynamically. I've got the code below which will automatically create a single control but if I need to create 10 or 20 different checkboxes how can I get them neatly organised on a form?

Public Sub CreateTextBoxes()
Dim Test As String
Dim myText As TextBox
Dim NewTextBox As MSForms.TextBox
Dim T As String
T = "ABC"

Test = PropertyList(1)

'MsgBox test
Set NewTextBox = Me.Controls.Add("Forms.textbox.1", T)
With NewTextBox
.name = T
.Top = 20
.Left = 150
.Width = 150
.height = 12
.Font.Size = 7
.Font.name = "Tahoma"
.BorderStyle = fmBorderStyleSingle
.SpecialEffect = fmSpecialEffectFlat
End With
NewTextBox.Value = "123"

End Sub

Any suggestions or just go with ?

 

[SkipVought]

Hi,

You have to think thru this very carefully.

First, what is the MAX number you can expect?

How would you fit and position them?

How many ROWS/COLUMNS in the form?

Can the size of the form change?

Then its a matter of loops and simple math to place a subset of the maximum.


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[djburnheim]

Thanks, I'm also worried about how many the Max is. I've gone with my 1st thought which is a little dirty but it works...

On my form I've created 60 checkboxes and have set them to visible=false, I've then used the code below to make them visible and change the caption...if I ever get more than 60 items in my list I'm going to have to change the form.

Private Sub UserForm_Initialize()

'Populate the software detail frame with the names of the approved software

'All approved software is listed in column A of WorkSheet 'Approved Software'
'First software title is in cell A2, what is the last software title
Dim LastRow As Integer

LastRow = Worksheets("Approved Software").UsedRange.Rows.Count

'Now populate checkbox captions
Dim RowN As Integer
Dim ctrl As Control
Dim Software As String

RowN = 2

Sheets("Approved Software").Select

For Each ctrl In Me.frmSoftware.Controls
If TypeName(ctrl) = "CheckBox" Then
If RowN <= LastRow Then
Software = Worksheets("Approved Software").Cells(RowN, 1).Value
ctrl.Visible = True
ctrl.Caption = Software
RowN = RowN + 1
ChkN = ChkN + 1
End If
End If
Next ctrl
End Sub

 

[SkipVought]

You ought to be able to develope an algorythm to determine the size of the form per the number of checkboxes.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

What exactly are you trying to accomplish, there may be a better solution. There are several options for building lists with checkboxes. A list view control seems like an easy solution. Or maybe a tree view. Either case you could have thousands of items with checkboxes and never change the form, or build a dynamic form.

 

[MajP]

listview

http://i40.tinypic.com/292nyq8.jpg

 

[combo]

I agree with MajP. It is possible to create a set of dynamic checkboxes, they can be added to a scrollable frame to get enough space on a fixed size userform. The vb listview is however much simpler to apply.
The native vba listbox control could be another option after:
- ListStyle = fmListStyleOption,
- MultiSelect = fmMultiSelectMulti.

combo