First time Login

[sofiavba]

Hi,

I have a excel spreadsheet that when opened asks a user to login. I want the Cancel button not to be enabled when the user opens the file for the first time but after that the cancel button should be disabled. Is that possible?

 

[rmikesmith]

I want the Cancel button not to be enabled when the user opens the file for the first time but after that the cancel button should be disabled.

Can you clear up the discrepancy? Enabled or Disabled first time? Also, do you mean first time the file is opened ever or in a given Excel session? How is this login administered; i.e., a Userform you've incorporated?


Regards,
Mike

 

[vladk]

In other words, according to your statement, you need that button being disabled all the time. Just set Enabled property of this button to False.

 

[sofiavba]

I want the cancel button to be disabled only the first time ever a users opens a file that he/she downloads from our website.

 

[fumei]

And if they download the same file a second time? It will be considered a "new" first time open?

Gerry

 

[sofiavba]

Gerry thats correct. If a user downloads the file again it t will be a new file and so the cancel button should be disabled and the user must login to the excel spreadsheet again.

 

[rmikesmith]

There are multiple ways to do this. Essentially, you have to have some "flag" that your code can refer to and it will need to be changed after the first time. My approach uses a hidden workbook name whose value is read then changed.

In the module for the login Userform copy the following code (or if the event procedures already exist, just add the contained code to what you have):

Dim IsFirstUse As Boolean

Private Sub UserForm_Initialize()
   
   IsFirstUse = (ThisWorkbook.Names("FirstUse").RefersTo = "=TRUE")
   cmdCancel.Enabled = Not IsFirstUse
   
End Sub


Private Sub UserForm_Terminate()

   If IsFirstUse Then
     ThisWorkbook.Names("FirstUse").RefersTo = "=FALSE"
     ThisWorkbook.Save
   End If
End Sub

From the above, you can see the name is named "FirstUse". To create a hidden name (not visible if you select Insert|Name|Define... from the Excel menu) you must use VBA or, what I recommend, the Name Manager Add-In created by Jan Karel Pieterse, an MS Excel MVP. This will also enable you to change the RefersTo value easily for testing purposes. You can download this free Add-In here:

http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp

BTW, you want to set the RefersTo value to TRUE, then save the workbook before distributing.


Regards,
Mike

 

[sofiavba]

Mike thanks you for the help but I have a question, when I put the code into my spreadsheet, my login form will not display. It basically bypasses the full login process and goes directly to the database without even asking for the users information. Am I doing something incorrectly?

Thanks so much for your help!!

 

[rmikesmith]

Can you post the relevant code from the login process/form display? That may help to see what is happening.


Mike

 

[sofiavba]

Here you go:

Option Explicit
Private strErrorMessage As String
Private Const ERR_LOGIN_ACCESS_DENIED = "Access Denied! Please enter a valid username and password."
Private Const ERR_LOGIN_EMPTY_USERNAME = "Username cannot be empty!" & vbCrLf & "Please enter a valid username and password."

Private Sub cmdCancel_Click()

txtUsername.Text = ""
strErrorMessage = ""
lblErrorMsg.Visible = False
Me.Hide
'Unload Me

End Sub

Private Sub cmdOK_Click()

lblErrorMsg.Visible = False
If (txtUsername.Text = "") Then 'dont check for password; technically it can be empty (although not recommended security-wise)
lblErrorMsg = ERR_LOGIN_EMPTY_USERNAME
lblErrorMsg.Visible = True
Else
strErrorMessage = ""
Me.Hide
End If

End Sub

Private Sub UserForm_Activate()
'Always clear the username/password fields before showng the dialog box
txtUsername.Text = ""
txtPassword.Text = ""
If (strErrorMessage = "LOGIN_DENIED") Then
lblErrorMsg = ERR_LOGIN_ACCESS_DENIED
lblErrorMsg.Visible = True
Else
lblErrorMsg = strErrorMessage
lblErrorMsg.Visible = False
End If
End Sub

