4 digit PIN Style Login System

Hello Beautiful People,

Here I'm giving away the most unique way to login in excel based applications as 4 digit PIN style.

It's Fast and secured. And even with this post, you will receive the VBA Code to change the PIN No. by the authorized user.

So, What happened yesterday, my traditional login system, which has a username and password, as usual, has become more boring. I have just completed a development of an application for billing purpose and I was testing it and I felt, I should have something more fast and furious and secured way of login system. I was thinking of for the idea and suddenly my mom came to my room and asked me for my VISA debit card and she also asked me the PIN no. of the same card. But that leaves the fantastic idea on me.

I have quickly developed something very simple and sober which works exactly like what I have assumed. The user just needs to type 4 digit pin and without any control and by using of Textbox Change Event Mechanism, I get the best result out it. and it worked like a HERO!.

Now, I'm giving away the VBA code for the Userform and Module for Changing PIN No. on any time by the Authorized User.

Here are the things you need to prepare before placing of VBA Codes.

1. User form with only One TextBox and Designed it just like what you can see in the middle of the screenshot attached to this Post.

2. Place a Command Button anywhere you want and caption it as "Change PIN No."

3. Now Open the Userform and Double Click on the Textbox and put this code in it.

Private Sub TextBox1_Change()

Application.ScreenUpdating = False

Dim RealPass As Long

RealPass = THENEWGST.Sheets("REGISTRY").Range("Z1").Value

With LOGIN

If VBA.Len(.TextBox1.Value) = 4 Then

If VBA.Val(.TextBox1.Text) = VBA.Val(RealPass) Then

'Call here the next Procedure you want to execute after entering of successful PIN No. 

Else

MsgBox "Wrong PIN" & vbNewLine & vbNewLine & "Call us at 919328093207 for Any Query", vbExclamation, "Zyest Solutions"

.TextBox1.Value = vbNullString

End If

End If

End With

Application.ScreenUpdating = True

End Sub

4. Then Goto the button where you captioned it as "Change PIN No." and Put the following VBA Code in the Sub.

Private Sub PIN_CHANGING_PROCESS()

Application.ScreenUpdating = False

On Error Resume Next

Dim I As Variant

Dim J As Variant

Dim K As Variant

Dim L As Variant

L = THENEWGST.Sheets("REGISTRY").Range("Z1").Value

I = VBA.InputBox("Please Enter Your Current PIN", "PIN Changing Process")

If VBA.Val(I) = VBA.Val(L) Then

'/-------

J = VBA.InputBox("Please Enter Your NEW PIN", "PIN Changing Process")

If VBA.IsNumeric(J) = False Then

MsgBox "Only Numeric Inputs Are Allowed", vbExclamation, "Validation by Zyest Solutions"

Exit Sub

End If

If VBA.Len(J) <> 4 Then

MsgBox "Only Four [4] Digits are allowed as PIN", vbCritical, "Validation By Zyest Solutions"

Exit Sub

End If

'/-------

K = VBA.InputBox("Please Enter Your NEW PIN Again", "PIN Changing Process")

If VBA.IsNumeric(K) = False Then

MsgBox "Only Numeric Inputs Are Allowed", vbExclamation, "Validation by Zyest Solutions"

Exit Sub

End If

If VBA.Len(K) <> 4 Then

MsgBox "Only Four [4] Digits are allowed as PIN", vbCritical, "Validation By Zyest Solutions"

Exit Sub

End If

'/-------

If VBA.Val(J) = VBA.Val(K) Then

THENEWGST.Sheets("REGISTRY").Range("Z1").Value = VBA.Val(J)

MsgBox "Great! Your PIN No. is Changed!" & vbNewLine & vbNewLine & "Your New PIN No. is : " & J, vbOKOnly, "Zyest Solutions"

Else

MsgBox "PIN No. Doesn't matched, Please do it again", vbCritical, "Validation By Zyest"

End If

'/-------

Else

MsgBox "Wrong PIN No. Please Enter Correct PIN No.", vbExclamation, "Validation By Zyest Solutions"

End If

Exitit:

Application.ScreenUpdating = True

End Sub

Note: "THENEWGST" is my workbook name. So you can replace it with ThisWorkBook

So You can modify this code according to your requirements.

