Below is a sub routine that worked great on my machine but when I gave it to
a user, I would get a subscript out or range error. This was due to not
having the .xls extension in the wbname variable or in the windows ( )
activate piece of the code. When calling a file does it always need the
..xls extension?

Sub See_Vendor()
x = ActiveWorkbook.Name

wbname = "Current Vendors.xls" <<<<<<<<<<<<=========

Call WorkbookIsOpen(wbname)
If Err < 0 Then
Workbooks.Open Filename:="s:\finance\acct-gl\current vendors.xls"
End If

Windows("current vendors.xls").Activate <<<<<<<<<<<<<============

For Each n In ActiveWorkbook.Names
n.Delete
Next n
Range("a2").Select
Top = ActiveCell.Address
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Select
bottom = ActiveCell.Address
Worksheets("Sheet1").Range(Top, bottom).Name = "Vendor"

Load Vendor_Lookup
Windows(x).Activate
Vendor_Lookup.Show

End Sub

You show these lines of code...

x = ActiveWorkbook.Name
wbname = "Current Vendors.xls"

However, the x variable is not used in the rest of your code... was the
assignment to wbname supposed to be x or is it a hard-coded value you user
types in? If you were supposed to be assigning x, then it should have the
xls extension IF the workbook has been saved at least once. So, I'll assume
the problem is because the xls extension was not provided by your user. If
you don't think you can impress upon him/her the importance of providing the
extension, then you can have your code do it for them. Just add this line of
code after the wbname assignment statement...

If LCase(Left(wbname, 4)) < ".xls" Then wbname = wbname & ".xls"

--
Rick (MVP - Excel)


"Dan" wrote in message
...
Below is a sub routine that worked great on my machine but when I gave it
to
a user, I would get a subscript out or range error. This was due to not
having the .xls extension in the wbname variable or in the windows ( )
activate piece of the code. When calling a file does it always need the
.xls extension?

Sub See_Vendor()
x = ActiveWorkbook.Name

wbname = "Current Vendors.xls" <<<<<<<<<<<<=========

Call WorkbookIsOpen(wbname)
If Err < 0 Then
Workbooks.Open Filename:="s:\finance\acct-gl\current vendors.xls"
End If

Windows("current vendors.xls").Activate <<<<<<<<<<<<<============

For Each n In ActiveWorkbook.Names
n.Delete
Next n
Range("a2").Select
Top = ActiveCell.Address
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Select
bottom = ActiveCell.Address
Worksheets("Sheet1").Range(Top, bottom).Name = "Vendor"

Load Vendor_Lookup
Windows(x).Activate
Vendor_Lookup.Show

End Sub

See http://www.cpearson.com/excel/FileExtensions.aspx for a great deal
of info about the significance of the file extension and the "Hide
Extensions For Known File Types" Windows setting.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 24 Jun 2009 10:25:01 -0700, Dan
wrote:

Below is a sub routine that worked great on my machine but when I gave it to
a user, I would get a subscript out or range error. This was due to not
having the .xls extension in the wbname variable or in the windows ( )
activate piece of the code. When calling a file does it always need the
.xls extension?

Sub See_Vendor()
x = ActiveWorkbook.Name

wbname = "Current Vendors.xls" <<<<<<<<<<<<=========

Call WorkbookIsOpen(wbname)
If Err < 0 Then
Workbooks.Open Filename:="s:\finance\acct-gl\current vendors.xls"
End If

Windows("current vendors.xls").Activate <<<<<<<<<<<<<============

For Each n In ActiveWorkbook.Names
n.Delete
Next n
Range("a2").Select
Top = ActiveCell.Address
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Select
bottom = ActiveCell.Address
Worksheets("Sheet1").Range(Top, bottom).Name = "Vendor"

Load Vendor_Lookup
Windows(x).Activate
Vendor_Lookup.Show

End Sub