Working on an application which uses a form to ask the user to enter an ID to
determine the role of the user (admin, engineer, manager, etc..). The user
id's and roles are stored in an access database and brought into excel
through queries. If the user only has one role then the application moves
forward with out a selection process, however if the user has multiple roles
then the form displays a combobox to make their role selection.

However when I call the form from an excel macro using

Load UserEntry
UserEntry.Show

the combobox loses the first choice from the list, but if I step throught
the form in VB editor the combobox does not lose any choices. I have
verified that not matter which direction all choices are selected and placed
on the spread sheet and that all choices are then selected and given a range
name list. So it's not missing data or improper name list possitioning. My
code is as follows and its pretty simple but I can't figure this out.

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
If Not Found Then
Load UserUpdate
UserUpdate.Show
End If
'
' If user id found or entered
' Select the role 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

The problem always occurs when called from excel and is always ok when
stepped through in VB, is there a property or setting that I'm not setting
properly?

Thanks

Dave

Try changing

Range("A2:" + ActiveCell.Offset(NRec - 1, 0).Address).Select

to

Range("A2").Resize(Nrec,1).Select

--
Regards,
Tom Ogilvy


"Dave" wrote in message
...
Working on an application which uses a form to ask the user to enter an ID
to
determine the role of the user (admin, engineer, manager, etc..). The user
id's and roles are stored in an access database and brought into excel
through queries. If the user only has one role then the application moves
forward with out a selection process, however if the user has multiple
roles
then the form displays a combobox to make their role selection.

However when I call the form from an excel macro using

Load UserEntry
UserEntry.Show

the combobox loses the first choice from the list, but if I step throught
the form in VB editor the combobox does not lose any choices. I have
verified that not matter which direction all choices are selected and
placed
on the spread sheet and that all choices are then selected and given a
range
name list. So it's not missing data or improper name list possitioning. My
code is as follows and its pretty simple but I can't figure this out.

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
If Not Found Then
Load UserUpdate
UserUpdate.Show
End If
'
' If user id found or entered
' Select the role 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

The problem always occurs when called from excel and is always ok when
stepped through in VB, is there a property or setting that I'm not setting
properly?

Thanks

Dave