Dynamically create and Place Controls such as CheckBoxes,Text-Boxes in a UserForm

Dear Forum,

Please please help me Im not so fluent in VBA and therefore unaware of a lot of Events and Proprties of Controls so googling also becomes difficult for mw when you dont know what you need to GOOGLE or even Search in the Forum...

I am trying to make a USer Form for the past 1 month or so and I am learning new things almost every day about the unexplored VBA UserForm stuff..
This is indeed very good for my learning curve rendering improvisation at every juncture in the User Form...

Now what I intend to do is have two forms One To Enter Data and the other form to select this Added Data in another form via a User Form..

So i Need to add the Checkboxes Dynamically..!

Hi e4excel,

Here is the solution on how to add an check box using vba

ActiveSheet.Shapes.AddFormControl(xlCheckBox, Left:=Cells(1, 1).Left + 5, Top:=Cells(1,1).Top, Width:=Cells(1, 1).ColumnWidth * 7, Height:=Cells(1, 1).RowHeight)

Originally Posted by narayansingh.t

Hi e4excel,

Here is the solution on how to add an check box using vba

ActiveSheet.Shapes.AddFormControl(xlCheckBox, Left:=Cells(1, 1).Left + 5, Top:=Cells(1,1).Top, Width:=Cells(1, 1).ColumnWidth * 7, Height:=Cells(1, 1).RowHeight)

Hey t hanks NarayanSingh,

Would appreciate if you could explain where I need to put this code in the MOdule and how do I paste it as one single line as I tried doing that and kept getting errors.

Can you please upload a Dummy WOrksheet showing this example with a CheckBox and a Txt Box next to it..

Warm regards
e4excel

Dear Forum,

I am uploading a Dummy SHeet explainig my requirement so please please help me based on this File as this is a basis for my actual file..

I have somehow managed to Add data from a USer Form which was quite a challenge however there are some things which I still am unaware such as ComboBox values population, then Add checkboxed and other controls dynamically..

The file would give the persob helping a gist of what has to be undertaken...

Please explain where the code needs to be put and also if possible work in the same file...

Warm Regards
e4excel

Dear Snb,

With Due respect to your suggestions I would definitely try to keep it simple however the demands of that particular USerForm calls for Dynamic Controls..

The form I have added in this thread is just a reference as while making this USERFORM I learnt about uploading the data in the Sheet..

Now the actual file is the one in another thread...
http://www.excelforum.com/excel-prog...l-objects.html

But, what I want to do i Add the Policy Details in the Main Sheet and then again I will require the same added information but not all of it and only a few Policy Nos and its respective details. and therefore require it with CheckBoxes..

From the other thread while Inputting the Policy Information for the first time will be added in the Data Bank and the next time whenver the same Policy Premium is to be Paid I will just select it from the Existing Policy Details...

I am explaining this to reinforce my need for dynamic controls addition..

I would appreciate if someone could just help me with even 1 CheckBox and 1 TextBox in the Dynamic Form as this logic would be applicable for several different instrumnts like Fixed Deposits,Mutual Funds etc..

Please revert for any more details as I really need this to work at the earliest...

Warm Regards
e4excel

Dear e4excel,

As I understand from you question is that you want to create a user form but with variable textboxes or checkboxes, etc. and you want these elements to be added to your form dynamically according to the entries in your sheet, in this case you might want to use something like this:

Please Login or Register  to view this content.

Dear Mohd9876,

Can you please upload a file with the example...I also need the combobox in it the new userform.

Dear RoyUK,

That wont be a feasible option as I will be needing only a few Policies to be displayed and not all..so Hiding them would mean that some of them would be hidden in a contiguous list..

Warm regards
e4excel

Dear e4excel,

I attached an excel sheet that will hopefully help you in your application.
check out the code and modify according to you requirement, and if there is something not clear please let me know

Sincere Apologies...!!!

RoyUK, i misinterpreted the ENabling Option as Hiding it as I actually want only the relevant details to be made into Check Boxes and TextBoxes.

Dear Mohd,

I am going through your file and it seems very close to the requirement but the only issue is that I want the New Form to be generated with the Information which was entered using the Show FOrm Window..this is saved in the Sheet1 and then from this data I want the New Form to get generated..

Maybe, I was not able to explain correctly...

I will do that again in my next post as too tired now had a very long day...

Thanks for the File Mohd it s really very close...

Warm Regards
e4excel

Dear Mohd,


I checked your file again its indeed very dynamic even when I added new columns of data in the Sheet1. i.e. Column Headings that they got immediately added in the User Form which is really very impressive..

