Data Validation using Named Dynamic Range on Another Sheet

Question

HI All,

I am a relative newcomer to VBA. My question is as below.

I am trying to validate data on sheet4 via a combo box, using a named dynamic range on sheet1. When I use a named dynamic range on sheet4 (to validate the data on the same sheet) it all works fine. I have used the code from the Contextures site to create the combo box and to name the range. I have inserted and named the dynamic range on both sheet4 and sheet1( in an effort to solve it), but it treats them the same.It picks up the range as defined, but the combo box changes its behaviour, viz:

1. It does NOT use the combo box properties as I defined them ,i.e. different font and length of drop-down. It reverts to its default settings for these.
2. It does not display the active cell contents in the combo box. It displays the same name no matter which cell is selected.(I don't see how it is selecting this name for display).

The code I am using for the named dynamic range is:

=OFFSET(Income!$B$2,0,0,COUNTA(Income!$B:$B),1)

I guess I am doing something wrong, but after several hours I am just not going to see it.

Any suggestions would be very welcome. Thank you.

Answer 1

Hello, Tom. Thanks for your response.

Because I am relatively new to VBA I'm not sure what you mean by "workbook level name" and "worksheet level name", in this particular context.

On the other point, I assume by "referencing the range directly in the control" you mean enter the range into the Rowsource property of the combo box. Well, while creating a combo box on a new workbook I noticed that there is no Rowsource property?!? Also, I'm not sure how to enter a range into it, even if it was there. (I thought it accepted only a cell reference.)

Also, if I try to look at the Properties of the combo box while the validation is pointing at the "other" sheet, i am unable to select the combo box, even though it appears on the sheet, when I click Design Mode in the Toolbox. However, when I point the validation back to a list on "this" sheet, I am able to select the combo box and view the Properties as normal.

Lastly, I thought it was necessary to use a dynamic named range if, as is the case here, items may be added to it at any time.

It just seems to get curioser and curioser!

Thank you once again.

Bobbler

Answer 2

A control toolbox/activeX combobox does not have any problem binding to a range on another sheet.  You don't need a dynamic named range to do this.  The contextures site has several sample workbooks that use an activeX combobox to simulate a data validation dropdown.   So I don't know what she has written in terms of the dynamic named range in your case.  but you may be able to solve your problem just by referencing the range directly in the control. 

It sounds like you might have a workbook level name and a worksheet level name  with the same name.  This might be creating a conflict depending on what sheet is active.  You might want to start again in a new workbook and implement the Debra's suggestion exactly as written and get it working first before you try changing things.

---

--

Tom Ogilvy

note: If you receive an answer to your question - please mark that answer or answers so others know the question has been answered.

Answer 3

Hello again, Hans. Thanks for replying.

I haven't used the BeforeDoubleClick event procedure, but I did use the DataValComboboxClick event procedure. As well as the KeyDown event procedure. They are both in the worksheet module and I created the combo box via the Control Toolbox - just exactly as specified at Contextures. It all woks beautifully on Sheet4, where the combobox was created, but not when I point the data validation to Sheet1. Because of this, I copied the validation data from Sheet1 to Sheet4 (when the workbook opens) and then hid the column. This is OK, but when new rows are added to Sheet4 the imported data shifts down. It is a workaround of sorts, but starting to get rather clumsy if I now have to re-import the validation data after each new row is added.

I just find it strange that through all ny searching I can't find anyone else having this problem, so I must assume that I am doing something to cause this.

I have also re-created Sheet4 and the combobox from scratch a couple of times, following the exact instructions from Contextures, but still the same.

The search goes on....

P.S.:  A HUGE THANK YOU TO CONTEXTURES FOR THEIR FANTASTIC SITE, and to everyone for trying to help. Duffers like me would be lost without dudes like you.

Answer 4

Have you copied the Worksheet_BeforeDoubleClick and Worksheet_SelectionChange event procedures into the worksheet module? They need to be there, they won't work in a standard module.

(You *have* used a combo box from the Control Toolbox, haven't you? A combo box from the Forms toolbar won't work for this purpose)