Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#NAME? Some users get error some don't? | Excel Discussion (Misc queries) | |||
More users working in the same excel at the same time? | Excel Programming | |||
Time format and various users | Excel Programming | |||
Time format and various users | Excel Programming | |||
Time format and various users | Excel Programming |