Posts

Showing posts from 2018

Run your VBA Program Daily & Automatically!

Image
My Client has a Query! He wants to receive an email as a notification just before a 15 days before his employee's vacation period starts. here is his explanation in his words : "I am thinking about a script that opens the spreadsheet once a day iterate through all the employees (about 20) then check if today's date is equal to the day that each employee will complete one year minus 15 days, and, if the result is true, send the notification. But I don't know how to implement that with code." Here is the solution how to make this automation happened. 1. Create a workbook 2. Add Worksheet 3. Name it as Employee Manager 4. Create a Table and enter the Employees Details in line by line 5. Create a VB Program which finds the Joining Date of Employee and if it matches the criteria then It will Email you just before the vacation period starts for him/her. 6. Supposed, Employee joined on 1st Jan 2018 then on the 15th December 2018, you will receive an

Future of VBA

It's necessary to think of exactly 10 years ahead of now because it makes your actions more based on future. well, when you are doing good something anything, the best thing you received out of it is a community of Good People. Yes, the best asset. I met one guy on LinkedIn named Vincent ISOZ and he suggested me to provide the same VBA solution on power query. because according to him it is the future. in the next 10 years, every corporation will moving towards cloud office. Then he wrote one comment and I really felt, I should post that comment here in front of you guys, because if VBA is the tree then we are the leaves of it. well here is his comment... "I also worked for Microsoft as an external consultant during 4 years. VBA will be stopped in 2024 (according to the roadmap I saw but you can probably add the 3 usual years of delay) as only Office Online will still be maintained (cloud strategy of Microsoft). We already know that the replacement of VBA is Power Query (w

Import XML Data in Excel Using VBA

Here is the complete VBA code to import XML Data into the Excel Workbook in a completely dynamic approach. Look at the following video. Following is the VBA Code Sub Impor_XML_Data()          'Code By Kamal Bharkahda     Application.ScreenUpdating = False     Dim TargetSheet As Worksheet     Dim ChooseFIle As Variant     Dim TargetSheetName As String     Dim TargetCellAddress As String          TargetSheetName = Application.InputBox("Write a Sheet Name where you want to place the XML Data ***Case Sensitive***", "Target Sheet Name")     TargetCellAddress = Application.InputBox("Write a Cell Address from where XML Data Start Placing", "Target Cell Address")               Set TargetSheet = ThisWorkbook.Sheets(TargetSheetName)     TargetSheet.UsedRange.Clear          ChooseFIle = Application.GetOpenFilename("XML File (*.xml), *.xml", , "Kamal Bharakhda", , False)          If ChooseFIle = vbNullString

Import CSV Data into the Worksheet!

Hello Friends! Merry Christmas to all of you and to your friends and family. Let me share a Christmas present for you all. Do you want to import CSV file into the sheet and calculate the sum of the column data into the last row? Well here is the file! and video of the file is in the comment box. Let me know if you guys have any queries. Thank you. and enjoy your beautiful festival season. Regards, Kamal. Sub ImportCSVFile()     'By Kamal Bharakhda @ 919328093207 [E] kamal.9328093207@gmail.com     Application.ScreenUpdating = False     Dim xFileName As Variant     Dim TargetSheet As Worksheet     Dim Rg As Range     Dim xAddress As String     Dim TargetRange As Range          Set TargetSheet = Sheets("DATABASE")     TargetSheet.UsedRange.Clear     xFileName = Application.GetOpenFilename("CSV File (*.csv), *.csv", , "Kamal Bharakhda", , False)          If xFileName = False Then Exit Sub      On Error Resume Next     Set R

IST (Indian Standard Time) to EST (Eastern Standard Time) Zone in Excel VBA.

Image
Use the following function to get IST Date Time Values of the cell value into EST Date-Time Value. Paste the following VBA code into Module and that's it! '/----------------- Function IST_to_EST(ByR ef SelectRange As Range) As Date          ESTValue = SelectRange.Value - (0.0416667 * 10.5)     IST_to_EST = ESTValue End Function '/----------------- See this video to get more idea on this. Please comment if you have any query.

Delete Row from the Table when you have multiple tables on the same sheet

