Issue with named ranges and user forms

[Cease]

Hey guys,

I'm trying to have the caption of a TextBox within a user form change, based on cells that change with data validation.

Using the Control Source section within the TextBox properties, this seems to work if I have it controlled by a named range.
(ex, I have cell BQ14 named "Blue1").


However, now when I change the data validation, cell BQ14 no longer changes.

Before I set the Control Source, cell BQ14 changed its value based on data validation using an index/match formula combination.

Now, however, the name is set, and when I click on the Cell BQ14 there's no function, just the name.
When I try to change it back to a formula it runs the formula, and then changes the cell back to the name.


Any idea why this is changing?

Thank you in advance for any advice or insight!

 

[Andrew Poulsom]

A control and its ControlSource are inextricably linked. The link is bidirectional - changing either changes the other.

Also you can't use data validation on a cell with a formula.

 

[Norie]

It's changing because you are using the ControlSource property.

That's a 'two-way' thing - if you change something on the userform it will be reflected in the cell you've used for the control source, and vice versa.

I'm not sure exactly what the mechanics would be when you throw formulas, data validation and control source together.

Not good it would seem.

What are you trying to do?

When do you want to show or change the value from the cell on the form?

 

[Cease]

Great to know that's two way, thank you both!

The big picture I'm working on is to build a user form to fill out a lot of fields that can change.

The validation itself shows in cell D5, and has 4 choices. With each of those choices a list that displays from BQ14:BQ36 changes, based on the value of D5.

What I'm trying to do, is for the user to make a choice from D5, then click a button to pull up the user form.

On the form itself I'm hoping to have a series of textboxes that equal the value from the range BQ14:BQ36, with a different TextBox tied to each name.

So TextBox1 would display the value of BQ14
TextBox2 would display the value of BQ15

and so on.



I'm really not sure if this is possible, but am trying to get this to work.

Thanks again for your time!

 

[Norie]

Why multiple textboxes?

You could show all the data in a listbox.

Set rng = Range("BQ14:BQ36")
 
ListBox1.List = rng.Value

If the user needs to change any of the values they could select it from the list and enter in the new value via a textbox.

You could also add code to allow the user to add/delete values.

Maybe a bit easier to work with than a whole load of textboxes.

 

[Cease]

I tried setting up a listbox, great Idea, definitely a lot clearer than textboxes.

Although the code doesn't seem to do anything where I have it.

I've built this on a dummy sheet, so this is the entirety of the code. Not sure what's missing?

Sub MB()
Set Rng = Range("BQ14:BQ36")
ListBox1.List = Rng.Value
End Sub

I had it as a load of textboxes for my own weird format I had set up, but I can work it in as a listbox no problem, just didn't think of it as an option.

Thanks so much for your time!

 

[Cease]

Ok, I added it to the macro to launch the UserForm and it works perfect.
Sometimes I really miss the obvious.

Thanks so much again, have a great day!

 

[Norie]

That really isn't the place to put the code, it should probably be in the userform Initialize event.

If you double click the userform in design mode you should see this.

Private Sub UserForm_Click()
 
End Sub

Now that's the click event, to get the Initialize event just selected it from the right dropdown at the top of the code window.

Now you should see this.

Private Sub UserForm_Initialize()
 
End Sub

Any code you want to run should go in here.

 

[Cease]

Wow, that makes too much sense and is extremely helpful.

I thought I had done enough of the right reading before jumping into this, but I think I was mistaken :x

Great info and thanks for all of your help Norie!