Use inputbox to add line to validation list

[jakobahman]

Hi, Im trying to add value to a data validation list using the guide at http://www.dailydoseofexcel.com/arch...ta-validation/

The only issue I have with it is that my validation list is in another sheet, and I can't get the code ti work with the changes suggested in the comments at that site.

David suggests that changing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Should work. But I get an "application defined or object defined error" at the line With Me.Parent.Names("=Prosjekt").RefersToRange.

Any ideas how I could get it to work?

My complete code is:

Please Login or Register  to view this content.

[DonkeyOte]

You wouldn't need the "=" when referring to the Name in the Workbook, ie the range is called "Prosjekt" not "=Prosjekt", that said I'm not sure that method will work depending on how Prosjekt is defined.

Might be better to post a sample file with the important pieces in place (ie the cell with validation and the sheet containing the validation list with defined name setup).

[jakobahman]

Thanks for the reply!

It seems to me that Me.Parent.Names isn't valid.

Because when I type

Please Login or Register  to view this content.

I don't get any suggestions from the VB-editor. I would expect "names" to be shown in a dropdownlist, just like "Parent" did.

So is that really correct?

[DonkeyOte]

Re: Me.Parent etc... yes you can access the named range that way, eg:

Please Login or Register  to view this content.

However your code doesn't really make a great deal of sense - esp. out of context. I suspect you're trying to add the new value (as input via InputBox dialog) to the original Data Validation list but how you do that will in part be determined by how the name is initially defined (ie is this a Dynamic Named Range)

As previously suggested posting a file will make life easier for all concerned.

(I have also moved this thread to the Programming Forum)

[jakobahman]

You are correct.

What I'm trying to create is a time registration-sheet. Where the users enter date, time spent and what project(prosjekt in Norwegian) they spent time with.

One row is one entry.
On each row I have a dropdown in column F where I want my users to select project from a dropdown that refers to defined range "Prosjekt" from sheet "list".

The first line in that range is the entry "<Nytt prosjekt>" (New project). When selected, I want an inputbox to appear where the user enters the new projectname which then will be added to the range.

(I also want them to enter contact for this project and what client it is in subsequent inputboxes, but I thought I would focus on that later).

THIS is what the code is supposed to do.

The range is defined as

Please Login or Register  to view this content.

Attached is the xlsm I'm working on if I wasn't clear enough.

[DonkeyOte]

So if you do as was suggested initially, ie change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and subsequently alter:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

what happens ?

you may also need to think about those "seemingly" project dependent fields when adding new projects, ie Kunde / Kontaktperson.

[jakobahman]

The same error still occurs on the same line...

I'm not very used to coding VBA, is there some way I can debug and get more information out of that line?

[DonkeyOte]

The code with the advised changes worked without incident for me I'm afraid.

[jakobahman]

Hmm. strange.

Did you get the same error as me without those changes?

[DonkeyOte]

This file has just two changes from the version you initially uploaded and they are per my prior post, this version works for me

(when running locally I disable the SelectionChange event given I do not have the Calendar Control installed on my client - else the code will not compile (expected))

[jakobahman]

I get the same error with your workbook.

Do you think

Please Login or Register  to view this content.

have some dependencies or addons required that I am missing?

Its an standard Excel 2007 installation(although not the complete Office 2007 installation).

[DonkeyOte]

There's not really anything I can add I'm afraid given the file I uploaded worked without incident.

[jakobahman]

Thanks for your help DonkeyOte.

I have now tried your uploaded file on 3 different machines with 3 different setups of office and I still get error 1004. (Office 2007 Eng + Norwegian and Office 2003 Norwegian)

Are you absolutely sure I don't need a specific library to make that line work?

Is there some way I can see what dependencies a function has? Ive tried to google "me.parent.names" but didn't come up with anything useful to help me understanding why this isn't working for me.

I am very grateful to you helping me here. I really appreciate it!

/Jakob

[romperstomper]

You'd be unlikely to find anything by Googling that. Me refers to the worksheet containing the code. Me.Parent refers to the workbook containing the worksheet. Then Me.Parent.Names refers to the Names collection of the workbook. You might try debugging by breaking down into individual steps:

Please Login or Register  to view this content.

and see where the error occurs now. If it's still at the me.parent line, then we can break it down further.

[jakobahman]

I got it to work by skipping the parent.names altogether.

I realized a simple hardcoded line will do for now.

So the solution is:

Please Login or Register  to view this content.

Im still curious as to why I couldn't get the other way to work...



Thanks for all the help!