User form interaction with work sheet drop down

[mcinnes01]

Hi,

I have built this fractional salary tool to calculate salaries. The tool works by having a pop up with 3 drop downs.

First you choose the Scale
Then the fraction
Then the Scale point

Basically on the spreadsheet by selecting the scale from the drop down in cell w15 it uses INDIRECT($X$15) in the validation for the drop down. cell x15 has a lookup and pulls the name of the range for that scale.

This means that only the points that relate to that scale are available in the scale point drop down.

So my problem is that, this works perfectly on the spreadsheet, but it doesn't work on the macro. The macro is accessed on the "MENU" tab.

[Palmetto]

I know this is not the answer you are looking for, but why bother with a userform in the first place since it appears you can accomplish what you need using formulas in the worksheet?

Why not move the validation drop downs to the Menu sheet and set this sheet up to mimic a form? It is much simpler and avoids needless use of VBA.

[Leith Ross]

Hello mcinnes01,

I moved your ComboBox1 event from the Change to the Click event. This seems to be working. Have a look and let me know.

[mcinnes01]

It doesn't seem to change the points range when a different scale is selected.

I've been trying to see if I can use rowsource

E.g. (but doesn't work)

Please Login or Register  to view this content.

[Leith Ross]

Hello mcinnes01,

Maybe I am not clear on the problem. Can you give me a couple of examples showing the inputs, what you expect, and what you are getting?

[mcinnes01]

I thought this might work but apperently it doesn't. Can you give me some pointers as to where I am going wrong.

I take it that when you use rowsource in your code you don't set a row source in the property box?

Please Login or Register  to view this content.

[Leith Ross]

Hello mcinnes01,

Just set the RowSource equal to the name of the named range.

[mcinnes01]

my problem is, that the x15 which I have now named "dynamicRng" does a lookup based on w15 and returns the name of the range which I have set for each scale. These can be seen on the Scale data sheet.

At the moment the validation works on the sheet by using INDIRECT(dynamicRng) or INDIRECT($X$15)

But if I set the row source property to dynamicRng in the PointDrop combobox it return the name of the range relating to that scale.

E.g. Scale 1 has a Range Name of sc1

so in the point drop combo box, instead of listing 1,2,3,4,5,6,7,8,9,10 it just list sc1

[mcinnes01]

also if I set the row source property to INDIRECT(dynamicRng) it works for the scale that is in sheet before you open the macro but then doesn't update when you select a new scale.

[romperstomper]

Does this do what you want?

[mcinnes01]

Thats exactly what I was trying to do, thank you!

I really need an VBA course by the looks of things, I seem to be a million miles of coming up with code like that myself.

Thanks RS you rule!