Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does combobox lose choices
Working on an process where a user enters their id in a form to determine
what role they will have in the application (admin, engineer, manager, etc.), if a single role is found for the user then it continues on with no selection process, but if the user has multiple roles they can take I have the form display a combobox for them to select their role for this process. Problem the first choice always seems to disappear from the drop down list if I run it from excel, if I step through the form in VB editor the list is ok all choices appear. The spread sheet always has all of the role entries, the range name list always includes all entries, but when the form is called from excel using Load UserEntry UserEntry.Show what ever the first choice is in the list disappears from the list My code once the userid has been entered is as follows Beginning: If UserName.Value = "" Then ErrorOk "User Name must be entered to continue" Exit Sub Else ' ' Verify that the user exists in the database WUserId = UserName.Value Found = False dbName = "MLMBudget.mdb" tbName = "Personnel" WSql = "Select UserId From Personnel Where UserId=""" + WUserId + """" VerifyEntry ' ' If user id not found have them enter their profile and select roles If Not Found Then Load UserUpdate UserUpdate.Show End If ' ' If user id found or entered ' Select the data from the table and place on the sheet to create a name list tbName = "UserPriv" WSql = "Select Role From UserPriv Where UserId=""" + WUserId + """" QueryTable 2, 1 ' ' Create the name list and display combobox for selection If NRec 1 Then Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select Selection.Name = "RoleList" Role.Visible = True Label2.Visible = True Else Range("A2").select WRole = ActiveCell.Value UserEntry.Hide Unload UserEntry End If End If End Sub Is there a property flag or some setting that would cause this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does combobox lose choices
Try changing
Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select to Range("A2").Resize(Nrec,1).Select -- Regards, Tom Ogilvy "slicerdh" wrote in message ... Working on an process where a user enters their id in a form to determine what role they will have in the application (admin, engineer, manager, etc.), if a single role is found for the user then it continues on with no selection process, but if the user has multiple roles they can take I have the form display a combobox for them to select their role for this process. Problem the first choice always seems to disappear from the drop down list if I run it from excel, if I step through the form in VB editor the list is ok all choices appear. The spread sheet always has all of the role entries, the range name list always includes all entries, but when the form is called from excel using Load UserEntry UserEntry.Show what ever the first choice is in the list disappears from the list My code once the userid has been entered is as follows Beginning: If UserName.Value = "" Then ErrorOk "User Name must be entered to continue" Exit Sub Else ' ' Verify that the user exists in the database WUserId = UserName.Value Found = False dbName = "MLMBudget.mdb" tbName = "Personnel" WSql = "Select UserId From Personnel Where UserId=""" + WUserId + """" VerifyEntry ' ' If user id not found have them enter their profile and select roles If Not Found Then Load UserUpdate UserUpdate.Show End If ' ' If user id found or entered ' Select the data from the table and place on the sheet to create a name list tbName = "UserPriv" WSql = "Select Role From UserPriv Where UserId=""" + WUserId + """" QueryTable 2, 1 ' ' Create the name list and display combobox for selection If NRec 1 Then Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select Selection.Name = "RoleList" Role.Visible = True Label2.Visible = True Else Range("A2").select WRole = ActiveCell.Value UserEntry.Hide Unload UserEntry End If End If End Sub Is there a property flag or some setting that would cause this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does combobox lose choices
Didn't resolve the issue list choices are Admin, Engineer, Manager, and
Planner but Engineer, Manager, and Planner show up when ran from the show command in the sheet macro, but all 4 show up when I step through the form. Dave "Tom Ogilvy" wrote: Try changing Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select to Range("A2").Resize(Nrec,1).Select -- Regards, Tom Ogilvy "slicerdh" wrote in message ... Working on an process where a user enters their id in a form to determine what role they will have in the application (admin, engineer, manager, etc.), if a single role is found for the user then it continues on with no selection process, but if the user has multiple roles they can take I have the form display a combobox for them to select their role for this process. Problem the first choice always seems to disappear from the drop down list if I run it from excel, if I step through the form in VB editor the list is ok all choices appear. The spread sheet always has all of the role entries, the range name list always includes all entries, but when the form is called from excel using Load UserEntry UserEntry.Show what ever the first choice is in the list disappears from the list My code once the userid has been entered is as follows Beginning: If UserName.Value = "" Then ErrorOk "User Name must be entered to continue" Exit Sub Else ' ' Verify that the user exists in the database WUserId = UserName.Value Found = False dbName = "MLMBudget.mdb" tbName = "Personnel" WSql = "Select UserId From Personnel Where UserId=""" + WUserId + """" VerifyEntry ' ' If user id not found have them enter their profile and select roles If Not Found Then Load UserUpdate UserUpdate.Show End If ' ' If user id found or entered ' Select the data from the table and place on the sheet to create a name list tbName = "UserPriv" WSql = "Select Role From UserPriv Where UserId=""" + WUserId + """" QueryTable 2, 1 ' ' Create the name list and display combobox for selection If NRec 1 Then Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select Selection.Name = "RoleList" Role.Visible = True Label2.Visible = True Else Range("A2").select WRole = ActiveCell.Value UserEntry.Hide Unload UserEntry End If End If End Sub Is there a property flag or some setting that would cause this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does combobox lose choices
Sounds like an unexpected sheet is Active when you run your code and perhaps
it has your list starting in A1 rather than A2. After you run your code and get a truncated list, do F2 and select RoleList and see where you end up. Does it highlight 4 cells with all the choices? -- Regards, Tom Ogilvy "Dave" wrote in message ... Didn't resolve the issue list choices are Admin, Engineer, Manager, and Planner but Engineer, Manager, and Planner show up when ran from the show command in the sheet macro, but all 4 show up when I step through the form. Dave "Tom Ogilvy" wrote: Try changing Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select to Range("A2").Resize(Nrec,1).Select -- Regards, Tom Ogilvy "slicerdh" wrote in message ... Working on an process where a user enters their id in a form to determine what role they will have in the application (admin, engineer, manager, etc.), if a single role is found for the user then it continues on with no selection process, but if the user has multiple roles they can take I have the form display a combobox for them to select their role for this process. Problem the first choice always seems to disappear from the drop down list if I run it from excel, if I step through the form in VB editor the list is ok all choices appear. The spread sheet always has all of the role entries, the range name list always includes all entries, but when the form is called from excel using Load UserEntry UserEntry.Show what ever the first choice is in the list disappears from the list My code once the userid has been entered is as follows Beginning: If UserName.Value = "" Then ErrorOk "User Name must be entered to continue" Exit Sub Else ' ' Verify that the user exists in the database WUserId = UserName.Value Found = False dbName = "MLMBudget.mdb" tbName = "Personnel" WSql = "Select UserId From Personnel Where UserId=""" + WUserId + """" VerifyEntry ' ' If user id not found have them enter their profile and select roles If Not Found Then Load UserUpdate UserUpdate.Show End If ' ' If user id found or entered ' Select the data from the table and place on the sheet to create a name list tbName = "UserPriv" WSql = "Select Role From UserPriv Where UserId=""" + WUserId + """" QueryTable 2, 1 ' ' Create the name list and display combobox for selection If NRec 1 Then Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select Selection.Name = "RoleList" Role.Visible = True Label2.Visible = True Else Range("A2").select WRole = ActiveCell.Value UserEntry.Hide Unload UserEntry End If End If End Sub Is there a property flag or some setting that would cause this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does combobox lose choices
Tried the F2 didn't get a selection, however in the "Name List" drop down the
name of RoleList appears and when I select it all choices are highlighted, if I select a single cell the "Name List" box shows the cell address when I highlight all the choice cells RoleList appears in the box. So this is the way its suppose to be but I still have choice number 1 disappearing from my drop down. Dave "Tom Ogilvy" wrote: Sounds like an unexpected sheet is Active when you run your code and perhaps it has your list starting in A1 rather than A2. After you run your code and get a truncated list, do F2 and select RoleList and see where you end up. Does it highlight 4 cells with all the choices? -- Regards, Tom Ogilvy "Dave" wrote in message ... Didn't resolve the issue list choices are Admin, Engineer, Manager, and Planner but Engineer, Manager, and Planner show up when ran from the show command in the sheet macro, but all 4 show up when I step through the form. Dave "Tom Ogilvy" wrote: Try changing Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select to Range("A2").Resize(Nrec,1).Select -- Regards, Tom Ogilvy "slicerdh" wrote in message ... Working on an process where a user enters their id in a form to determine what role they will have in the application (admin, engineer, manager, etc.), if a single role is found for the user then it continues on with no selection process, but if the user has multiple roles they can take I have the form display a combobox for them to select their role for this process. Problem the first choice always seems to disappear from the drop down list if I run it from excel, if I step through the form in VB editor the list is ok all choices appear. The spread sheet always has all of the role entries, the range name list always includes all entries, but when the form is called from excel using Load UserEntry UserEntry.Show what ever the first choice is in the list disappears from the list My code once the userid has been entered is as follows Beginning: If UserName.Value = "" Then ErrorOk "User Name must be entered to continue" Exit Sub Else ' ' Verify that the user exists in the database WUserId = UserName.Value Found = False dbName = "MLMBudget.mdb" tbName = "Personnel" WSql = "Select UserId From Personnel Where UserId=""" + WUserId + """" VerifyEntry ' ' If user id not found have them enter their profile and select roles If Not Found Then Load UserUpdate UserUpdate.Show End If ' ' If user id found or entered ' Select the data from the table and place on the sheet to create a name list tbName = "UserPriv" WSql = "Select Role From UserPriv Where UserId=""" + WUserId + """" QueryTable 2, 1 ' ' Create the name list and display combobox for selection If NRec 1 Then Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select Selection.Name = "RoleList" Role.Visible = True Label2.Visible = True Else Range("A2").select WRole = ActiveCell.Value UserEntry.Hide Unload UserEntry End If End If End Sub Is there a property flag or some setting that would cause this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does combobox lose choices
If rolelist is the rowsource or listfillrange property, then I can't say how
you could lose an item from the list. I have never seen any complaints about this - my guess is it is reflecting what is defined at that time and somehow, rolelist doesn't include all choices. -- Regards, Tom Ogilvy "Dave" wrote in message ... Tried the F2 didn't get a selection, however in the "Name List" drop down the name of RoleList appears and when I select it all choices are highlighted, if I select a single cell the "Name List" box shows the cell address when I highlight all the choice cells RoleList appears in the box. So this is the way its suppose to be but I still have choice number 1 disappearing from my drop down. Dave "Tom Ogilvy" wrote: Sounds like an unexpected sheet is Active when you run your code and perhaps it has your list starting in A1 rather than A2. After you run your code and get a truncated list, do F2 and select RoleList and see where you end up. Does it highlight 4 cells with all the choices? -- Regards, Tom Ogilvy "Dave" wrote in message ... Didn't resolve the issue list choices are Admin, Engineer, Manager, and Planner but Engineer, Manager, and Planner show up when ran from the show command in the sheet macro, but all 4 show up when I step through the form. Dave "Tom Ogilvy" wrote: Try changing Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select to Range("A2").Resize(Nrec,1).Select -- Regards, Tom Ogilvy "slicerdh" wrote in message ... Working on an process where a user enters their id in a form to determine what role they will have in the application (admin, engineer, manager, etc.), if a single role is found for the user then it continues on with no selection process, but if the user has multiple roles they can take I have the form display a combobox for them to select their role for this process. Problem the first choice always seems to disappear from the drop down list if I run it from excel, if I step through the form in VB editor the list is ok all choices appear. The spread sheet always has all of the role entries, the range name list always includes all entries, but when the form is called from excel using Load UserEntry UserEntry.Show what ever the first choice is in the list disappears from the list My code once the userid has been entered is as follows Beginning: If UserName.Value = "" Then ErrorOk "User Name must be entered to continue" Exit Sub Else ' ' Verify that the user exists in the database WUserId = UserName.Value Found = False dbName = "MLMBudget.mdb" tbName = "Personnel" WSql = "Select UserId From Personnel Where UserId=""" + WUserId + """" VerifyEntry ' ' If user id not found have them enter their profile and select roles If Not Found Then Load UserUpdate UserUpdate.Show End If ' ' If user id found or entered ' Select the data from the table and place on the sheet to create a name list tbName = "UserPriv" WSql = "Select Role From UserPriv Where UserId=""" + WUserId + """" QueryTable 2, 1 ' ' Create the name list and display combobox for selection If NRec 1 Then Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select Selection.Name = "RoleList" Role.Visible = True Label2.Visible = True Else Range("A2").select WRole = ActiveCell.Value UserEntry.Hide Unload UserEntry End If End If End Sub Is there a property flag or some setting that would cause this? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does combobox lose choices
Thnaks for your suggestions, This has had me stumped for a couple of weeks
now and I have not been able to find a problem any where, Oh well I'll keep trying Thanks Dave "Tom Ogilvy" wrote: If rolelist is the rowsource or listfillrange property, then I can't say how you could lose an item from the list. I have never seen any complaints about this - my guess is it is reflecting what is defined at that time and somehow, rolelist doesn't include all choices. -- Regards, Tom Ogilvy "Dave" wrote in message ... Tried the F2 didn't get a selection, however in the "Name List" drop down the name of RoleList appears and when I select it all choices are highlighted, if I select a single cell the "Name List" box shows the cell address when I highlight all the choice cells RoleList appears in the box. So this is the way its suppose to be but I still have choice number 1 disappearing from my drop down. Dave "Tom Ogilvy" wrote: Sounds like an unexpected sheet is Active when you run your code and perhaps it has your list starting in A1 rather than A2. After you run your code and get a truncated list, do F2 and select RoleList and see where you end up. Does it highlight 4 cells with all the choices? -- Regards, Tom Ogilvy "Dave" wrote in message ... Didn't resolve the issue list choices are Admin, Engineer, Manager, and Planner but Engineer, Manager, and Planner show up when ran from the show command in the sheet macro, but all 4 show up when I step through the form. Dave "Tom Ogilvy" wrote: Try changing Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select to Range("A2").Resize(Nrec,1).Select -- Regards, Tom Ogilvy "slicerdh" wrote in message ... Working on an process where a user enters their id in a form to determine what role they will have in the application (admin, engineer, manager, etc.), if a single role is found for the user then it continues on with no selection process, but if the user has multiple roles they can take I have the form display a combobox for them to select their role for this process. Problem the first choice always seems to disappear from the drop down list if I run it from excel, if I step through the form in VB editor the list is ok all choices appear. The spread sheet always has all of the role entries, the range name list always includes all entries, but when the form is called from excel using Load UserEntry UserEntry.Show what ever the first choice is in the list disappears from the list My code once the userid has been entered is as follows Beginning: If UserName.Value = "" Then ErrorOk "User Name must be entered to continue" Exit Sub Else ' ' Verify that the user exists in the database WUserId = UserName.Value Found = False dbName = "MLMBudget.mdb" tbName = "Personnel" WSql = "Select UserId From Personnel Where UserId=""" + WUserId + """" VerifyEntry ' ' If user id not found have them enter their profile and select roles If Not Found Then Load UserUpdate UserUpdate.Show End If ' ' If user id found or entered ' Select the data from the table and place on the sheet to create a name list tbName = "UserPriv" WSql = "Select Role From UserPriv Where UserId=""" + WUserId + """" QueryTable 2, 1 ' ' Create the name list and display combobox for selection If NRec 1 Then Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select Selection.Name = "RoleList" Role.Visible = True Label2.Visible = True Else Range("A2").select WRole = ActiveCell.Value UserEntry.Hide Unload UserEntry End If End If End Sub Is there a property flag or some setting that would cause this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Choices | Excel Discussion (Misc queries) | |||
Nested IF with = and <= choices | Excel Worksheet Functions | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Choices | Excel Worksheet Functions | |||
poping up a box with choices? | Excel Programming |