Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms
I posted this earlier and did not get a response, I will try t
clarify.. I am using a userform with listboxes that the user enters data into an than another userform that returns the calculated results. I would lik to know the code to retain text that was entered in each listbox (i the registry or whatever) on the press of an action button. Here is JW's solution but I get an error improper use of Me function o something similar when I use it. 'Sub Getdefaults() ' Dim ctl As Control ' Dim Ctrltype As String ' For Each ctl In Me.Controls 'Ctrltype = TypeName(ctl) 'If Ctrltype = "Textbox" Or _ '"Combobox" Or _ '"Optionbox" Or _ '"Checkbox" Or _ '"Spinbutton" Then 'ctl.Value = GetSetting _ '(APPNAME, "Defaults", ctl.Name, ctl.Value) 'End If 'Next ctl 'End Sub 'Sub SaveDefaults() ' Dim ctl As Control ' Dim Ctrltype As String ' For Each ctl In Me.Controls 'Ctrltype = TypeName(ctl) 'If Ctrltype = "Textbox" Or _ '"Combobox" Or _ '"Optionbox" Or _ '"Checkbox" Or _ '"Spinbutton" Then 'SaveSetting APPNAME, _ '"Defaults", ctl.Name, ctl.Value 'End If 'Next ctl 'End Sub Any thoughts? Thanks in advanc -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms
You could use a Public 2 dimensional Array to store the Listbox names and Values
But where is this On action Button? The Me.Controls assumes you are calling the procedure from the form where the controls are on At the top of a Module Put this Public MyArray() As Strin But this code will have to be called from the form's modul Dim Lstbx As MSForms.ListBox, i As Singl For Each ctrl In Me.Control If TypeOf ctrl Is MSForms.ListBox The Set Lstbx = ctrl ReDim Preserve MyArray(0 to1,i) ' << assumes Option Base MyArray(0,i)= Lstbx.Nam MyArray(1,i)= Lstbx.Tex i = i + End I Next MyArray will now hold all the values for your list boxes after the form is closed ----- eklarsen wrote: ---- I posted this earlier and did not get a response, I will try t clarify. I am using a userform with listboxes that the user enters data into an than another userform that returns the calculated results. I would lik to know the code to retain text that was entered in each listbox (i the registry or whatever) on the press of an action button Here is JW's solution but I get an error improper use of Me function o something similar when I use it 'Sub Getdefaults( ' Dim ctl As Contro ' Dim Ctrltype As Strin ' For Each ctl In Me.Control 'Ctrltype = TypeName(ctl 'If Ctrltype = "Textbox" Or '"Combobox" Or '"Optionbox" Or '"Checkbox" Or '"Spinbutton" The 'ctl.Value = GetSetting '(APPNAME, "Defaults", ctl.Name, ctl.Value 'End I 'Next ct 'End Su 'Sub SaveDefaults( ' Dim ctl As Contro ' Dim Ctrltype As Strin ' For Each ctl In Me.Control 'Ctrltype = TypeName(ctl 'If Ctrltype = "Textbox" Or '"Combobox" Or '"Optionbox" Or '"Checkbox" Or '"Spinbutton" The 'SaveSetting APPNAME, '"Defaults", ctl.Name, ctl.Valu 'End I 'Next ct 'End Su Any thoughts Thanks in advanc -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms
Thank you for your reply.
Will I then need to call the Array to repopulate when the form i re-opened? Perhaps using a on activate or something -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms
Yes: You can use this to repopulate the for
This also has to be run from within the form's Modul Dim i As Singl For i = 0 to Ubound(MyArray Me.Controls(MyArray(0,i)).Text = MyArray(1,i Nex ----- eklarsen wrote: ---- Thank you for your reply Will I then need to call the Array to repopulate when the form i re-opened Perhaps using a on activate or something -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms
Oh, 1 more thing, it would make sense to clear the array each time before its reused, so do this for the first code
Dim Lstbx As MSForms.ListBox, i As Singl ReDim MyArray() '<< Add this lin For Each ctrl In Me.Control If TypeOf ctrl Is MSForms.ListBox The Set Lstbx = ctrl ReDim Preserve MyArray(0 to1,i) ' << assumes Option Base MyArray(0,i)= Lstbx.Nam MyArray(1,i)= Lstbx.Tex i = i + End I Next ----- chris wrote: ---- Yes: You can use this to repopulate the for This also has to be run from within the form's Modul Dim i As Singl For i = 0 to Ubound(MyArray Me.Controls(MyArray(0,i)).Text = MyArray(1,i Nex ----- eklarsen wrote: ---- Thank you for your reply Will I then need to call the Array to repopulate when the form i re-opened Perhaps using a on activate or something -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User forms
ReDim MyArray() won't work
Use This : ReDim MyArray(0,0 There is a Method just to clear an array but i can't remember it right now! This will work Anyhoo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
New to User Forms | New Users to Excel | |||
User Forms | New Users to Excel | |||
User forms | Excel Programming | |||
User forms | Excel Programming | |||
User forms | Excel Programming |