Enjoy it and try it. and leave your comments here. thanks.

Regards

Kamal Bharakhda

kamal.9328093207@gmail.com

+919328093207

Sheets Navigator Tool in Excel VBA

You all have that one workbook or spreadsheet, which have definitely more than 50 sheets. Which is very boring to scroll sometimes when you want to work with any random sheets more often.

There are many ways we can perform navigation through sheets. We can create the shapes to navigate there but hardly, we can only do that for a few sheets. Not for every sheet. So, here I'm coming with a permanent and handsome solution.

My Sheet Navigator

How it looks?

How it Works?

it's just initiated with pressing "Ctrl+k"

(I have selected "k" as a shortcut key.)

Select the Sheet you want to navigate to, and double-click it. Navigator takes you to that sheet and userform will be closed automatically.

Isn't easy?

Let's develop this Awesome Tool. 

Step 1: Goto the Developer Tab and click on the Record Macro. You will be asked to name the new macro which you gonna record. Now, don't require to name this macro but if you want then do it. Now, most important is that to assign a macro with the Shortcut key. As I have said, in my case I have chosen the letter "k" and you choose what you want. but remember, if you choose the active shortcut key, after this process, that earlier action will be overlapped by the current macro activities

So save the macro and goto the Developer Tab again and Stop the Recording. Now in VB Editor, you will see this module. 

Step 2: Design the Userform. 

You just need one list box! Insert the Userform and pick the list box from the toolbox and set it just like what you can see in the image. 

Step 3: Coding! 

This tool is running totally on two Events. 

1. UserForm_Initialize event 

2. Listbox ListBox_DblClick Event

Here is the Code for UserForm_Initialize event 

Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

    

    Dim idp As Variant

    idp = ThisWorkBook.Name

    

    Dim Machine As Workbook

    Set Machine = Excel.Workbooks(idp)

    

    Dim sh As Worksheet

    

    For Each AllSH In Machine.Sheets

        Userfrom1.ListBox1.AddItem (sh.Name)

    Next

    

Application.ScreenUpdating = True

End Sub

Here is the Code for Listbox ListBox_DblClick Event 

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Application.ScreenUpdating = False

    

    Dim idp As Variant

    idp = THEIDP.Name

    

    Dim Machine As Workbook

    Set Machine = Excel.Workbooks(idp)

    

    With UserForm1.ListBox1

        Machine.Worksheets(.List(.ListIndex)).Activate

    End With

    

    Unload UserForm1

Application.ScreenUpdating = True

End Sub

There you go...save it and run it! EasyPeasy!!!!

Thank for trying this. 

Regards,

Kamal Bharakhda 

Userforms, TexBoxes & Condition Matching in Excel VBA

Suppose,

TextBox1.Value = 10
and
TextBox2.Value = 10

also, if you pass this following coding it gives the wrong result.

If TextBox1.value = TextBox2.value then 
    MsgBox "Yes They are Equal"
Else
    MsgBox "No, They are not Equal"
End if

You will defiantly get the second message and that is, "No, They are not Equal!"

So why does it happens? In Excel VBA, when you are dealing with Userforms, sometimes work demands an use of conditional matching criteria. Just like, IF the value of textbox1 is equal to textbox2 then do something. just like that.

So, Textboxes have always stored values entered in it as String Format. yes even if it's number or not. So, when do we matching the two textboxes based on numbers then we will surely not get the correct result.

So there are two ways to get rid of this.

1. Use Val function
2. Store the Value of TextBoxes into the Worksheet Range.

1. Use of Val Function

If Val(TextBox1.value) = Val(TextBox2.Value) then 
    MsgBox "Yes They are Equal"
Else
    MsgBox "No, They are not Equal"
End if

This time you will receive the First Message.

2. Let's look at this.

'Store the Value of Textboxes into the cell. 
Sheets("Sheet1").Range("A1").Value = TextBox1.value
Sheets("Sheet1").Range("A2").Value = TextBox2.value

If Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A2").Value then 
    MsgBox "Yes They are Equal"
Else
    MsgBox "No, They are not Equal"
End if

The result will be the same, the first one. It's because of Excel automatically recognize the DataType of the Data entered in the cell. So It will become more accurate and also solves your purpose.

These are the small things. many are knowing and many don't. Who doesn't it's for them. 

