Excel Addin...update form & detect authentication!

[pmatz]

Hi,

I have a developed a pretty neat Excel add-in in VBA, which goes down a column of values in a sheet and performs a domain lookup query on each value populating the cells to the right with looked up attributes. Its very helpful.

I have two outstanding issues before i can really roll it out to my site; one nice to have and one rather more important.

The nice to have is my status bar update issue:

I have used a simple label on my main userform which calculates the amount of values to go through and performs an update on the form increasing the width accordingly.

Code: Select all

                'update progress bar
                intPrg = intPrg + 1
                UserForms(0).lblProgress.Width = Int((348 / intPrgLim) * intPrg)
                UserForms(0).Repaint

the strange thing is that this works for the 1st ~20% of the process, then the form 'jumps' a little to the right and down, and the updates stop. I notice that the on screen updates also stop at this same point.

The code still runs however, as when all lookups have finished both the status and the cells are all updated correctly.

I wanted to add the status bar to try and reassure users that the code was still running, as i had noticed the screen only updated for the 1st few seconds, then looked like it had hung - where in fact it was still working.

any ideas?

Secondly is a more important issue to fix:

The user is prompted for their domain logon details with which the code connects to the domain to perform the lookups:

(code snippet)

Code: Select all

' Use ADO to search Active Directory.
' Use alternate credentials.***********************************NEED TO CHECK CREDENTIALS FOR SUCCESSFUL LOGIN>>>>
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Properties("User ID") = strUser
adoConnection.Properties("Password") = strPassword
adoConnection.Properties("Encrypt Password") = True
adoConnection.Properties("ADSI Flag") = ADS_SERVER_BIND _
     Or ADS_SECURE_AUTHENTICATION
adoConnection.Open "Active Directory Provider"

Debug.Print adoConnection.State
Set adoCommand.ActiveConnection = adoConnection

Debug.Print adoCommand.State
' Search OU. Use server binding.
strBase = "<LDAP://" & strServer & "/" & strDNSDomain & ">"

'start loop off now authenticated
Do
               ' cRange.Select

As you can see i have tried to gather whether or not the user has typed in a correct password and hence will authenitcate correctly, using the adoconnection.state and adocommand.state properties.
Unfortunately, both properties report the same whether the correct or incorrect password is used!

This has the result in my add-in, that if the incorrect password is put in, the script just hangs :(((

i cant roll this out to other users until both of these are addressed really, as I would like to propmt the user if the password is incorrect and hence avoid hanging application, and also keep the user reassured that the script is working :)

well, thought I would throw it out there.

Thanks

Paul

[HansV]

I have seen similar problems with long loops. Try inserting a line

DoEvents

in the loop.

I'm afraid I can't help you with your second question.

[pmatz]

DoEvents

THANKS! must remember that one.

[pmatz]

Hi Hans,

Some simple error handling enabled me to catch the error if a bad password was used :)

Also enjoying using the Custom UI Editor to get some more professional looking buttons and tabs in the Ribbon.

result!

[HansV]

Good to hear you solved it.