Posts

Showing posts from 2019

Delete Target Sheet Automatically if the Target Sheet is Unprotected!

Friend asked me a little query. he wants VBA to delete his Target Sheet automatically if the Target Sheet is Unprotected! Now, that's something easy but it's not! Not at all! unless you know the best way to achieve that task using an available property of the Sheet Object. There's a Sheet property called,."ProtectContents" Here's how you can perform.. Step 1: Choose TargetSheet Step 2: Open Code Editor for that Target Sheet Object from VBE. Step 3: Paste Below Code Private Sub Worksheet_SelectionChange(ByVal Target As Range)     On Error Resume Next     Application.DisplayAlerts = False     If Sheets("SheetName").ProtectContents = False Then         Sheets("SheetName").Delete     End If     Application.DisplayAlerts = True     On Error GoTo 0 End Sub That's it! Now if someone will illegally try to crack your Target Sheet password from your workbook then after any cell selection by the user will trigger the abov

Questions Should be asked by Excel VBA Developer to their Clients

Image
#Discussions Questions Should be asked by Excel VBA Developer to their Clients before initiating the project. We all are Learning lots of things as freelancers every day. Especially Non Technical part of the project. And you know it's the most important part of the project where we are getting to know everything before we begin. I'm inviting you here to take part in this post. I hope this post will help each member of the group in their freelancing carrier. General Category's Questions! What does your business actually do? (Explain in Short!) Why You want this application? What current setup you have to simplify the process? Why do you want to develop this application on Excel VBA Framework? Have you ever tried to research for the already available solutions? If Yes, then what makes you not to choose them for the process? Do you have your competitors? If Yes, then what they are using for the same process? Application-oriented questions

Finding a String within a List of Strings

Don't you think it's too easy to perform such a procedure using INSTR function? Yup, it's damn easy but the reason behind writing this post was a little bit core side of the problem. Let's take an example here suppose you have 100 cells containing a string. Now, you want to extract the cell address where the cell's string contains "1p" or anything else. Now, Cell's string could have been anything but you have to find a specific string within a set of cells. It's very easy to find such using following code. dim I as Long  For I = 1 to 100 Step 1 if VBA.Instr(1,Cells(I,1).value, "1p") > 0 then  msgbox .cells(i,1).Address end if  Next I  Above code will simply give you the address of the matching cell with string. But here comes the catch. String 1p is in standard or lower case and you might don't know in the cells which case would be there so, to avoid that confusion programmer generally uses the following line using O

Build Searchable Dropdown Control using ComboBox and ListBox

Steps to achieve this control Step 1: Insert an Userform Step 2: Insert TextBox from Toolbar Step 3: Insert ListBox from Toolbar Step 6: Write Few names in Sheet1 in column 1 Step 7: Insert a button on a worksheet and write the following code to it. Private Sub CommandButton1_Click()     UserForm1.Show End Sub Step 4: Change ListBox Heigh to 0 from Properties window. Step 5: Copy Following Code Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)          MsgBox Me.ListBox1.List(Me.ListBox1.ListIndex)      End Sub Private Sub TextBox1_Change()          Dim I As Long, tmp As String, arr() As String     Dim J As Long, ArrCount As Long, K As Long          With Me.ListBox1         .Clear         .Height = 0     End With          If Me.TextBox1.Value = vbNullString Then Exit Sub          tmp = vbNullString     ArrCount = 0          'Filling the Array with Values which matches the criteria     With Sheet1         J = .Cells(.Rows

Need for a Class-based Programming in Excel VBA!

Need of a Class based Programming and Responding to Events of Dynamically added Controls Title speaks it self that I'm going explain here about Events Procedures of the Controls we are using in UserForms in Excel VBA. I always wondered to myself about, "why should I required Class Based Programming rather I can manage my every tasks through General Modules in Excel VBA? " I consistently in conversation with many experts of excel VBA who has very salient knowledge with class based programming and they always told me about we need class programming to Create the more secured, effective and manageable structure of the object based programming. Yes they are correct that's what the class programming were introduced to the world of programming. in short, I settled with above thoughts that class programming is tool for me to make my programming structure more effective! Which indirectly means If i can manage everything from the general modules and userforms modules t

Passing Arrays to Sub Procedure