But what I want is slightly different, I think I was not able to explain it very well..as My main file which I m working is from the other thread and the file attached in the first post of this query was just an example to explain the requirement..

I am sorry was unable to post the File in the last couple of days as I was busy perparing it..!
Your file was good but my requirement is different than that and therefore I felt it neccessary to post a new file with an example of my requirement..

You need to enter the Information using the Button SHow Entry-Form then after entering all the details I need the details for only those Policies belonging to the person slected in the Drop-down and those details would be in the form of ChckBoxes and text-boxes if there are more than one Policies for a person than those many controls should be added one below the other...

Dear e4excel,

I have modified and added some code to your excel workbook according to my understanding of what you want to be done.
the details form will be generated according to the policy holder name and the "Mr A" sheet will be appended with the values selected from the check boxes.
see if that is what you need and tell me if you need anymore help.
regarding the date of commitment i replaced the controls you were using to select the date with a control from Microsoft called Date Time Picker which you can find when you right click on your toolbox and select additional controls.

I hope that will help you,

Regards,

Dear e4excel,

please find attached the new workbook with the modifications that you asked for, note that the data will now be appended to a sheet whose name is the same as the name of the Policy holder and if it doesn't already exist it will be created.
about the sentence, i didn't know where you want it to be displayed so i have shown it as a message box; you can modify the code and place it where ever you like.

in the link below you can find more information about the date and time picker.
http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx

Best Regards,
Mohammad

Originally Posted by mohd9876

Dear e4excel,

please find attached the new workbook with the modifications that you asked for, note that the data will now be appended to a sheet whose name is the same as the name of the Policy holder and if it doesn't already exist it will be created.
about the sentence, i didn't know where you want it to be displayed so i have shown it as a message box; you can modify the code and place it where ever you like.

Best Regards,
Mohammad

Hey thanks Mohd,

it worked very well for Mrs B and Ms C however it throws an error when I tried for more than 2 policies for Mr A , the SHeet got created which was a verg novel thing (Appreciated) and the data pasted but the message did not show up as it went in an error mode at the line:

Regarding the sentence, I wam trying to make an Auto-Generated sentence i.e. Description of a Debit Entry when it s made towards a Consolidated Payment for more than 1 Policy....

What was the error and on which line?
can you attach the workbook on which the error was generated and i will see what was the problem

Dear e4excel,

regarding the scroll bars, i modified the form so that when the number of rows is more than 10 it will display a scroll bar to be able to navigate properly.

the error maybe due to the error in that line is due to overflow since i declared premium as integer, but now i modified it to double which hopefully solves the problem.

check the attached workbook and i hope you don't have any more problems.

Best Regards,
Mohammad

Dear Mohd,

I think the Major portion of getting the User Form is already achieved with your valuable help...

I was just thinking of the Formatting Aspect to make it more presentable...

Do I need to do it right in the beginning or after entering the Data and this pertains to both the UserForms...the First one where I am entering the Data...and also the New Form developed by you.. and also in the SHeets were the Data is arranged...

Warm Regards
e4excel

Dear e4ecxel,

I think it would be more practical to include the formatting in the code where the data is entered in case you want to move the code into a new workbook without copying the entire workbook or remaking the formatting. So I would suggest you add the formatting on each new entry using the Add Entries button in the first form and the OK button in the second form.

Regards,
Mohammad

Dear e4excel,

i have attached this workbook at which i fixed the problem with the column headers and added a button to select all of the check boxes.
about the formatting i started doing some of it but i am sorry i can't do it for you because it needs some time that unfortunately i don't have. but you can easily find out how the formatting is done in VBA by simply recording a macro and checking the generated code and modifying it.
i hope it all works out for you.

best regards,
mohamamd

Dear Mohd,

Just wanted to know from the code:

Please Login or Register  to view this content.

Where do I change the placement for the controls and also how to add and where to add/delete any fields from the dynamic form..

I would require to make such useful Multiple Forms when I want to add details for any other Investments such as Mutual Funds , Bank Deposits.. etc..

Can I have the Auto_filter Optin removed when not in use or after the Details have beed added to the sheet..

Warm Regards
e4excel

Dear e4excel,
if you want to remove the auto filter option you simply add this line

Please Login or Register  to view this content.

about how to change/add/delete controls you simply modify these codes:

Please Login or Register  to view this content.

the code is very simple and it has nothing complicated in it but if you go through it and find anything that you don't understand please don't hesitate to ask me.

