Holding form control values within VBA

[Guest]

I have a similar question to that what Dave made earlier.... I want to be
able to have a userform, with comboboxes, textboxes etc, and hold the values
that are entered within the form / code and not by setting the control source
to a worksheet. The simple reason is that I dont want to have a seperate
sheet, that has to be hidden, and protected by workbook protection passwords
etc. It would be better to hold it all within the VBA, and just protect the
VBA project.

I want to be able to use the form on different reports, by importing the
form and attached code.

I have a feeling that this might not be possible, but can anyone help?

Thanks,

Darren

 

[Sharad Naik]

Now what exactly you mean by holding?
To hold within run time as long as application is running you can use
Public Variables declared at module level.
Or do you want to hold the values even after closing the application?
Then this is not possible. You will have to either save in a seperate sheet.
You can keep this sheet unvisible, protected etc. (In the code you unprotect
it when eveer entering data in to it and protect back when done.)
Or you will have to write the data in to a text file. Open and close that
text file
as and when needed. It will be little complicated that using a sheet.

Sharad

 

[Bob Phillips]

Darren,

Don't think you can do it. The form will initialise with whatever is defined
in design mode. I think the sheet, or workbook names, is the way to do it,
but this adds the workbook dependency you are trying to avoid.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Actually it is possible but it is silly and requires a lot of coding.

All you need to do is create a non visible textbox on the fly on the form
you are using. Then just fill it with the data you want to save.

However you will need to write a procedure that can write to and correctly
retrieve data stored in the text box.

 

[Guest]

Thanks for everyones help with this.

Cheers

Darren

 

[Bob Phillips]

and how is that data preserved when the for is unloaded?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Oh, I could be wrong here.

I was under the impression that you can add controls to a userform during
runtime and save the form with the new controls.

If so I thought you could create a text box which isn't visible to the user
and store (in a primitive fashion) some data in that.

I may well be wrong about saving the userform during runtime. If so, sorry.

Nick Shinkins

 

[Bob Phillips]

No, that is precisely the problem.

When a form loads, it loads with the form as it is designed, i.e. without
any runtime additions. Even if you had a non-visible control on the form
created at design time, it would not preserve the value assigned at run
time.

The only way that I know of is to save the data off-form so to speak, in a
database, a flat file, or in an Excel workbook.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peter T]

I was under the impression that you can add controls to a userform during

runtime and save the form with the new controls.

If so I thought you could create a text box which isn't visible to the user
and store (in a primitive fashion) some data in that.

Could user "Designer" from within the same project to make persistent
changes, but not AFAIK while the form is loaded, eg:

Sub FormStuff()
'in a normal module
Dim oCtrl As Control

With ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
'create a new control
Set oCtrl = .Controls.Add("Forms.Textbox.1")
oCtrl.Value = "new textbox"
'oCtrl.Visible = False
'change other attributes to oCtrl.whatever

'CommandButton1 already exists
..CommandButton1.Caption = "last designed : " & Now
End With

End Sub

Havn't tried but might be possible while the form is loaded to keep
variables at global level, run "FormStuff" with OnTime as the form is
unloaded, and put the variables back into controls, text, tag etc.

maybe between the "With's"
..listbox1.list = [myArray]

Regards,
Peter

 

[Peter T]

I was under the impression that you can add controls to a userform during

runtime and save the form with the new controls.

If so I thought you could create a text box which isn't visible to the user
and store (in a primitive fashion) some data in that.

Could user "Designer" from within the same project to make persistent
changes, but not AFAIK while the form is loaded, eg:

Sub FormStuff()
'in a normal module
Dim oCtrl As Control

With ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
'create a new control
Set oCtrl = .Controls.Add("Forms.Textbox.1")
oCtrl.Value = "new textbox"
'oCtrl.Visible = False
'change other attributes to oCtrl.whatever

'CommandButton1 already exists
..CommandButton1.Caption = "last designed : " & Now
End With

End Sub

Havn't tried but might be possible while the form is loaded to keep
variables at global level, run "FormStuff" with OnTime as the form is
unloaded, and put the variables back into controls, text, tag etc.

maybe between the "With's"
..listbox1.list = [myArray]

Regards,
Peter

 

[Guest]

I sort of solved this in the end, by using the deletelines/insertlines
procedures to update the VBA code on the Userform Activate event. I keep the
form in the background, and only update the VBA before the form and
spreadsheet closes, as I needed to unlock the VBA project with a sendkeys, in
order to update the code. (I didnt want to leave the VBA unprotected whilst
the sheet was being used)

This is probably a messy way of doing it, but it seems to be working Ok, and
means that I can keep all of the code, and settings that I need, within the
forms code, and not have to put the on a spreadsheet, etc.

Thanks again for all that replied,

Cheers

Darren

I was under the impression that you can add controls to a userform during
runtime and save the form with the new controls.

If so I thought you could create a text box which isn't visible to the user
and store (in a primitive fashion) some data in that.

Could user "Designer" from within the same project to make persistent
changes, but not AFAIK while the form is loaded, eg:

Sub FormStuff()
'in a normal module
Dim oCtrl As Control

With ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
'create a new control
Set oCtrl = .Controls.Add("Forms.Textbox.1")
oCtrl.Value = "new textbox"
'oCtrl.Visible = False
'change other attributes to oCtrl.whatever

'CommandButton1 already exists
..CommandButton1.Caption = "last designed : " & Now
End With

End Sub

Havn't tried but might be possible while the form is loaded to keep
variables at global level, run "FormStuff" with OnTime as the form is
unloaded, and put the variables back into controls, text, tag etc.

maybe between the "With's"
..listbox1.list = [myArray]

Regards,
Peter

Oh, I could be wrong here.

I was under the impression that you can add controls to a userform during
runtime and save the form with the new controls.

If so I thought you could create a text box which isn't visible to the user
and store (in a primitive fashion) some data in that.

I may well be wrong about saving the userform during runtime. If so, sorry.

Nick Shinkins