• 0

Excel Programming


Question

Okay so I have an excel spreadsheet with a heap of drop-down boxes, using Data Validation to fetch the data from a range of cells.

What I need to implement, are 10 schemes.

eg: Scheme 1, Scheme 2, Scheme 3, and so forth..

And, compared on what's selected in the first drop down box, will affect what options/ranges of cells

will be available in the other drop down boxes..

eg: If listbox 1 is selected as "Scheme 4", then use Cells a13 - a17, or if Scheme 6 is selected then use cells a17 - a20

Anyone know a steady way to do this? I guess you can say I'm a beginner.

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

You'll need to use the Developer tools within Excel.

It'll be programmed in VBA which is a really easy language to pick up quickly.

I've always found it's slightly easier and neater to create a form which will get the data from the spreadsheet itself.

Should be pretty simple to do, just create a form with the relevant drop down boxes and have it fetch the data from ''nameOfSheet''.

Link to comment
Share on other sites

  • 0

I've done this. What I'm asking is I need the formula to change data validation depending IF a certain cell returns a function.

I can do a whole heap of nested IF functions, in a formula, if need be.

Link to comment
Share on other sites

  • 0

'These are the coordinates for the rowsource
	Select Case Me.location.Text
	Dim x_min As Long, x_max As Long, Y As Long

		Case "England"
			'Erase the previous selection
			Me.department.Text = "Please select a department:"
			'Determine the row source as a function of the specific design of your database
			x_min = 3
			Y = 1
			Worksheets("deptData").Activate
			x_max = Cells(2, Y).Value + 2
			'Assign the row source
			Me.department.RowSource = DataSheetName & "!" & Range(Cells(x_min, Y), Cells(x_max, Y)).Address

		Case "Scotland"
			Me.department.Text = "Please select a department:"
			x_min = 3
			Y = 2
			Worksheets("deptData").Activate
			x_max = Cells(2, Y).Value + 2
			Me.department.RowSource = DataSheetName & "!" & Range(Cells(x_min, Y), Cells(x_max, Y)).Address

		Case "Ireland"
			Me.department.Text = "Please select a department:"
			x_min = 3
			Y = 3
			Worksheets("deptData").Activate
			x_max = Cells(2, Y).Value + 2
			Me.department.RowSource = DataSheetName & "!" & Range(Cells(x_min, Y), Cells(x_max, Y)).Address

		Case "Wales"
			Me.department.Text = "Please select a department:"
			x_min = 3
			Y = 4
			Worksheets("deptData").Activate
			x_max = Cells(2, Y).Value + 2
			Me.department.RowSource = DataSheetName & "!" & Range(Cells(x_min, Y), Cells(x_max, Y)).Address
	End Select

Just pasted that from something I've just finished working on. It shows how the Select...Case statements work.

In this example I have used it to select the row source range for another combo-box based on the value selected in another.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.