Very Very Important Tutorial for VBA Developers. "Passing Arrays to the SubRoutine" You won't be complete without using arrays in programming. Arrays are like blessings to the programmers. Like Juliet. :D So usually in the life of Excel VBA developers, we code sometimes repetitive task within a single procedure. Like creating the same excel sheet or template for their office staff or for their customers using a single data table. So what happens actually, we always Loop through a database for generating each report or template of many. so, it's a time consuming and it's even a bad way of using code VBA resource. So what arrays will do, in a first attempt, arrays will collect the whole table into it and then you can use it for the rest. but then comes if we more repetitive processes then we have to create subroutines or sub-procedures to cut down the length of the main procedure. So, now it's again necessary to pass the array to that subroutine or s

Add New Item in ComboBox in a Cool Way

Step 1: In any column on a sheet let's assume Column "J". in Cell J1 type header of the combo box and in Cell J2 type "Add New Item" Step 2: Select the Cell J2 --> Goto Formulas Tab --> Click on Name Manager --> Click on "New" --> Type any name you required, Let's say you typed "MY LIST" =OFFSET(KDATA!$J$2,0,0,COUNTA(KDATA!$J:$J)-1,1) Step 3: Now, U can design the user form and add the combo box in it. type the following line in the userform_initialize event Userform1.ComboBox1.RowSource = "MY LIST" Step 4: Double click on the combo box and write following VBA codes into it. and that's it. Private Sub D3_Change()     If U.D3.Value = "Create New Type" Then         With shKDATA             .Range("J" & .Cells(.Rows.Count, "J").End(xlUp).Row + 1).Value = _             Application.InputBox("Please Type New Supplier Type", "Add New Supplier Type

Look for the String in the Cell of Multiple Column Table and if not found then Filter the whole row

Suppose, You have 10 columns table in excel and want to filter out such rows which do not have String that you are looking for. If any cell of the row has such string then it should not be filtered out. 

Want your Excel File/Application will RUN smoothly in everywhere?

Hello Developers,  With this post, I'm going to share with you a very handy and useful trick which will help you to make you Excel or Macro Enable Excel files DISTRIBUTABLE or PORTABLE. The main issue usually excels developer faces when their highly invested spreadsheets stop working or generate of bugs in the initialization. There are lots of causes of that problem but the main issues a developer usually face a Catastrophic Run Time Error. Which occurs when you have used a function or formula in your excel sheet which doesn't support in that office version. Then what, developers nightmare starts from the same moment. He needs to go through each formula and code from the top to the bottom to find the unsupported functions. But I have the solution and I'm gonna share it with you. :) There is a website or we should call it a lifesaver website called https://www.techonthenet.com Now, when you gonna use any formula which you have doubts whether

How to create Values as Developer?

If we talking about the development of an application then there are following things concerns the most. 1. Handy, Clean, Sober and Easy to understandable, User Input interface. which is simply known as a user interface or a front-end. 2. The Backend, which has lots of data processing procedures, functions, and formulas, which works on user inputs, should be very well written, optimized, updated and should be very well commented. 3. The Reporting or Outputs or main objective of the application should be How effectively, efficiently and quickly can be delivered without causing legging. 4. The Database structure, where the user's highly important data has been saved to connect, all the missing dots of the application. So, a developer with a good vision, will consider all the above points and design a whole software based on the same consideration. When we help the user to input the required field within their comfort it creates value. 1. When we help the user to process

Fastest Method to Copy Filtered Data in Excel VBA

Image
Yesterday in my post, https://www.facebook.com/groups/ExcelFreelancersGroup/permalink/2469040236459183/ related to 'Copy the "Filtered" Data from Sheet1 to the Sheet2' and Mr. Mehmet, asked the following thing in addition to the provided solution and here was his query "Many of Excel users are using excel as Database and they are facing huge datasets such as 500k rows and 100 columns. Could you please provide a faster way to copy and paste data such as arrays or advanced filter or anything that you would suggest us." ****SOLUTION**** Now, I think Mr. Paul Kelly has your answer with such great detail as never seen or experimented before. I would like to thank him for his effort to get such figures which are mentioned in the image attached to this post. So the basic query of my friend Mehmet is to know all about which method is Fastest of all. So Mr. Paul has conducted his own speed test to know which method is really quick in the average scenar