Run time 9 error for some users but not everyone

[Shazbot79]

I have created a workbook that when opened compares the user's windows
username against a named range and launches one of 2 userforms
depending on whether they are in the list or not. When one of the
userform launches (Userform5) for some people it hits a runtime error
when trying to autropopulate the userform. For other people (including
me) it works fine. Here is the code:

Private Sub UserForm_Activate()

Dim rng As Range
Dim rng2 As Range
Dim cell As Range
Dim teamname As String
Dim address As String
Dim homephone As String
Dim mobile As String
Dim ext As String
Dim staff As String
Dim fullname As String
Dim c As Range

ComboBox1.AddItem "A&D" 'ListIndex = 0
ComboBox1.AddItem "A&D MANAGER" 'ListIndex = 1
ComboBox1.AddItem "DEPARTMENT MANAGER" 'ListIndex = 2
ComboBox1.AddItem "PROGRAMME" 'ListIndex = 3
ComboBox1.AddItem "PROGRAMME MANAGER" 'ListIndex = 4
ComboBox1.AddItem "SERVICE" 'ListIndex = 5
ComboBox1.AddItem "SERVICE MANAGER" 'ListIndex = 6
ComboBox1.AddItem "TECHNICAL" 'ListIndex = 7
ComboBox1.AddItem "TECHNICAL MANAGER" 'ListIndex = 8


TextBox8.Value = Sheets("User ID Control List").Range("userid").Value




With Sheets("Team Contact List").Range("StaffIDs")
If .Find(Sheets("User ID Control List").Range("userid").Value,
LookIn:=xlValues) Is Nothing Then
Sheets("EditSheet").Select
UserForm5.Hide
MsgBox "Cannot find your user ID in current staff list. Please add
your details."
UserForm3.Show




Else

Set c = .Find(TextBox8.Value, LookIn:=xlValues)
Application.GoTo c, True

teamname = c.Offset(0, -6).Value
address = c.Offset(0, -1).Value
homephone = c.Offset(0, -4).Value
mobile = c.Offset(0, -3).Value
ext = c.Offset(0, -2).Value
staff = c.Offset(0, 0).Value
fullname = c.Offset(0, -5).Value



TextBox1.Value = fullname

ComboBox1.Value = teamname

TextBox3.Value = address

TextBox4.Value = homephone

TextBox5.Value = mobile

TextBox6.Value = ext

TextBox7.Value = ""

TextBox8.Value = staff

Sheets("EditSheet").Select

End If
End With

End Sub





Here is the error:

Runtime error 9, subscript out of range
and it fails on this line:
TextBox8.Value = Sheets("User ID Control List").Range("userid").Value

I am entirely stuck and would really appreciate help on this one!
Thanks

 

[Barb Reinhardt]

What do you get if you do this

Debug.print Sheets("User ID Control List").Range("userid").Value

 

[Shazbot79]

Hi,
it returns the window's username.
Thanks

 

[Robert Flanagan]

It may be that the range name userid is multple cells long or not foun. The
..value property is for a single cell. When the error happens, do a watch on
range("userid").address. If userid is defined it will report the address.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Shazbot79]

The range points to one cell. If it helps, the error previously
occurred when trying to populate Combobox1 with a named range which
was on another sheet, but it threw out a 1004:runtime error method
'range' of object'_Global failed so I changed the code to

ComboBox1.AddItem "A&D" 'ListIndex = 0
ComboBox1.AddItem "A&D MANAGER" 'ListIndex = 1
ComboBox1.AddItem "DEPARTMENT MANAGER" 'ListIndex = 2
ComboBox1.AddItem "PROGRAMME" 'ListIndex = 3
ComboBox1.AddItem "PROGRAMME MANAGER" 'ListIndex = 4
ComboBox1.AddItem "SERVICE" 'ListIndex = 5
ComboBox1.AddItem "SERVICE MANAGER" 'ListIndex = 6
ComboBox1.AddItem "TECHNICAL" 'ListIndex = 7
ComboBox1.AddItem "TECHNICAL MANAGER" 'ListIndex = 8


and it then failed on the next attempt to use a named range....
TextBox8.Value = Sheets("User ID Control List").Range("userid").Value


It makes me think that perhaps it is something to do with access to
these ranges but other than that I'm stumped. The fact that it only
affects some users and not all throws me too.

 

[Dave Peterson]

You could add checks for everything that could go wrong...

Dim TestWks as worksheet
dim TestRng as range
'...

set testwks = nothing
on error resume next
set testwks = worksheets("User ID Control List")
on error goto 0

if testwks is nothing then
msgbox "The worksheet wasn't found!"
else
set testrng = nothing
on error resume next
set testrng = testwks.range("userid")
on error goto 0

if testrng is nothing then
msgbox "The range named UserId wasn't found--but the sheet was"
else
'check for things that could go wrong with that range
'too many cells, an error in the cell,...
'or just use:
me.TextBox8.Value = testrng.cells(1).text
end if
end if

 

[Shazbot79]

Great idea! Having done that it throws up the msgbox "The worksheet
wasn't found!". But I am still stuck....why would this worksheet exist
for some people and not for others. Also for one user it works
sometimes but not always. Why should rights to a worksheet be
intermittent? My VBA skills are fairly rudimentary so I don't have
enough knowledge of how rights to things work. Can anyone shed light?

 

[Phil Hibbs]

I am unfamiliar with the "worksheets" object... lets check the help...

"For an Application object, returns a Sheets collection that
represents all the worksheets in the active workbook"

Could the "active workbook" be changing?

Try accessing the workbook and worksheet explicitly via objects,
stored at the start before anything can change. Then work out where it
is changing, if it is.

Dim oBook As Workbook
Dim oSheet As Worksheet

Set oBook = ActiveWorkbook
Set oSheet = oBook.Sheets("User ID Control List")

Phil.

 

[Dave Peterson]

My bet is that the wrong workbook is active.

I'd add another msgbox:

msgbox activeworkbook.fullname

Maybe it's out of date (same name, but from the wrong folder???). Or maybe it's
just not the correct workbook because of a user choice.

 

[Shazbot79]

thanks to all your help I'm a little further forward.

I added msgbox activeworkbook.fullname to run when the workbook opens.
For those people not having the issue the workbook is displayed along
with its DOS filepath, for those having the problem the workbook is
displayed in the msgbox along with the UNC filepath. Is there any code
I can add that will force Excel to use the DOS filepath in the
properties of the workbook?

Thanks again for your efforts so far.

 

[Dave Peterson]

First, it shouldn't matter if the user is opening the workbook using the UNC
path or a mapped drive--unless you have two different versions of the file.

There are API functions that will convert mapped drives to its UNC path.

But there aren't any that will convert a UNC path to a mapped drive if the user
doesn't have that UNC path already mapped.

I think you're going down the wrong road thinking that the problem is with the
wrong path.

I'd still bet that the users with problems opening the wrong workbook.