• 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.

EXCEL 2007 - VBA Multiple Combo Box Assistance

B

Bigmiddle

New Member
My programming knowledge is terrible, but due to the struggles with getting our clients to complete a simple form i have gone down the route of reproducing the excel form so that they have simple options to choose from.

As part of this i am trying to produce linked combo-boxes for Country, County and Local Authority (Council) where the data for each is in a separate sheet, can someone please assist with this as i cant find anything online which will do the job i require.

I have attached a copy of the file which has the detail on separate sheets, currently the Local Authorities are only populated for London, but you get the idea of whats going to be required.
 

Attachments

  • Tender Information Verification Template.xlsm
    30.3 KB · Views: 10
Hui,

Thank you for your response, but the reason I was steering away from Data Validation is due to all the lists information having to be populated in the same sheet as the form and not being able to link to multiple columns of data, there is going to be potentially 120 columns containing all the various local authorities, with these lists from 1 local authority through to London which has a total of 34 different local authorities.
 
Could someone please respond to this without suggesting Data Validation as i have stated, this will not be possible, hence why i have asked the question regarding help with this problem in the VBA Macros Sections of the forum, as i am asking for assistance with programming the combo-boxes?

If it is not possible then could someone please tell me this?
 
Lymm,

I have this set up on the file attached to the post, the Combo-Boxes are in place, the first links to a list of UK & Ireland Countries on 1 sheet

On a second sheet i have the countries along the first 4 columns and below that a list of respective counties which the second combobox needs to select information from based on the selection of which country.

On a following sheet there is 120 columns headed by each county and below is a list of local authorities, i would like final each combo-box to only select the relevant list to choose options from.

I am looking for someone to assist with the programming requirements as my knowledge of programming is very basic.
 
Hi @Bigmiddle

I think.. you are looking for below one..

Please check the attached.. if its working as per requirement..

I have added "Cell Link" properties for the Drop Down..

Code: 
Sub DropDown23_Change()
On Error Resume Next
With Sheets("County")
ActiveSheet.Shapes("drop down 25").ControlFormat.RemoveAllItems
ActiveSheet.Shapes("drop down 25").ControlFormat.List = _
  Range(.Cells(3, Sheets("tender info verification").Range("F15")), .Cells(.Cells(3, _
  Sheets("tender info verification").Range("F15")).End(xlDown).Row, Sheets("tender info verification").Range("F15").Value))
End With
End Sub
Sub DropDown25_Change()
On Error Resume Next
With Sheets("LA")
ActiveSheet.Shapes("drop down 26").ControlFormat.RemoveAllItems
trgtcolumn = Application.Match(ActiveSheet.Shapes("drop down 23").ControlFormat.List _
  (ActiveSheet.Shapes("drop down 23").ControlFormat.ListIndex), Sheets("LA").Range("1:1"), False) + Sheets("tender info verification").Range("F16") - 1
  ActiveSheet.Shapes("drop down 26").ControlFormat.List = _
  .Range(.Cells(4, trgtcolumn), .Cells(.Cells(4, trgtcolumn).End(xlDown).Row, trgtcolumn))
End With
End Sub
 

Attachments

  • Tender Information Verification Template.xlsm
    35.1 KB · Views: 10
Debraj,

That has helped massively, thank you very much, just got to populate the rest of the information for that section now.
 
You must log in or register to reply here.
Back
Top