My client has query was to delete a row from the table. It sounds easy. But when the same sheet has more than two tables starting from the same row (defiantly from the different columns) it's a bit tricky to delete only a row from the Specific table. let's say in the same worksheet You have Table1 and Table2 and you want to delete a row from table 1 dynamically by just selecting a cell inside the table range that's how my client wants to remove a row. I prepared the workbook to show the demo to delete a row of specific table range by selecting the cell inside of it. Here is the Code : Private Sub CommandButton1_Click()          If ActiveCell.Value = vbNullString Then Exit Sub          Const RestrictedRow As Byte = 1     Const FirstColofTable1 As Long = 1     Const LastColofTable1 As Long = 5     Const FirstColofTable2 As Long = 7     Const LastColofTable2 As Long = 11     Const Table1LeftIndex As String = "A"     Const Table1RightIndex As Str

If my input is 1,2 & 3 then result should be 1,2,3,12,13,23,123

Q. Need VBA code to get different combinations. Eg. if my input is 1 & 2 then the result should be 1,2,12. If my input is 1, 2 & 3 then result should be 1,2,3,12,13,23,123 Input can go up to 20 digits also Also, result 12 and 21 should treat as same Similarly, 123 & 321 or 231 or 312 is the same. here is the perfect dynamic solution. You can add more than 20 digits and it will still provide you with the perfect result. Put the following code in the module and that's it. I was working on this since yesterday. :D

Using Spin Button, Hide & Show the column one by one in Excel VBA

Show and Hide Columns one by one by Spin button up to mentioned columns limits. this is going to be the best small tool for excel users. here is the video. and here is the code of Spin Button. Private Const DefaltWidth As Integer = 9 Dim TargetSheetName As String Dim TargetSheet As Worksheet Dim FirstColNo As Integer Dim LastColNo As Integer Dim I As Integer Private Sub SpinButton1_SpinDown()     Application.ScreenUpdating = False     'Assigning Values to the Variables     TargetSheetName = "Sheet1"     Set TargetSheet = Application.ThisWorkbook.Sheets(TargetSheetName)     FirstColNo = TargetSheet.Range("A7").Value     LastColNo = TargetSheet.Range("A9").Value     If TargetSheet.Range("A7").Value = Empty Or TargetSheet.Range("A9").Value = Empty Then         MsgBox "Please Set The  Column Range Limits Before You Proceed", vbCritical, "Kamal Bharakhda"         Exit Sub     End If     

Working With NAMED Ranges in VBA

1. The code to change the value of each cell of the name range at the same time Sub Change_the_Value_of_Each_cell_in_Named_Range()     Dim NameRange As Range     Set NameRange = Range("SALES")     Dim Eachcell As Range     For Each Eachcell In NameRange         Eachcell.Value = "Sales" & "2017"     Next Eachcell     MsgBox "Done" End Sub 2. Let's Say You have a list of Named Range in Sheet3 where you want to change the value of each Named Range with the Same Format Sub Change_The_Value_of_Each_Named_Range()     Dim I As Long     I = 2     Dim SheetName As String     SheetName = "Sheet3"     Dim TargetRange As Range     Do Until Sheets(SheetName).Cells(I, 1).Value = vbNullString         Set TargetRange = Range(Sheets(SheetName).Cells(I, 1).Value)         TargetRange.Value = Sheets(SheetName).Cells(I, 1).Value & VBA.Format(Sheets(SheetName).Range("B6").Value, "YYYY")     I = I + 1  

Timer in Excel Using VBA

Gift Your self a Nice Timer in Excel Using VBA Just Give It A Try! Step 1: Insert an Userform Step 2: Locate the SHOWMODAL Property of The Userform and make it FALSE Step 3: Design the userform you as you are seeing in video or you can design your own. it's up to you. Step 4: Inert a New Module and paste the following code into it. Sub () UserForm2.Show vbModeless End Sub Step 5: Double Click on a button on the userform which will start the clock and paste the following code in it. Private Sub CommandButton1_Click() Do DoEvents UserForm2.Label1.Caption = VBA.Time Loop End Sub Step 6: Paste the following code to close the Timer Private Sub CommandButton2_Click() Unload UserForm2 End Sub Step 7: Put a Button on the Sheet and assign macro named as "Open_Timer" and it's done! enjoy friend. By Kamal Bharakhda

The completely dynamic way to Rename the Shapes in Excel VBA

