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" wrote in message
...
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