Microsoft Excel - User form list not in drop downlist

Hi All,

i am creating a small tool in excel. Each and every thing works for me but iam unable to get the user list in the drop down menu. The workbook is attached with. You just click to change password utility to enter in the worksheet, because the drop down in (select user) does not work.

The sample file is attached with...test.zip

I have check the problem is in the userform1 ----) select combo list box ---) Properties----) Rowcount ( in which i enter Users) this name Users doesn't updating any idea?

Thanks and Regards,

farrukh hameed

Hi Farrukh,

Pichart Y,

i have tested the same create new workbook in excel 2003 and it works. You can find the .zip file to check in this workbook i set Users  in Rowsource (if i ommit Users from rowsource) and use Protected!A2:A19. The application login windows buttons do not work. You can check to provide password is password.

I could save as excel 2003 file to excel 2007 to work but i have a other file having lots of code and button it is difficult to move them,can you please rectify what the problem is ?

Password.zip

thanks
farrukh

Pichart Y,

i have tested the same create new workbook in excel 2003 and it works. You can find the .zip file to check in this workbook i set Users  in Rowsource (if i ommit Users from rowsource) and use Protected!A2:A19. The application login windows buttons do not work. You can check to provide password is password.

I could save as excel 2003 file to excel 2007 to work but i have a other file having lots of code and button it is difficult to move them,can you please rectify what the problem is ?

Password.zip

thanks
farrukh

Hi farrukh,

I have try your file, but sorry, I can't get your questions, could you please explain more these 2 points....
1) "The application login windows buttons do not work."
2) "but i have a other file having lots of code and button it is difficult to move them"

Regards,

Pichart Y.

Hi pichart,

sorry i did not explain to you well.

1) When i open test.xlsm i do not find the user list in the select user (combo list box). That's why the enter password button does not work.

2) i can save password.xls to newname.xlsm then it works for me ,but i have to set this userform1 and userform2 in my other master file, when i import the userform1 and 2 from password.xls and open my master file again the user list of names disappears.

I have noticed that when i first open password.xls and provide credentials then open test.xlsm ( i find the user list in test.xlsm). I am wondering that there is no link between password.xls or test.xlsm so why it is happening like that, open the individual file test.xlsm (the userlist disappears and when open both finding the user list in test.xlsm)

Thanks

farrukh

Hi Farrukh,

  After you import the userform1 and userform2, you cannot see the list in your combo box...this is because in your master file, there is no "Defined Name Range" yet. For easily modification to your master file, I suggest you to...

1. copy sheet "protected" to all your Master files, this is to keep the worksheet structure of your authentication control.
2. In the Worksheet event procedure add these lines of code to create Name Range
   •     ActiveWorkbook.Names.Add Name:="Users", RefersToR1C1:= _
           "=OFFSET(Protected!R1C1,1,0,COUNTA(Protected!C1)-1,1)"
         ActiveWorkbook.Names("Users").Comment = ""
   •     ActiveWorkbook.Names.Add Name:="Users_List", RefersToR1C1:= _
           "=OFFSET(Protected!R1C1,1,0,COUNTA(Protected!C1)-1,2)"
         ActiveWorkbook.Names("Users_List").Comment = ""
3. I keep all your both sheets "Protected" and "Blank", in order to keep your code run (your code run by "Fixed Sheet Name").
4. I change you authen table to be easily monitor (user and password) by...
   • In file ImportUserFormTest1.xlsm
     • Farrukh 1= FR1
     • Farrukh 2=FR2
     • Farrukh 3=FR3

Here attachment ---> ImportUserFormTest1.zip

---------------here is the code In the Worksheet event procedure---------------

Private Sub Workbook_Open()
If Sheets("Blank").Visible <> xlSheetVisible Then
Sheets("Blank").Visible = xlSheetVisible
End If
Sheets("Blank").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    ActiveWorkbook.Names.Add Name:="Users", RefersToR1C1:= _
      "=OFFSET(Protected!R1C1,1,0,COUNTA(Protected!C1)-1,1)"
    ActiveWorkbook.Names("Users").Comment = ""
    ActiveWorkbook.Names.Add Name:="Users_List", RefersToR1C1:= _
      "=OFFSET(Protected!R1C1,1,0,COUNTA(Protected!C1)-1,2)"
    ActiveWorkbook.Names("Users_List").Comment = ""

Sheets("Protected").Visible = xlSheetVisible
UserForm1.Show
End Sub

-------------------------------------------------------------------------

Hope this help.

Pichart Y.

Pichart Y,

WOW !

Awesome thank a lot really, it worked for me :)

Thank you for your kind help and support :)

Thanks and Regards,

Farrukh

Hello,

You are welcome, Happy hearing the your problem is solve, congratulations!!

Where are you there, you are working, what time is it there?

Here, Thailand 17:00, I am going to leave my office.

Always see you here when you have question / problem about excel.

Cheers,

Pichart Y.