Best Regards,
mohammad

Dear Mohd,

I am really sorry for bothering you so much but every new improvisation is actually making me make it more better and better..

I also thought instead of having the Drop-Down of the Names in the Sheet, it would be better to have the same in your Dynamic Form and also when I tried changing the postions of the 2 commandbuttons Select All and OK I got an error in the line for

Me.Controls.Remove (control.name) and the
Error 444 Message box said could not delete the controls. this method cannot be used in this context..

I was trying to put the command buttons one below the other to save space and reduce the width of the form..

And one more thing, when the Details get copied to a NEw Sheet Auto-Generated with your code then the Sr. No is the same from the Main Sheet , is it possible to have it serially so that it looks more neater..


I intend to add one more text box for the Name of the Person who is a nominee as Nominees Name and then exixiting label for Nomineed then would aprtly change to Relationship..

I wanted to fine-tune the controls Width for appropriate usage of space in the form as some text boxes require very less space and therefore the form can be made a little less wider and even the internal spaces between each control sideways can be fixed and also the Internal spaces between the controls one above the other...

Once the entire concept is Fine_tuned I will have to replicats such Dynamic FOrms for different Investments such as Mutual Funds, Bank Deposits, etc..
Therefore, I am forced to bother you so much...


Warm regards
e4excel...

Dear e4excel,

sorry for late reply.

Regarding the select all button you can add another button named "select none" and assign it the same function but instead of chkbox.value = True write chkbox.value = False.

Regarding the 444 runtime error, you can fix this by simply correcting this line as follows:

Please Login or Register  to view this content.

about the other things I am sorry I can't do this work for you (unless you give me a pay check ) you have to do it on your own, try searching the help files or as I told you record a macro and format some cells and see the code generated for you and then you can modify it to suit your requirements, it is not that hard.

Wish you all the best

Regards,
Mohammad

No issues at all..!

Dear Mohd, you have been a great help to me...

I wil definitely reearch and try to manage those things however wanted to know whether I can add a combobox for the Policy Holder's name in the Dynamic Form so that all the informations is avaialable on the form..

I might go for a Toggle Button if thats more convenient for the Select All / None if thats feasible or else would simply proceed as you mentioned..

I did understand a lot of things in the Formatting but have not found any properties to set the MaxLength and also to have the Formats for Singular/Plural Years and the Rupee Currency Formatting once thats done the only t hing reminaing is to have a good formatting for the data in the sheets to be made more presentable..


I am closing this thread as SOLVED now and I thank you from the bottom of my heart..
If you can simply guide me on the requirements nothing like it but am not wishing for you to work on the file...


Warm Regards
e4excel

Well,

Dear Mohd,

I did manage to use a Toggle BUtton to get the effect succesfully..

please find the code below:

Please Login or Register  to view this content.

However when I tried to add the Combo Box I am not able to proceed further as Setting the l to the Combobox value is throwing an error..

Maybe because the form is created with the name selected from the dropdown in the Sheet whereas I am trying to have the dropdown in the Form Itself..

I realise this is just like the Forms is created before the selection whereas if the Combo-Box is in the form then the Form has to re-create i.e. should expand in Height with the addition of new controls...

Warm Regards
e4excel

Dear e4excel,

Good for you being able to modify the code and add the toggle button.
About the combo box I can't figure out what the problem might be and what actually you need by placing the combo box in the form itself. if you upload your last workbook and point to me where the error occurred I may be able to help you.

Mohammad

Hey Mohd,

It were not possible if I had not your code to go in the Toggle Button so am just happy that I could do it but the files entirely done by you..thanks...

I was trying to remove the Dropdown on the sheet to select the Policy Holder so that I just have two forms...

Please find the Sheet attached with a little bit of Formatting added to it..

I have added a few lines of code FYI

Please Login or Register  to view this content.

Mohd, also is it possible to add a "&" just before the last policy no in the Message Box which has concatenated all the Policy Nos..

Please Login or Register  to view this content.

P.S. In that sentence part for the Policy No(s) was it possible to add "&" instead of the comma...

Premium for L.I.C Policy No(s) : 912345666, 845678910 & 944521005 and Total Premium: Rs 6000 "..

Warm Regards
e4excel

try this out;

Mohammad

Wow,

Mohd, Thanks a million^million...
Really impressed with all your help and perseverance wish I could double rep for that!..

Anyways thanks a lot and now new challenge for me would be to replicate such Dynamic Forms for other Investments as explained...

Thanks once again ..

God Bless..

Warm Regards
e4excel