Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
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 "Darren" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
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) "Darren" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
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. "Darren" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
Thanks for everyones help with this.
Cheers Darren "Nick Shinkins" wrote: 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. "Darren" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
and how is that data preserved when the for is unloaded?
-- HTH RP (remove nothere from the email address if mailing direct) "Nick Shinkins" wrote in message ... 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. "Darren" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
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" wrote: and how is that data preserved when the for is unloaded? -- HTH RP (remove nothere from the email address if mailing direct) "Nick Shinkins" wrote in message ... 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. "Darren" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
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) "Nick Shinkins" wrote in message ... 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" wrote: and how is that data preserved when the for is unloaded? -- HTH RP (remove nothere from the email address if mailing direct) "Nick Shinkins" wrote in message ... 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. "Darren" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
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").D esigner '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 "Nick Shinkins" wrote in message ... 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" wrote: and how is that data preserved when the for is unloaded? -- HTH RP (remove nothere from the email address if mailing direct) "Nick Shinkins" wrote in message ... 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. "Darren" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
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").D esigner '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 "Nick Shinkins" wrote in message ... 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" wrote: and how is that data preserved when the for is unloaded? -- HTH RP (remove nothere from the email address if mailing direct) "Nick Shinkins" wrote in message ... 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. "Darren" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding form control values within VBA
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 "Peter T" wrote: 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").D esigner '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 "Nick Shinkins" wrote in message ... 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" wrote: and how is that data preserved when the for is unloaded? -- HTH RP (remove nothere from the email address if mailing direct) "Nick Shinkins" wrote in message ... 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. "Darren" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Control on Form | Excel Discussion (Misc queries) | |||
2007 Form Control/ActiveX Control font difference | Excel Discussion (Misc queries) | |||
Tool Tip Text for Form control/ Active-X control | Excel Programming | |||
passing control value from one form to another form | Excel Programming | |||
#Holding values in different format# | Excel Programming |