How to create labels during runtime in a userform based on a named ranged from a combobox in the userform or inputbox

  • Hello I'm trying to make a userform where it creates labels during runtime but the label values are based on a value that is selected from a combobox in the userform. The value is a named range, the code below can make a number of textboxes during runtime based on a value from a inputbox. I also need the quantity of the text boxes to be same of how many values there is in a named range. Is there anyone that canhelp? I attached the file as well. Test_File.xlsm



    Edited once, last by pike: add code tags for newbie ().

  • I tried to change datarng to a named range but it's not reading it. I also change DataRng to a name but its not setting the labels to use the values from the named range and setting the amount of the labels and textboxes as the counted amount of the name range HeartStory



  • I don't understand why you want to use a named Range, CurrentRegion is Dynamic so will reflect changes in content, i.e. it will expand or contract as ows are added or removed.


    You need to use Set when adding a value to a Variable declared as a Range

    Code
    1. Set DataRng = Range("HeartStory")
  • The reason I want to use a named range is because I want to link the variable that is a named range with a combo box. So whichever named range is selected in that combobox will transfer onto the userform menu, like if I had select a named range from the combo box with six values cat, dog, squirrel, bird, mouse, lion, I want those values to display as the caption for the label boxes and only show the quantity of the named range which for this example is only 6.

    Does that make sense?

  • Currently now I have the HeartStory as the range and it is only showing the first value of that named range but it's not show the rest of the named range as the captions for the labels.

  • These are the named ranges I am using. I was able to get that fixed about displaying the label captions now my dilemma is I want to have a combo box opening during the initialization event to select the named range from a combo box that is created during runtime and have the values of that named range to be displayed on the userform.

    I also want to know how I can set the values of the textboxes to a specific named range as well. I am going to have multiple tables and I want to be able to select the correct name range to transfer the values of the textboxes to the correct table for example I have sheet called table1, I want to transfer the values of the textboxes to D11, and then be able to clear it and then make another entry with the same questions below the first entry and so on.

  • So I figured out how to add entries but I need to add the column headers, I saved a column of values as a name range to be use as columne headers. I'm trying to copy that named ranged and transpose it onto another sheet. I am using combo boxes in the userform to select which sheet and which starting point to paste that named range. For some reason its not letting me.

    Here's the code

    Files

    Edited once, last by royUK: Add Code Tags. Please use them in future. Simply click on the </> button in the post menu. It will open a box for you to paste the code into. ().

  • Yes I was able to generate labels from a named range, I was able to post entries using the text boxes. I'm just trying to copy a named range and paste and transpose as column headers with the selection of a combo box to select which name range I want to use for column headers. The code I'm using doesn't let me do. It keeps giving me error.

  • I attached the file and picture displaying what I need. In the userform I have three combo boxes. The first one is to select the named range, second is to select a named range that is the starting point like for exampled Table1!("D10"). Thats the start point where I want to paste transpose the named range as column headers. The third combobox is to select the worksheet I want to paste transpose the columns. The command box DBColumn Headers is the button I want to do that but the code is not matching up and gives me an error.

  • The first problem that I have found is with the ComboBoxes. You have manually set the list values but then tried to do this within the code. This will cause a problem, so I have removed the manual settings.


    I can't make out what the form is for but I think you are over complication it. Can you give some clear instructions wht you are doing, the image isn't much help.

  • I've amended your code. It now adds the Labels with Captions.


    The comboboxes are loaded using the data in the columns, they do not need Named Ranges.


    Let me know what the other requirements are.