Thank you!

Kamal Bharakhda

Making VBA Development Version Independent Part 1

Making VBA Development Version Independent, will require focussed research and I think it's still "long" to go... ( Here "long" is not data type :P ) 

I tried my best to hell throughout VBA versioning issues but sometimes VBA itself has limitations which keep us behind the facility of distributing the spreadsheets. 

We need to solve every issue on a particular basis. 

So Let's try that first particular. :D

>> Finding the Last Empty Row of Worksheet Data <<

This is most common task amongst every VBA Tasks. There are multiple ways to get the last empty row but the easiest way is following mentioned. (I use it every time!)

'/-------------------

'Finding Last Empty Row of Sheet1

Sub Test()

    Dim LROW as Long 

    With Thisworkbook.Sheets("Sheet1")

    LROW = .cells(.Rows.count, 1).End(XlUp).Row + 1

    End With 

    Msgbox LROW

End Sub

'/-------------------

Above subprocedure will throw Last Empty Row Number through Message Box. 

Have you noticed, I have used a variable to store the procedure reflection. And that where the whole procedure goes wrong to become a version independent. 

Let me explain, 

I have assigned that LROW variable with datatype LONG. Which is itself has different values when you have MS OFFICE of 32bit or 64bit. Ans technically in both systems has a different method to assign long datatype. How? (Research It!)

So, to solve at least this issue I want to change the datatype of a variable from LONG to Integer! Yes, Integer datatype has same values across every version and its exact value is between -32,768 to 32,767 and we know, every worksheet after MS OFFICE 2007 will have more than 10 lacs of rows. which will not hold the row number data and will throw an error at the same time on execution like Type Mismatch Error no. 13

So, if you guys are sure about that, your database will not exceed the 32000 lines then I have perfect version independent code for you. :) apply it. 

'/-------------------

'Finding Last Empty Row of Sheet1

Sub TestVersionIndependent1()

Dim LROW as Integer

    With Thisworkbook.Sheets("Sheet1")

        LROW = .cells(32000, 1).End(XlUp).Row + 1

    End With 

Msgbox LROW

End Sub

'/-------------------

Above code will work for sure. Enjoy...!!

I will keep post more n more on this subject. 

- Kamal Bharakhda

Text Masking in Excel VBA

Hello Friends,

Two days ago, I have posted one query here on Password Masking of Userform Textbox. David Miller tried to help me out but it's not happened actually.

Problem:

.PasswordChar property has been used to mask text to look like Password Characters just like "*********"

So, it's working fine in Windows Excel but not in MAC Excel.

Solution:

So, I developed a code which works with Textbox1_Change events. There is some limitation too with my solution. In general, you can edit the typed password during writing but in my case user won't be able to do so. if User wants to edit the password, they have to rewrite the whole code again.

My code will use the Cell A1 to store the actual password and will use it for credential matching.

here is the solution:

'/+++++++++++++++++++++++++++

Private Sub TextBox1_Change()

    Dim mystring As Variant
    Dim textlen As Integer
    Dim counter As Integer
    Dim passlen As Integer

    mystring = UserForm1.TextBox1.Value
    textlen = VBA.Len(mystring)
    passlen = VBA.Len(Sheets("Sheet1").Range("A1").Value)

    If VBA.Right(mystring, 1) = "*" Then
        If passlen <> textlen Then
            MsgBox "You're not allowed to do so"
            UserForm1.TextBox1.Value = ""
            Sheets("Sheet1").Range("A1").Value = ""
        Exit Sub
        End If
    Exit Sub
    End If

    For counter = 1 To textlen
        If textlen > 0 Then
            If VBA.Mid(mystring, counter, 1) = "*" Then
            Else
            Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value & VBA.Mid(mystring, counter, 1)
            End If
         End If
    Next

    If textlen > 0 Then
         UserForm1.TextBox1.Value = VBA.Replace(mystring, VBA.Mid(mystring, textlen, 1), "*")
    End If

End Sub

'/++++++++++++++++++++++++++++

this way text masking will be more universal and portable.

Thank you.

The scope of the Variables in VBA - Part 1

The scope of the variables means when we declare variable to hold some information in it, we need to provide those variables with some datatypes. DataTypes could be anything. Integer, long, string, variant etc...

