• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reuse textbox userform to populate table

J

Jeffrey Lebowski

Member
Hi:

I have drafted up a sample of my problem. Here is the link.
https://www.dropbox.com/s/fg50jrb7dbg79ur/txtbox userform.xlsm

As you can see my table has a ton of combo boxes. For the sake of my question we are only concerned with the combo boxes in the 'Notes' rows (5, 9, 13, 17, 21). The combobox in "B5" is tied to code so that when Yes is selected, it prompts a userform that allows text to be entered. When the userform is filled and OK button is hit, the textbox info is copied into the 'Notes' sheet table (cell b2).

I want this same process to happen for each of the comboboxes in any of the aforementioned rows when their value is 'yes'. I could figure out how to reuse the same userform, but what I cant understand is how to tell the userform to change the cell it populates based on which combobox triggered the userform.

Let me know if I can provide more detail on the issue. Thanks for helping!
 
Hi Jeff ,

Can you download your file and see ?

https://www.dropbox.com/s/6zr86e0vvgmfaa0/txtbox userform.xlsm

I have just coded the outline of what can be done ; can you take it from here ? If not , let me know , and I'll complete it , but you'll have to give me some time.

What you need to do after downloading the file is to run the procedure named SetControlHandler in the Sheet2 (Input) section. Thereafter , when you change any combobox value on the worksheet , the name of that combobox will be displayed.

Narayan
 
Narayan,

Thank you for your help. I have looked at the file and tried to determine how to proceed but unfortunately I am afraid it is beyond my vba skills.

I would greatly appreciate your continued assistance. I am not sure how to use the combobox ID to populate the correct field in the 'Notes' sheet after displaying the textbox userform.

Thanks again & have an awesome day.
 
Hi Jeff ,

Will do.

I will go by the following :
The combobox in "B5" is tied to code so that when Yes is selected, it prompts a userform that allows text to be entered. When the userform is filled and OK button is hit, the textbox info is copied into the 'Notes' sheet table (cell b2).
Click to expand...
1. If B5/C5/D5/..../B9/C9/D9... = "YES" , then the userform will be displayed.

2. On exiting the filled userform , the contents of the textbox will be entered in the appropriate cell on the Notes tab.

Narayan
 
Narayan,

I checked the file, and I noticed one issue:

The comboboxes under Delaware (column I) get pasted into column A of the 'Notes' sheet....instead of column I.
Other than that it works great!

The file that I will be applying this code to will have the same number of columns but a total of 19 questions....so 456 comboboxes! With the rows of significance still following the 3, 6, 9, 12..... pattern. I will try to modify the code so that it works on this file but I may need your assistance if I am unsuccessful.

Thank you so much for your help.
 
Hi Jeff ,

Sorry , my mistake ; can you change the following statement :

col_num = (Val(cbo_s) Mod 8) + 1

to

col_num = ((Val(cbo_s) - 1) Mod 8) + 2

This is in the procedure objOLEControl_Change in the Class Module.

Narayan
 
Thanks Narayan.

Since I am expanding this code to allow for 19 total questions, with everything else remaining constant, would this piece of code be the only piece that I need to change? (I have added to the arrays):

Code: 
            lookup_array = Array(0, 17, 25, 41, 49, 65, 73, 89, 97, 113, 121, 137, 145, 161, 169, _
                185, 193, 209, 217, 233, 241, 257, 265, 281, 289, 305, 313, 329, 337, 353, 361, 377, _
                385, 401, 409, 425, 433, 449)
            output_array = Array(0, 1, 0, 2, 0, 3, 0, 4, 0, 5, 0, 6, 0, 7, 0, 8, 0, 9, 0, 10, 0, _
                11, 0, 12, 0, 13, 0, 14, 0, 15, 0, 16, 0, 17, 0, 18, 0, 19)
After I made these revisions, the userform will not display when I change one of these comboboxes. Is there some sort of initializing code I must execute when all changes are finished???


Also, if you wouldn't mind explaining the following code in greater detail it would really help my understanding:
Code: 
'          Since the combobox numbering starts from 1 and goes till 120 ,
'          we can have numbers of 1 , 2 or 3 digits.
'          If the number is of 1 digit , we will have ox? as the initial value of cbo_s ;
'          If the number is of 2 digits , we will have x?? as the initial value of cbo_s
'          where ? ranges from 0 through 9
            cbo_s = Right(objOLEControl.Name, 3)
            If Asc(cbo_s) > 57 Then cbo_s = Right(cbo_s, 2)
            If Asc(cbo_s) > 57 Then cbo_s = Right(cbo_s, 1)
 
So despite my less-than-full understanding of the code, I have (more or less) adapted the code to fit my working file with 456 comboboxes. I would really appreciate some help with the following:

Often when I start with the first "Notes" combobox (in cell B5), it will run the userform and the generate a run-time error '1004' when I click the userform OK button. When I try to debug it points me to the following line of code:​
Code: 
ThisWorkbook.Worksheets("Notes").Cells(Rownum, Colnum).Value = Me.TextBox1.Text
Is there an obvious error here that is causing this to happen?​
 
Hi, Jeffrey Lebowski!
Haven't read all the topic but after downloading your original file I can't even get the user form displayed. Am I missing something?
Regards!
 
Hi Jeff ,

Is it possible for you to upload your current workbook ?

Otherwise , when you get the runtime error that you have already posted , check out the following in the Immediate Window , by pressing the Debug button in the displayed error dialog box :

1. What are the values of Rownum and Colnum ? You can find out this by typing in :

?Rownum , Colnum

in the Immediate Window.

2. Similarly , type in :

?Me.TextBox1.Text

in the Immediate Window.

Secondly , if the textbox is itself not being displayed , there can be two reasons :

1. The adding of data to the Array , which you have done , needs to be verified , to see if it is correct ; from what I can see in your post , this is correct.

2. The numbering of the comboboxes has followed the same order throughout.

Narayan
 
I was able to resolve this issue by building off of the demo workbook you uploaded instead of trying to recreate another file from scratch...

The only issue remaining is that whenever I close and re-open the file, all the ComboBoxes are reset to blank. I started another thread (sorry if this was out of line but I wasn't sure it was completely relevant) at this link: http://chandoo.org/forum/threads/save-combobox-values-when-closing-workbook.11754/
Is there a way to save the ComboBox values so that they are available when the file is re-opened???

Thanks a lot for your help I really appreciate it!
 
You must log in or register to reply here.
Back
Top