Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading UserForm Controls (with a twist!)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading UserForm Controls (with a twist!)
why don;t you link the check boxes to specific cells - maybe on a hidden sheet?
"Trevor Williams" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading UserForm Controls (with a twist!)
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 -- Rick (MVP - Excel) "Trevor Williams" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading UserForm Controls (with a twist!)
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 "Patrick Molloy" wrote: why don;t you link the check boxes to specific cells - maybe on a hidden sheet? "Trevor Williams" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading UserForm Controls (with a twist!)
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 "Trevor Williams" wrote: 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 "Patrick Molloy" wrote: why don;t you link the check boxes to specific cells - maybe on a hidden sheet? "Trevor Williams" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading UserForm Controls (with a twist!)
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 On Oct 30, 11:21*am, Trevor Williams wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loading a Userform | Excel Programming | |||
Loading a Userform | Excel Programming | |||
Loading website from userform | Excel Programming | |||
Loading Userform | Excel Programming | |||
Loading UserForm on start up. | Excel Programming |