Creating Buttons to fill out form.

joyousrob

Board Regular
Joined
Nov 12, 2014
Messages
56
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

IndustrySupersectorSectorSubsectorDefinition
0001 Oil & Gas0500 Oil & Gas0530 Oil & Gas Producers0533 Exploration & ProductionCompanies engaged in the exploration for and drilling, production, refining and supply of oil and gas products.
0537 Integrated Oil & GasIntegrated oil and gas companies engaged in the exploration for and drilling, production, refining, distribution and retail sales of oil and gas products.
0570 Oil Equipment, Services & Distribution0573 Oil Equipment & ServicesSuppliers of equipment and services to oil fields and offshore platforms, such as drilling, exploration, seismic-information services and platform construction.
0577 PipelinesOperators 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.
0580 Alternative Energy0583 Renewable Energy EquipmentCompanies that develop or manufacture renewable energy equipment utilizing sources such as solar, wind, tidal, geothermal, hydro and waves.
0587 Alternative FuelsCompanies 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.
1000 Basic Materials1300 Chemicals1350 Chemicals1353 Commodity ChemicalsProducers 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.
1357 Specialty ChemicalsProducers 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.
1700 Basic Resources1730 Forestry & Paper1733 ForestryOwners 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.
1737 PaperProducers, 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.
1750 Industrial Metals & Mining1753 AluminumCompanies 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.
1755 Nonferrous MetalsProducers 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.
1757 Iron & SteelManufacturers 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.
1770 Mining1771 CoalCompanies engaged in the exploration for or mining of coal.
1773 Diamonds & GemstonesCompanies engaged in the exploration for and production of diamonds and other gemstones.
1775 General MiningCompanies engaged in the exploration, extraction or refining of minerals not defined elsewhere within the Mining sector.
1777 Gold MiningProspectors for and extractors or refiners of gold-bearing ores.
1779 Platinum & Precious MetalsCompanies engaged in the exploration for and production of platinum, silver and other precious metals not defined elsewhere.
2000 Industrials2300 Construction & Materials2350 Construction & Materials2353 Building Materials & FixturesProducers 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.
2357 Heavy ConstructionCompanies 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.

<tbody>
</tbody>
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,890
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

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,792
Messages
5,655,332
Members
418,190
Latest member
Timex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top