adding new items to a dropdown

[northernstar]

i have a file (which is attached to another thread) and i would like the user to beable to add new manufacturers names to the dropdown and for these to be saved for all users

the file is going to be on a network as a read only file

any ideas to point me in the right direction

any questions let me know

thanks

[lucas]

Well, you could have at least given us a link to the other thread so we could see the file.

If the file is going to be read only then you won't be able to write to it unless you have permissions for removing the read only property...write to it and then re protect it...do you have those kinds of permissions?

Better question, will your users have those permissions and if they do what is the point of it being read only?

[lucas]

Another question that comes to mind which you didn't tell us and didn't give us the file....is this a validation drop down...if so the answer is yes to the first part. If it is a combobox then probably yes also.

[northernstar]

here is the link to the other thread with the file

http://vbaexpress.com/forum/showthread.php?t=14938

the users are only going to have general permission, no special permission this is to prevent them from do any damage

it is a combobox

hope this helps

thanks again

[lucas]

I used a named range for the rowsource for the manufacurers combobox. Go to insert-name-define to see how the named range is set up to be dynamic...if you add to or subtract from the column then the range adjusts to the changes.

The rowsource is set in the properties of the combobox14. You will find that it reads "Data" which is the name of the dynamic named range.

The code to add entries through the combobox is:

Private Sub ComboBox14_AfterUpdate()
Dim Rng As Range, c As Range
Set Rng = Range("Data")
Set c = Rng.Find(ComboBox14)
If c Is Nothing Then
    Rng(Rng.Cells.Count).Offset(1) = ComboBox14.Value
End If
End Sub

It goes in the code module of the userform2.
One thing you will notice is that the item is not immediatly added to the combobox but is added to the named range on the sheet so the next time you open the userform your new entry will show. That should not be a problem though but just don't expect it to be there until you close and reopen the userform.

[northernstar]

thanks for the code

it doesnt seem to want to work

fails at

Rng(Rng.Cells.Count).Offset(1) = ComboBox14.Value

not sure why

any ideas
thanks

[lucas]

open the file I sent you in the previous post.
hit the button to open the userform
enter some data into the Manufacturer combobox and hit enter
close the form
open the form
drop the manufacturer combobox and look for your new entry.
I get no error.

[northernstar]

just tried it on your file and it is working fine, thanks

must have done some typing error on my version

not to worry can put this option back in

thanks again

[lucas]

You need to read my post#5 again..carefully....you must have a named range "Data" or adjust the code accordingly just for starters.

[northernstar]

just posted another query you maybe able to help me with?

http://vbaexpress.com/forum/showthread.php?t=14969