Consolidating Multiple Sheets into ONE New Sheet using VBA.

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 above Sheet Selection Event.

The Code further investigate using the mentioned property whether the sheet is protected or not. if it is unprotected instead of Protected then Code will delete the sheet in a fraction of seconds.

Many of us do have an idea of this and many of us would not. So this strategy will help freelancers to make their templates more commercially protected!

Keep Learning.
Kamal Bharakhda

Questions Should be asked by Excel VBA Developer to their Clients

#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!

1. What does your business actually do? (Explain in Short!)
2. Why You want this application?
3. What current setup you have to simplify the process?
4. Why do you want to develop this application on Excel VBA Framework?
5. Have you ever tried to research for the already available solutions? If Yes, then what makes you not to choose them for the process?
6. Do you have your competitors? If Yes, then what they are using for the same process?

Application-oriented questions

1. Do you want the application to run on how many systems? (1/2/Multiple)
2. Do you want to keep your database in Excel or Outside of it?
3. If you want to keep your database outside then where you exactly want to place? (In Online Servers or in your Local System. because if you want your application should run on two or more systems then we have to centralize the database so every system can synchronize equally at a time.)
4. Do you want an application that should have a user-based login system? Like Admin will see everything and Employee will access few things of the system.
5. Will this application have anything effective for the professional year changes?
6. I will require information about each step of the application. because we will design a table of forms to save the information in the database. like, Username, address, etc.. etc..
7. Which of the operating system are you using?
8. Which of the Microsoft Office version are you using?

Choose User Interfaces for your application.

1. Example 1
2. Example 2
3. Example 3

In this section, you have to link here Your earlier made templates or VBA application's user interfaces. Let the client decide what he wants. You can also show examples of others which you can design on your own.

Please make comments with your suggestions and ideas. Let all the members will gain more benefits. :)

Kamal Bharakhda

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 OR logical treatment.

dim I as Long 
For I = 1 to 100 Step 1
if VBA.Instr(1,Cells(I,1).value, "1p") > 0 or VBA.Instr(1,Cells(I,1).value, "1P") then 
msgbox .cells(i,1).Address
end if 
Next I

But believe me, to execute the proper way is a bit easy and different using inbuild VBA functions. Because more stuff on the logical operation the processor would take more time to process it. So better is to use the following idea to process the same thing using  UCASE function.

dim I as Long 
For I = 1 to 100 Step 1
if VBA.Instr(1,UCASE(Cells(I,1).value), UCASE("1p")) > 0  then 
msgbox .cells(i,1).Address
end if 
Next I

Function UCASE will consider each alphabet in the string as UPPER case and that's how you bypass the CASE issue while finding them and matching the string withing string.

Our group member Anil Khanna Karunakaran has asked my this query at personal so I thought this could be a learning point for many friends.
See these kinds of little techniques are a very important part of programmers. Actually, this small difference actually defines the programmers sometimes in some situations.

Moral of the story is everything is capable to provide results. but the good programmers will do it more creative way. So, kindly share your techniques here with us to solve some general issues.

- Kamal Bharakhda

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.Count, 1).End(xlUp).Row
        For I = 1 To J Step 1
            If InStr(1, .Cells(I, 1).Value, Me.TextBox1.Text) > 0 Then
                tmp = tmp & .Cells(I, 1).Value & "|"
            End If
        Next I
    End With
    
    'Filling Listbox
    With Me.ListBox1
        .Top = Me.TextBox1.Height + Me.TextBox1.Top
        If tmp <> vbNullString Then
            tmp = VBA.Left(tmp, Len(tmp) - 1)
            Erase arr
            arr = VBA.Split(tmp, "|")
            ArrCount = UBound(arr)
            For K = 0 To ArrCount Step 1
                .AddItem arr(K)
            Next K
            .Height = 100
        Else
            .AddItem "No Item Found"
            .Height = 18
        End If
    End With
    
    'Code By Kamal Bharakhda
    
End Sub

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 then I wouldn't have been in collaboration with class programming ever. So I didn't paid any attention towards class based programming so far.

But the day come when I have changed my perception regarding class programming. It wasn't simply optional part of programming but it's a necessary part of programming when you are building the solutions at certain level.

Ohh, How? You guys pretty much know about my experiments with userforms. I used to create effective user interfaces with userforms since the day I have been introduced to the userforms of Excel VBA.

As time passed I kept observing modern day application which has very nice looking interfaces and controls like menu bars and etc. But those controls were not available in the common resources of excel VBA libraries. So I have challenged myself to create those tools or controls for userforms in excel to build smart looking solutions. I have build one Menu Bar based Tool for my applications and here is the link of that: 

https://www.youtube.com/watch?v=owpGuoJYEcM

But I found one major problem when I was developing above control where I have to add controls dynamically on the userforms. And I found there My Events of the Control which were added dynamically during run time on the userforms were not responding or not working!

So to get the rid of this issues I researched a bit and Found that the Events and methods to the dynamically added controls were not worked directly from the userform's modules.

We have to define class and with Events variables in the class so those dynamically added controls will keep reacting on the sequential events triggers.

So that's how I learned Class programming is very necessary at dynamic programming.

Here is the article where the author has solve this issue and you can refer that! Comment for more queries and ideas.

https://codereview.stackexchange.com/questions/205263/responding-to-events-of-dynamically-added-controls

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 subprocedure otherwise we have to do the same procedure, again and again, we keep wasting time to fill the arrays and so on. So here is the little code will help you to understand the concept.

Option Explicit
Sub TestingArrays()
Dim Arr() As String
Dim temp As String
Dim I As Byte, J As Byte

temp = "A|B|C|D"
Arr = VBA.Split(temp, "|")

'Following is Process
For I = 1 To 5
    Call TestingLinkedArray(Arr)
Next I
End Sub

Private Sub TestingLinkedArray(ByRef Arr2() As String)
Dim I As Byte
For I = LBound(Arr2) To UBound(Arr2)
    Debug.Print Arr2(I)
Next I
Debug.Print "-----"
End Sub

- Kamal Bharakhda