Public Sub SetErrorMsg(strErrMsg As String)
strErrorMessage = strErrMsg
End Sub

Dim IsFirstUse As Boolean

 

[rmikesmith]

I assume the posted code is in the login Userform code module? How do you call the login Userform? Can you post that code?

Nothing to do with the problem, but for consistency and as standard practice I would move the Dim IsFirstUse As Boolean line to the top of the module with the other variable and constant declarations.


Regards,
Mike

 

[sofiavba]

Yes the posted code is in the Userform module.

Here is the code when the userform is called

Public Function getMortgage(uwWorkbook As Excel.Workbook, bSilent As Boolean, pwdExcelProtection As String, Optional ByRef bHadErrors As Boolean) As Boolean

Dim myObj As Object
Dim blnResult As Boolean
Dim foundCOOP As Boolean, foundSrHsng As Boolean, foundSrApts As Boolean
Dim foundCommData As Boolean
Dim wsSummary As Excel.Worksheet
Dim foundPreCombData As Boolean
Dim foundFinCombData As Boolean

'Dim wb As Excel.Workbook
'Dim wsIE As Excel.Worksheet

Dim uid As String, pwd As String
Dim strLoanStatus As String
'Declare all parameter arrays for calling the web service
Dim arIE_Types() As String
Dim arFinlStmt_Data() As Variant
Dim arLoanInfo() As String
Dim arIE_Data() As Variant
Dim arRR_Data() As Variant
Dim arLA_Data() As Variant
Dim arDebtSvc_Data() As Variant
Dim arWebService_Data() As String
Dim arRR_Column() As Integer

Dim arPage() As String
Dim arChildrenToGet(0 To 0) As String
Dim strLoanNumber As String
Dim i As Integer, j As Integer
Dim ws As Excel.Worksheet
Dim iStatement As Integer
Dim strTabCode As String
Dim wsRR As Excel.Worksheet
Dim wsLA As Excel.Worksheet
Dim c As Integer
Dim r As Integer
'Dim rTop As Integer
Dim Cell As Excel.Range
Dim rng As Excel.Range
Dim rngBaseYear As Excel.Range
Dim rRR As Integer
Dim rLA As Integer
Dim strErrors As String
Dim intIE_Data_Col_Line_Item_Name As Integer
Dim intIE_Data_Col_Error_Info As Integer
Dim intIE_Data_Col_Amount As Integer
Dim intIE_Data_Col_Description As Integer
Dim intIE_Data_Col_Cashflow_Year As Integer
Dim intLA_Data_Col_Error_Info As Integer
Dim intLA_Data_Col_LA_Info As Integer
Dim intRR_Data_Col_Error_Info As Integer
Dim intRR_Data_Col_Detail_Summary_Ind As Integer
Dim currentLoanChartc As String
Dim currentLoanChartcDesc As String
Dim Version As String
'Commercial Variables
Dim strCommercial As String
'Initialize HadErrors return flag
bHadErrors = True

'First check if the calling workbook is based on a template that is supported by this Addin version
'If the template version of the caller workbook is not supported by the Addin, Notify user with an Error message and exit
On Error Resume Next
Version = uwWorkbook.Names(NAME_UW_WORKBOOK_VERSION_CELL).RefersToRange.value
If Not (Err.Number = 0) Then Version = ""
On Error GoTo ErrHandler
If Not CheckTemplateVersion(Version) Then
MsgBox ERR_MSG_UNSUPPORTED_TEMPLATE_VERSION, vbOKOnly + vbExclamation
getMortgage = True 'Since we already displayed an error message to the user, return true
Exit Function
End If


