VBA case lookup name range on different sheet

[bazfalty]

I am trying to do a dropdown combo box with the first combo box named "shift" and the second "Name" .

If "shift" = Green for "name" to look on a sheet called Shift_names at a named range called "green" and list in in the drop down

I currenlty have the code below where am i going wrong I have all my other code for the userform working and adding to a data collection sheet as entries are added.

Thanks

[6StringJazzer]

You never described your problem. What do you expect to happen, and what is happening instead?

Just looking at your code I suspect there is an issue in the range names. Omit the "Shift_Names!" part and just use the range names.

An image is almost never helpful. It would have been easier to copy your code and paste it in CODE tags, rather than taking a screenshot then attaching that. It would have been even better to attach your file so I could test my solution before I gave it to you.

[bazfalty]

Ive attached the book.

On the homepage when clicking 'enter standard work result' when filling in the data it currently saves it to a sheet called Data which is all working well. The only bit I cannot do is on the dropdown combo boxes if shift = (colour) it looks for the name range on sheet 'shift_names' and looks for that name range and put it in the drop ddown combo box called names on the userform

I'm pretty new to using any type opf coding and completed the rest following youtube tutorials.

Thanks

[6StringJazzer]

I was baffled because the code you showed in your first post does not appear in this file. So I started from scratch. I have written code that does what I think you want.

First, never name anything "Name". That is because Name is a built-in attribute for many things in the VBA object model. When you refer to Me.Name, you are referring to the name of the userform, not the combobox. I have changed your combobox name to ShiftName. If you don't like that you can change it to anything you want.

Second, I have initialized your Shift combobox index to -1 so it is blank when first displayed. You want to force your user to make a selection, rather than mistakenly allowing them to select Green by doing nothing, which was displayed when the form came up. The only time you want a combobox to show a default value is when it is a true default, that is, you are OK if the user doesn't change it.

Also, here is a tip from a user interface design standpoint. Selecting the Name combobox doesn't make sense until you have first selected a value from the Shift combobox. So to enforce that order I have added code that disables Name until Shift has a selection.

Last, I strongly recommend to everyone that they declare variables. Doing so prevents a lot of bugs and runtime errors.

[bazfalty]

I was baffled because the code you showed in your first post does not appear in this file. So I started from scratch. I have written code that does what I think you want.

First, never name anything "Name". That is because Name is a built-in attribute for many things in the VBA object model. When you refer to Me.Name, you are referring to the name of the userform, not the combobox. I have changed your combobox name to ShiftName. If you don't like that you can change it to anything you want.

Second, I have initialized your Shift combobox index to -1 so it is blank when first displayed. You want to force your user to make a selection, rather than mistakenly allowing them to select Green by doing nothing, which was displayed when the form came up. The only time you want a combobox to show a default value is when it is a true default, that is, you are OK if the user doesn't change it.

Also, here is a tip from a user interface design standpoint. Selecting the Name combobox doesn't make sense until you have first selected a value from the Shift combobox. So to enforce that order I have added code that disables Name until Shift has a selection.

Last, I strongly recommend to everyone that they declare variables. Doing so prevents a lot of bugs and runtime errors.

Great stuff, Thank you for doing this. People on here are so helpful, you have made my day.

[6StringJazzer]

Glad to help, and thanks for the rep!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

[bazfalty]

Can you help one more time.

when it saves the date it inserts in the data sheet as text so when I try and do a pivot chart with a timeline slicer it says no dates available. I have changed the date cell format to date and it still wont work

edit

ive solved it. thanks again