Creating Buttons to fill out form.

[joyousrob]

Hi guys,

Iv got the following excel (below) what id like to do is create buttons in different columns, for industry, supersector,subsector etc… and id like for the buttons to have conditions, so if I select the oil and gas industry in the first column, in the second colum I would only be able to select oil and gas as the supersector and in the third column I would be able to select oil and gas producers, equipment services and distribution, alternative energy etc….

and once selected the last column (Subsector ) the deffinition of this would appear in the next column.

Any ideas on how to do this?
Thanks

[TABLE="width: 1342"]
<tbody>[TR]
[TD]Industry[/TD]
[TD]Supersector[/TD]
[TD]Sector[/TD]
[TD]Subsector[/TD]
[TD]Definition[/TD]
[/TR]
[TR]
[TD]0001 Oil & Gas[/TD]
[TD]0500 Oil & Gas[/TD]
[TD]0530 Oil & Gas Producers[/TD]
[TD]0533 Exploration & Production[/TD]
[TD]Companies engaged in the exploration for and drilling, production, refining and supply of oil and gas products.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0537 Integrated Oil & Gas[/TD]
[TD]Integrated oil and gas companies engaged in the exploration for and drilling, production, refining, distribution and retail sales of oil and gas products.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0570 Oil Equipment, Services & Distribution[/TD]
[TD]0573 Oil Equipment & Services[/TD]
[TD]Suppliers of equipment and services to oil fields and offshore platforms, such as drilling, exploration, seismic-information services and platform construction.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0577 Pipelines[/TD]
[TD]Operators of pipelines carrying oil, gas or other forms of fuel. Excludes pipeline operators that derive the majority of their revenues from direct sales to end users, which are classified under Gas Distribution.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0580 Alternative Energy[/TD]
[TD]0583 Renewable Energy Equipment[/TD]
[TD]Companies that develop or manufacture renewable energy equipment utilizing sources such as solar, wind, tidal, geothermal, hydro and waves.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0587 Alternative Fuels[/TD]
[TD]Companies that produce alternative fuels such as ethanol, methanol, hydrogen and bio-fuels that are mainly used to power vehicles, and companies that are involved in the production of vehicle fuel cells and/or the development of alternative fuelling infrastructure.[/TD]
[/TR]
[TR]
[TD]1000 Basic Materials[/TD]
[TD]1300 Chemicals[/TD]
[TD]1350 Chemicals[/TD]
[TD]1353 Commodity Chemicals[/TD]
[TD]Producers and distributors of simple chemical products that are primarily used to formulate more complex chemicals or products, including plastics and rubber in their raw form, fiberglass and synthetic fibers.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1357 Specialty Chemicals[/TD]
[TD]Producers and distributors of finished chemicals for industries or end users, including dyes, cellular polymers, coatings, special plastics and other chemicals for specialized applications. Includes makers of colorings, flavors and fragrances, fertilizers, pesticides, chemicals used to make drugs, paint in its pigment form and glass in its unfinished form. Excludes producers of paint and glass products used for construction, which are classified under Building Materials & Fixtures.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1700 Basic Resources[/TD]
[TD]1730 Forestry & Paper[/TD]
[TD]1733 Forestry[/TD]
[TD]Owners and operators of timber tracts, forest tree nurseries and sawmills. Excludes providers of finished wood products such as wooden beams, which are classified under Building Materials & Fixtures.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1737 Paper[/TD]
[TD]Producers, converters, merchants and distributors of all grades of paper. Excludes makers of printed forms, which are classified under Business Support Services, and manufacturers of paper items such as cups and napkins, which are classified under Nondurable Household Products.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1750 Industrial Metals & Mining[/TD]
[TD]1753 Aluminum[/TD]
[TD]Companies that mine or process bauxite or manufacture and distribute aluminum bars, rods and other products for use by other industries. Excludes manufacturers of finished aluminum products, such as siding, which are categorized according to the type of end product.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1755 Nonferrous Metals[/TD]
[TD]Producers and traders of metals and primary metal products other than iron, aluminum and steel. Excludes companies that make finished products, which are categorized according to the type of end product.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1757 Iron & Steel[/TD]
[TD]Manufacturers and stockholders of primary iron and steel products such as pipes, wires, sheets and bars, encompassing all processes from smelting in blast furnaces to rolling mills and foundries. Includes companies that primarily mine iron ores.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1770 Mining[/TD]
[TD]1771 Coal[/TD]
[TD]Companies engaged in the exploration for or mining of coal.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1773 Diamonds & Gemstones[/TD]
[TD]Companies engaged in the exploration for and production of diamonds and other gemstones.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1775 General Mining[/TD]
[TD]Companies engaged in the exploration, extraction or refining of minerals not defined elsewhere within the Mining sector.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1777 Gold Mining[/TD]
[TD]Prospectors for and extractors or refiners of gold-bearing ores.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1779 Platinum & Precious Metals[/TD]
[TD]Companies engaged in the exploration for and production of platinum, silver and other precious metals not defined elsewhere.[/TD]
[/TR]
[TR]
[TD]2000 Industrials[/TD]
[TD]2300 Construction & Materials[/TD]
[TD]2350 Construction & Materials[/TD]
[TD]2353 Building Materials & Fixtures[/TD]
[TD]Producers of materials used in the construction and refurbishment of buildings and structures, including cement and other aggregates, wooden beams and frames, paint, glass, roofing and flooring materials other than carpets. Includes producers of bathroom and ******* fixtures, plumbing supplies and central air-conditioning and heating equipment. Excludes producers of raw lumber, which are classified under Forestry.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2357 Heavy Construction[/TD]
[TD]Companies engaged in the construction of commercial buildings, infrastructure such as roads and bridges, residential apartment buildings, and providers of services to construction companies, such as architects, masons, plumbers and electrical contractors.[/TD]
[/TR]
</tbody>[/TABLE]

 

[pbornemeier]

So for your example you would need
3 buttons for the 1st column
4 buttons for the 2nd column
8 buttons for the 3nd column
20 buttons for the 4th column

You could start with all of the buttons in the 2nd-4th columns set to invisible and when one of the visible buttons in the 1st column were clicked then other column 1 buttons would disappear and the appropriate buttons in the 2nd column would be positioned as desired then made visible

Or use combo boxes that populate level second and subsequent combo boxes based on the selections in the previous combo box

This code will populate the first combo box

Sub PopulateIndustry()

  Dim rngCell As Range
  Dim lLastRow As Long
  
  With Sheets("Sheet3").cboIndustry
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For Each rngCell In Sheets("Sheet3").Range("A3:A" & lLastRow).SpecialCells(xlTextValues)
      .AddItem CStr(rngCell.Value)
    Next rngCell
  End With

End Sub

For the next combo boxes you would add another If statement around its .AddItem that only added an item if the first 2,3,4 characters of the text matched the first 2,3,4 characters of the selection in the prior combo box.