Excel prompt (97)

[scrappe7]

i don't think it is possible to add a name to a list box in any convenient manner since nobody has the answer yet from a previous post so I would like to try another way. So another way to solve my problem could be this way.

How could i designate cell B1 of a sheet to be kind of like a user prompt cell. The user would enter a name in the cell. The tricky part, this cell would then appens its value to the bottom of the list of values in Column A. You see Column A gets its values from an access export, it contains a list of names. This column is then linked to a data entry sheet and used as the values for a drop down list. I have a problem when a new name is to be entered in the data entry and thus its not ont he list already. Can a list box and a normal cell exist as one??? i figure a macro that would take the user to the file where the export occurs and lets them enter the new name in B1 and then automatically put that name at the end of the list would do the trick. This way the list that has the references would get auto-updated and everyone's happy. I've tried to insert the name name at the top of the list but it wont upidate the data entry list, so its no good. ANy idea's anyone???? please i'm im a big jam.

[Andrew Cronnolly]

Can you insert the new name into second or third row of the rather than appending it at the bottom, as that way the range will expand to include it. For example if the List Fill Range is A2:A100, if you insert a cell between A2 and A3, the range in th elist box should expand to A2:A101, and incorporate the new name.

Does that help ?

Andrew C

[jujuraf]

I assume you're looking for VBA code? I can think of two solutions which would auto-populate the list box based on what's in the new range (set of columns). Here's one:

Your names are written to columnB. If you assign a range name to columnB (call it "dbNames") then assuming your list box is on this same worksheet, use that sheet's selectionchange() event to do the list box update when anything new is added to that range of cells. You could also resort the list each time a new item is added (I didn't do that, but it's easy).

I wrote this and it worked for me (tested in xl2000):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim k As Integer

If Not Intersect(Target, Range("dbNames")) Is Nothing Then
'repopulate listbox
lstNames.Clear
For k = 1 To Range("dbNames").CurrentRegion.Rows.Count
Debug.Print "adding: " & Range("dbNames").Cells(k, 1)
lstNames.AddItem Range("dbNames").Cells(k, 1)
Next k

End If

End Sub

I had a list of names in column b of a worksheet, assigned the name 'dbNames' to that column and then dropped a list box on this same sheet. I then added this code to that worksheet's event and everytime anything in column B gets changed,the list box gets updated.

Attached is the sample worksheet I wrote.
Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>