Posts

What is the formula for calculating the inclusive tax amount from a given amount?

Let's answer it with mathematical approach. You might required to use it in the formula or in VBA. This simplification will help you for sure. Assume few variablesX = Product's Original Amount without TaxY = Inclusive Tax Amount (i.e. you're looking)Z = Product's Amount with Tax or MRP or Included tax amount.Q = Tax %-So basic foundation of product's final amount after tax including is like,X + Y = ZNow, Y = ( Q * X ) / 100So the equation become,X + ( Q * X ) / 100 = ZNow solve the above equation to find the value of XX = ( Z * 100 ) / ( Q + 100 )And to get the value you required put this value of X back to the initial equationX + Y = Z{( Z * 100 ) / ( Q + 100 )} + Y = ZNow solve for Y,Y = ( Z * Q ) / ( Q + 100 )So put known values of Z & Q in the above equation and you will get the required value.Good day.Kamal Bharakhda

4 digit PIN Style Login System

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

Sheets Navigator Tool in Excel VBA

Image
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 tha…

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