Steve,
on a train at moment & just seen your respone - I have amended code a
submitted earlier to pick up all files in specified directory and show then
in dropdown.
Not tested but should work although you should be able to figure out any
changes needed.
Hope helpful

Sub CreateMenuBar()
Dim strFile
Dim strFolder
Dim mydata As String

'Folder where your workbooks are located
' change as required
strFolder = ThisWorkbook.Path & "\"

strFile = Dir(strFolder & "*.*", vbNormal)


DeleteBar

With Application.CommandBars.Add
.Name = "myfiles"
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating


With .Controls.Add(Type:=msoControlDropdown, temporary:=True)

.Tag = "filenames"
.Width = 200

Do While strFile < ""

.AddItem strFile


strFile = Dir


Loop


.OnAction = "testfile"

.ListIndex = 0

End With

With .Controls.Add(Type:=msoControlButton, temporary:=True)

.Width = 50
.Caption = "Close"
.Style = msoButtonIconAndCaption
.Enabled = True
.OnAction = "DeleteBar"

End With

End With
End Sub

Sub testfile()

Set ctrl = Application.CommandBars("myfiles").FindControl(Tag :="filenames")
MsgBox ctrl.Text

End Sub

Sub DeleteBar()
On Error Resume Next
Application.CommandBars("myfiles").Delete
On Error GoTo 0
End Sub



--
jb


"Steve" wrote:

Joel & John & all,

Sorry, I guess I got my lingo mixed up. The user is saving workbook A (the
whole xls file) with a different name, not just a worksheet within a
workbook. I am using a range or ranges workbook B to update a range or
ranges in the "Data" sheet of workbook A. So with that clarification (I
hope) I will restate the questions ....

Issue 1 The users have renamed workbook A, so when they click the button
in workbook B I would like them to be able to choose from a list of Excel
files in that directory the name of the workbook (herein called "A") that
they want to update. This would be simpler than typing in the name. How do I
program this?

Issue 2 I don't want to create external references or mess up the named
ranges in workbook A. How can I avoid this?