Image
Sub Rename_Shape() Dim SheetName As String SheetName = "Sheet1" Dim WS As Worksheet Set WS = ThisWorkbook.Sheets(SheetName) WS.Activate Dim Shp As Shape Dim ShapeName As String ShapeName = vbNullString Dim ShapeSelected As String Dim NewNameofShape As String For Each Shp In WS.Shapes ShapeName = ShapeName & " ' " & Shp.Name & " ' " Next Shp ShapeSelected = VBA.InputBox(ShapeName & " Please Input the Shape names in Inverted Comma", "Select Shape") NewNameofShape = VBA.InputBox("Please enter new name of Shape you have selected", "Rename Shape") If ShapeSelected = vbNullString Or NewNameofShape = vbNullString Then Exit Sub WS.Shapes(ShapeSelected).Name = NewNameofShape End Sub Points to Remember 1. The First Inpubox will provide you the list of the Shape available in the Inverted Comma. You need to type the name in the input area which you want to rename

Fill ComboBox based on Seach Criteria

Image
One the best approch for this requirement is to add one textbox as search bar which provides the same result you reuired. Here we need to focus on three things. I'm cosidering that your combobox is in the userform1 and your students data list is in the sheet1. Now, There are two events you need configure to perform this task. 1. UserForm_Initialize 2. TextBox1_Change and the one sub procedure which will populate the result based on the keyword in the textbox 3. Populate_Combobox Now Coding for the 1 & 2 is the same Private Sub TextBox1_Change() Populate_Combobox End Sub Private Sub UserForm_Initialize() Populate_Combobox End Sub Now let's see the code for the Sub Procedure Private Sub Populate_Combobox() Dim I As Long I = 2 Dim LenofStr As Long LenofStr = 0 With UserForm1 .ComboBox1.Clear If .TextBox1.Value = vbNullString Then Do Until Sheet1.Cells(I, 1).Value = Empty .ComboBox1.AddItem Sheet1.Cells(I, 1).Value I = I + 1 Loop Else

BOLD a Part of the Cell Value

Q:  D1=(A1&B1)...I want the result of B1 Bold when result show i.e. Biplab (A1) & Rs.12/-(B2). In Cell No. C1 there is formula =(A1&" "B1)...Now Result I want "Biplab Rs.12/-"..... Only Rs.12/- is BOLD. (Note: Length of B1 is not fixed) Let's create a function called Bold which will make any character under the function to bold. and you can use it directly in the sheet using formula feature.  here is the two thing. Put the Following function in the Module Function BOLD(ByRef Text As String) As String Text = "!" & Text BOLD = Text End Function Now Goto the Sheet and choose we will work on Worksheet_Change Event  here is the Code.  Private Sub Worksheet_Change(ByVal Target As Range) Dim I As Variant I = Target.Value Dim A As String Dim B As String Dim ALEN As Integer Dim BLEN As Integer Dim TargetColumnNo As Integer TargetColumnNo = 3 If Target.Column = TargetColumnN

Replace Excel's Data Validation Drop-down with Smart Userfrom Based Mini Tool

Creating Dropdown from the Validation part of the excel is very easy and very fundamental thing we should know as an Excel Enthusiastic. When our project required the same list of things to be chosen from the single cell usually make things done with creating the drop-down list from the Data Validation Tool which is inbuilt in Excel. Now, Sometimes you might have faced or not, but each cell of the column requires the same list of things for selection. I was usually ending up by drag down the dropdown list to the nth row which could be messy sometimes. and it even no looks cool. and you even can't put things apart from the list. What I have done here is, You don't require a dropdown list at all. Just see the video and you will get what I have done. The advantage of this tool is you don't need to create hundreds of dropdown in the same column when everything is available to you in just a double-click. To compile this tool I have required following two scopes. 1.

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 variables X = Product's Original Amount without Tax Y = 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 = Z Now, Y = ( Q * X ) / 100 So the equation become, X + ( Q * X ) / 100 = Z Now solve the above equation to find the value of X X = ( Z * 100 ) / ( Q + 100 ) And to get the value you required put this value of X back to the initial equation X + Y = Z {( Z * 100 ) / ( Q + 100 )} + Y = Z Now 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

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 d

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(Tex

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 Bo