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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
What do you get if you do this
Debug.print Sheets("User ID Control List").Range("userid").Value -- HTH, Barb Reinhardt "Shazbot79" wrote: 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
Hi,
it returns the window's username. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
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 wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
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 wrote: 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? -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time 9 error for some users but not everyone
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. Shazbot79 wrote: 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 |
Reply |
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 |