+ Reply to Thread
Results 1 to 11 of 11

VBA to create input boxes on the fly & other assorted issues

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    9

    VBA to create input boxes on the fly & other assorted issues

    Hello,

    I am new to VBA but I understand the basics rather quickly. I have created a userform that I would like to add some additional features to. I have a combobox that has 2 options. From these options I would like to specify multiple inputs and a submit data button. I would like to have this right on the user form without popup boxes for data entry and only have the number of inputs visible with the correct option. Is this possible?
    Thanks

    Example Input

    Drop Down Option 1 (9 inputs created)

    ID
    Sample ID1 Value1
    Sample ID1 Value1
    Sample ID1 Value1
    Sample ID1 Value1


    Drop Down Option 2 (17 inputs created)

    ID
    Sample ID1 Value1
    Sample ID1 Value1
    Sample ID1 Value1
    Sample ID1 Value1

    Sample ID1 Value1
    Sample ID1 Value1
    Sample ID1 Value1
    Sample ID1 Value1

    Sample ID1 Value1
    Sample ID1 Value1
    Sample ID1 Value1
    Sample ID1 Value1

    Sample ID1 Value1
    Sample ID1 Value1
    Sample ID1 Value1
    Sample ID1 Value1
    Last edited by ounvme; 10-10-2012 at 04:18 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA to create input boxes on the fly

    Hello there,

    Are these text boxes? Also, can you please post a sample of your workbook with a general design of the userform?

    Thanks!

  3. #3
    Registered User
    Join Date
    10-01-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA to create input boxes on the fly

    The workbook is currently empty. I am starting from scratch. The inputs can be text boxes. The values entered I want to have stored in columns.

    This does work but I can only get it to create one textbox.

    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA to create input boxes on the fly

    Hello there,

    Is there a reason why you just don't add 17 textboxes to your userform and set their original visible properties to false. Then on the combobox change event set the number of textboxes that you want to show visible property to true?

  5. #5
    Registered User
    Join Date
    10-01-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA to create input boxes on the fly

    After doing more reading I find that to be the best solution. I am also wondering if I can have one set of fields with a submit button and do it by entering individual sets of data such as below. I would also like to add a check to see if each textbox is filled before sending the data to the sheet.
    Please Login or Register  to view this content.
    How could I change the cell values each time the button is pressed?
    Last edited by ounvme; 10-03-2012 at 01:21 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA to create input boxes on the fly

    I'm sorry, I'm having a little problem understanding your form and your last question. Could you please elaborate or please post a sample of your workbook with the form so I can see what you are looking at?

    Thanks!

  7. #7
    Registered User
    Join Date
    10-01-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA to create input boxes on the fly

    Here is a sample book. I want it so that I can submit the data up to 14 times having each set of data in its own columm or row. Then after all the data is submitted I can open a verify data page that I can look up the values of the data strored by each click.

    Thank you

    Book2.xls
    Last edited by ounvme; 10-04-2012 at 08:42 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA to create input boxes on the fly

    I want it so that I can submit the data up to 14 times
    By the above statement, do you mean you want the same set of data added 14 times to 14 different rows

    or

    do you want to click the submit data have the data entered on the form store on the worksheet and then enter more data and then click submit again to have the new entered data stored after the previously submitted data?

    Thanks

  9. #9
    Registered User
    Join Date
    10-01-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA to create input boxes on the fly

    I was able to get the values onto the cell sheet using:
    Please Login or Register  to view this content.

    I do have another question. What is the best way to search for a value in a table? Should it just be a range of cells or should the data be formed into a table?
    (.062 & .078) (0.094) (0.125) (0.156)

    1 2.1 2.2 2.3 2.4
    2 2.3 2.4 2.5 2.6
    3 2.5 2.5 2.6 2.7
    4 2.5 2.6 2.7 2.7
    5 2.6 2.6 2.7 2.7
    6 2.6 2.7 2.7 2.8
    7 2.6 2.7 2.8 2.8
    8 2.7 2.7 2.8 2.8
    9 2.7 2.7 2.8 2.8
    10 2.7 2.7 2.8 2.8
    11 2.7 2.8 2.8 2.8
    12 2.7 2.8 2.8 2.8
    13 2.7 2.8 2.8 2.8
    14 2.7 2.8 2.8 2.8
    15 2.8 2.8 2.8 2.8
    16 2.8 2.8 2.8 2.8
    17 2.8 2.8 2.8 2.8
    18 2.8 2.8 2.8 2.8
    19 2.8 2.8 2.8 2.8
    20 2.8 2.8 2.8 2.8

    I want to be able to search for column .125 and row 2 and get either 2.5 or the location of 2.5

  10. #10
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA to create input boxes on the fly

    Below is a link to use the Find method in VBA

    http://www.ozgrid.com/VBA/find-method.htm

  11. #11
    Registered User
    Join Date
    10-01-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: VBA to create input boxes on the fly

    The problem with the Find method is you need to know what to search for. I do not know the value I am looking for but I do know the row and column that the value will be in based on the example. The row will be 1 to 20 and the column will be (.062 & .078) (0.094) (0.125) (0.156). By using these values I need to find the cell that these intersect on.

    Example:
    _abcd
    12345
    23456
    34567
    45678

    I know c and I know 3 and I want the function to return the value 6.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1