Loading UserForm Controls (with a twist!)

[Trevor Williams]

Hi All,

I have a UserForm that contains a multipage control. Each page of the
control contains several Frames, and each Frame contains several CheckBoxes.

Once the user has made their selection the Captions of the Checkboxes are
written to a specific Cell on the ActiveSheet, seperated with a semi-colon.

e.g. If the user has ticked CheckBox 1, 2, and 4 in Frame1 on Page1, and
then closed the form, the Cell A1 will contain the Value "Caption 1; Caption
2; Caption 4" (this is already in place and working.)

What I would like to do is re-load the UserForm based on the Cell Values.
When the form is re-opened the corresponding CheckBoxes will display as TRUE

Hope that makes sense(?) -- I'm using XL2002.

Look forward to your response.

Trevor Williams

 

[Patrick Molloy]

why don;t you link the check boxes to specific cells - maybe on a hidden sheet?

 

[Rick Rothstein]

Try using this UserForm_Initialize event...

Private Sub UserForm_Initialize()
Dim C As Control
For Each C In Frame1.Controls
If TypeName(C) = "CheckBox" Then
C.Value = InStr(Range("A1").Value & ";", C.Caption & ";") > 0
End If
Next
End Sub

 

[Trevor Williams]

Hi patrick -- thanks for the response.

The workbook is made up of a variable amount of sheets that will be using
the UserForm, so probably not a viable solution unless I can make the linked
cells update depending on the ActiveSheet...

Having said that, I could link the controls to some hidden cells on the
ActiveSheet and Load directly from there, and then use a Concatenate function
to populate the visible cells.

hmmmm - it's all becoming clear.

Thanks for the 'nudge'

Regards

Trevor

 

[B Lynn B]

I needed to solve nearly the same problem quite recently. I just put the
value of the stored string cell in a variable (ValStr) and then used Instr
function to test whether the caption value was in the string.

Dim ValStr as String
ValStr = Range("StoredValue").Value
IF Instr(1, ValStr, [Your Checkbox Caption]) > 0 then [YourCheckbox] = true

If you've controlled the enumerating of your checkboxes you can also just
use a loop to get them all.

Dim myCtrl as Control, ValStr as String, i as long
ValStr = Range("StoredValue").Value

for i = 2 to 6 (or the control numbers you want to load)
Set myCtrl = me.Controls(i)
If Instr(1, ValStr, myCtrl.Caption) > 0 Then myCtrl = true
next i

 

[JP]

If the checkbox names correspond EXACTLY to what you put in the cells
(as you describe, CheckBox1 corresponds to "Caption 1"), then you
could do something like this:

1) Split the value of the cell into a Variant:

Dim values() As Variant
values = Split(Range("A1").Value, ";")

2) Loop through the variant and re-populate each control accordingly

Dim i As Long
Dim valueName As String
Dim controlNumber As Long
For i = LBound(values) to UBound(values)
valueName = values(i) ' C

' extract number so we know which control to activate
' i.e. for "Caption 1", controlNumber = 1
controlNumber = Right$(valueName,1)

' re-populate the control
Me.Controls("CheckBox" & controlNumber).Value = -1
Next i

--JP