Posts

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 "…

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 not…

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
    tex…

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
'/-----------------------------------------…

Concept of Importing SQL data into the EXCEL using VBA

There're numerous modules available on the internet about Importing SQL 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 SQL server with ODBC Drivers. We need 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.RecordsetThe 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 SQL server where your data is kept under tables. Now, to connect the both excel and server, we need to address the things very properly. and it should be. because without providing the proper ad…

The Wonderful Login System for the Excel VBA

This is Kamal Bharakhda & I'm here presenting my tools for Excel VBA Workbooks for its protection. Yes, I have named this Application as "The Wonderful Login System" What it actually does, it will help you to make secure your Excel Workbooks at high-level. Yes, this application will use the online resources to compare the login credentials. Main Operation & Features of using my Login System. 1. Your application will not be accessed off-line. Yes, if your system has active internet, then only you can access the login system in the first place. 2. After checking the activeness with the Internet, you will see the Login form right at the moment. Now, Enter your credentials. 3. After entering the user details, you need to hit the "LOGIN" button once, and it will compare your provided pieces of information with an online database, and if it goes wrong then it gives the wrong messaged popups. 4. Let's say if you want to restrict one of the users the…

Default Text for Textbox of Userform

Image
Userform utility of VBA has always fascinated me to experiments beyond the limits. Well, I haven't touched the max yet. but for me, it's always necessary to dig new possibilities from available resources.

Today I have tried very common experiments with "Textbox Tool" in Userform.

I'm working on some serious Login system for the VBA Applications which will authenticate the user's credentials by matching it from the external database(Google Sheet). If it matched with the source strings, the program will allow the user to access the remaining features of the application. This Login system will be required the active internet connection.

But, that is not the subject of the discussion here.

We have already seen on many websites and on applications, the default text imprinted on the textboxes. Let's say, you are on the login page of any website. In the Username Box, you could see the default text like "USERNAME" or "TYPE YOUR USER ID HERE" e…