Sometimes we required a single variable to use it in multiple procedures and even throughout the whole project of the workbook sometimes.


To understand the scope of the variables, we need to understand the basic structure of the VBA. Whatever the procedure you create it depends on your declaration, how you want to use those subprocedures and variables.

There are three layers of the project.

1. Procedure
2. Module
3. Project

the bottom layer is Procedure

Like whatever you will define will stay up to that procedure only.

E.g.

insert the module and type the following sub-procedure.

'/------------------------------------------

Private Sub Testing1()
'This is the Procedure.
       Dim A as long
       A = 1
       msgbox A
End Sub
'/------------------------------------------

Private Sub Testing2()
       msgbox A
End Sub

'/------------------------------------------

The output of Testing1 will be "1"
and The output of Testing2 will be "0" or blank

Because we have declared variable A in sub procedure. and that variable A has the scope of up to that Testing1 Procedure only.

So it's seems a private variable declaration.

you can notice... procedural declaration has effect until its own procedure only. that's the concept. we can do more and more.

But Scope is not only limited up to variables. It also applies to the declaration of procedure too. but we will look forward to it in next part.

thank you.

- Kamal Bharakhda

Concept of Connecting MySQL data into the EXCEL using VBA

There're numerous modules available on the internet about Importing MySQL Data into Excel using VBA. But What they don't explain is about the concept of the Connection string. you need to understand few important attributes of connecting MySQL server with ODBC Drivers.

We need the following few things to attempt this procedure.

1. Go to the Tools > References > and choose Microsoft ActiveX Data Objects x.x Library (6.1 in my case) 
2. Then we need to create two objects with ADODB (ActiveX Data Objects), one for connection and one for data storage (which brings data with it).

We refer,
Connection as ADODB.Connection, and 
Recordset as ADODB.Recordset

The first part is over. Now, comes the toughest part of the program where we need to create the connection between our excel workbook and the MySQL server where your data is kept under tables.

Now, to connect both excel and server, we need to address the things very properly. and it should be. because without providing the proper addressing, we will surely get the frustrating errors. anyway, let's get back to the point. so, addressing the server correctly, we need to provide the various pieces of information to the objects. and those pieces of information are attributes which we will use to get the perfect data as queried.

Here are those important attributes / element. 
- Driver / Provider
- Server Name / Host
- DataSource / Database 
- Initial Catalog / Table Name 
- Username 
- User Password
- Port No.

You need someone who supports you while reaching towards your destination. that we called a DRIVER. the first element. there are many attributes we required to mention but the DRIVER is the most sensitive part of the program. Because it requires the damn clear conception of ODBC Driver. (There are many kinds of drivers, but mostly ODBC driver has been used to fetch the MySQL Server Data)

ODBC is the clear and simple driver/software, which will use your attributes/elements and will reach the perfect destination using your SQL Queries. Let's looks at the definition of ODBC by Microsoft. "Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data."

Now, ODBC Drive Which I have used is. "MySQL ODBC 5.3 ANSI Driver". There are many version available of the same software but I prefer to use this because it's the most successful one till.

You can find the ODBC driver in the System Drive C: > Windows Folder.

now the most important thing to remember is that, where this file is actually placed?

if your Windows operating is 32bit and office application also 32bit then VBA will look the ODBC driver in the system32 folder under windows. there's no issue at all.

but what if you have 64bit windows and running the 32bit office? you will face Driver Lost errors. It's just because, mostly all active drivers in the 64bit windows are kept in SYSWOW64 named folder under the same windows folder. So you need to copy that driver from system32 to the syswow64 folder.

but first, find the driver which named as odbcad32.exe (you can even find that drive in Administrative Tools)

Double click and find the DRIVER tab. you will get the above-mentioned ODBC driver name there. what if you don't have that driver specified there? just don't worry. Google the "MySQL ODBC 5.3 ANSI Driver" to download that driver's MSI form. and installed it. it has nearly an 8 MB of size. and I recommend you to choose 32bit if your office is 32bit. vice versa.

after installing, check the driver tab again, you will get that name there.... and now try your code again. you will get the result. and that's how I get.

it's the only thing which hasn't been discussed anywhere. so I thought I should share with you the proper concept.

thank you.



- Kamal C Bharakhda