' Get the loan number from the loan info sheet
strLoanNumber = uwWorkbook.Names(NAME_LOAN_INFO).RefersToRange.Worksheet.Names(NAME_LOAN_NUMBER).RefersToRange.value
If (strLoanNumber = "") Or (strLoanNumber = "000") Then
ShowErrorForm ERR_MSG_INVALID_LOAN_NUM
getMortgage = True 'Since we already displayed an error message to the user, return true
Exit Function
End If
' ------------------------------------------
' Invoke the web service to refresh data
' ------------------------------------------

' Call into the web service to the get the loan/mortgage data
On Error Resume Next

'Set myObj = New FM_IE_ExcelXMLMap.ExcelXMLMap 'EARLY BINDING ONLY FOR TESTING; IN PRODUCTION, ALWAYS USE LATE BINDING
Set myObj = CreateObject("FM_IE_ExcelXMLMap.ExcelXMLMap")
If (Err.Number = 0) Then
arChildrenToGet(0) = "EVERYTHING" 'Constant string indicating that we want to get all children of the mortgage dataset

'ReDim all parameter arrays before calling; there are out parameters in the function signature
ReDim arIE_Types(0 To 1)
ReDim arFinlStmt_Data(0 To 1)
ReDim arLoanInfo(0 To 1, 0 To 1)
ReDim arIE_Data(0 To 1)
ReDim arRR_Data(0 To 1)
ReDim arLA_Data(0 To 1)
ReDim arDebtSvc_Data(0 To 1)
'ReDim arWebService_Data(0 To 1)

'User gets three tries according to the standard norm for number of retries allowed for authentication
Dim bAuthSuccess As Boolean
bAuthSuccess = True

LoadUrl arWebService_Data()
For i = 1 To 3

' ------------------------------------------
' Retrieve user credentials before calling into the web service
' ------------------------------------------
UpdateStatusBar ("Authenticating with MFU web service...")
uid = ""
pwd = ""
If (uidpwd(uid, pwd) = True) Then 'try to retrieve cached credentials of user
If (uid = "") Then 'there are no cached credentials; this is the first time user is hitting the web service
Application.Cursor = xlDefault 'Restore the mouse cursor
Load frmMFULogin
If Not (bAuthSuccess) Then
frmMFULogin.SetErrorMsg ("LOGIN_DENIED")
End If
frmMFULogin.Show vbModal
'Check if user canceled the operation by clicking cancel on the form
If (frmMFULogin.txtUsername = "") Then
Unload frmMFULogin
Call UpdateStatusBar("", True)
getMortgage = True
Exit Function
Else
uid = frmMFULogin.txtUsername
pwd = frmMFULogin.txtPassword
End If
End If
Else
Exit For
End If

'Call web service method with user credentials
Err.Clear

'Make the call
UpdateStatusBar ("Calling web service to refresh data...")
'Show hourglass cursor to user indicating that the authentication process is not complete yet!
Application.Cursor = xlWait

 

[rmikesmith]

I'm struggling to understand your overall setup. Are the Userform (login) and GetMortgage functions in the same workbook? Is more than one workbook involved, here? From where is the GetMortgage function being called and what are you passing to its uwWorkbook parameter? I also see a reference in the comments to Add-In. Is the code you have posted part of an Add-In?


Regards,
Mike

 

[sofiavba]

Yes the getMortgage function is an Add-In and Userform are in the same workbook. When the user opens up the workbook, the data which is stored in our database needs to be displayed. To get the data we use the getMortgage function but the user must login and thats how the Userform comes into the picture.

I was given this workbook to work on only a few days ago and I can say it needs alot of help. But as there is not enough time to rewrite (as well as not having a good understanding of VBA) I need to work with what I have.

 

[rmikesmith]

I still have questions about your setup, but in the meantime here's something to try: Remove the code I posted from the Userform_Initialize event procedure and place it into the Userform_Activate event procedure. Likewise, move the code from Userform_Terminate to Userform_Deactivate.

Let me know if this helps. If not, I'll have additional questions.

Regards,
Mike