Search and Populate Matching Text

If you need to search your text and populate a list box based on the matching texts then here is a simple trick.

The list box will refresh after every new character typed in the text box and populate the list box with all matching characters.

The same can be used to populate a combo box or storing the matching texts in an array.

Data :

Fig: Data Structure

Search :

Fig: Search Sheet

Code :


Private Sub txt_item_Change()
    
    If txt_item.Text = "" Then
        lb_item.Clear
        search.Range("F2").Value = 0
        Exit Sub
    End If
    
    Dim rowCount, count, i As Long
    Dim colno As Integer
    
    search.lb_item.Clear
    
    colno = CInt(search.Range("E11").Value)
    rowCount = data.Range(search.Range("D11").Value & Rows.count).End(xlUp).Row
    count = 0
    
    For i = 2 To rowCount
        If InStr(1, Left(CStr(data.Cells(i, colno).Value), Len(txt_item.Text)), txt_item.Text, 1) Then
            search.lb_item.AddItem data.Cells(i, colno).Value
            count = count + 1
        End If
    Next i
    
    search.Range("F2").Value = count
    

End Sub

However to use this type of search for a data source exceeding 1 million records optimization is required. Indexing before search would be best in that case. Will update the same in the next blog.

Example File Link

Simple Login Screen using VBA

Here is a simple example to create a login screen to validate user credentials in excel

Step 1:

Create a simple user form with required text boxes and buttons

Step 2:
Write the following code for the respective buttons:

Private Sub cmd_login_Click()
    
    If txt_user.Text = "" Or txt_pass.Text = "" Then
        MsgBox "User ID or password field cannot be blank", vbCritical, "Invalid Login"
        Exit Sub
    End If
    '=========================
    '       USER ID's
    '=========================
    Dim userID(10) As String
    userID(0) = "sunny"
    userID(1) = "guest"
    
    '=========================
    '       PASSWORDs
    '=========================
    Dim passID(10) As String
    passID(0) = "admin"
    passID(1) = "guest"
    
    Application.DisplayAlerts = False
    Dim i As Integer
    Dim userName, passWord As String
    Dim userStatus, passStatus As String
    Dim loginStatus As String
    userStatus = passStatus = ""
    userName = txt_user.Text
    passWord = txt_pass.Text
    For i = 0 To UBound(userID)
        If userName = userID(i) Then
            userStatus = "Found"
            Exit For
        End If
    Next i
    If userStatus = "Found" Then
        If passWord = passID(i) Then
            passStatus = "Found"
            Call cmd_reset_Click
            frm_login.Hide
            welcomescreen.Visible = xlSheetVisible
            lockScreen.Visible = xlSheetVeryHidden
            Call updateUserDetails(userName)
            ActiveWorkbook.Save
            Application.DisplayAlerts = True
        Else
            MsgBox "Incorrect username or password.", vbCritical, "Login Failed"
            Call cmd_reset_Click
        End If
    Else
        MsgBox "Incorrect username or password.", vbCritical, "Login Failed"
        Call cmd_reset_Click
    End If
End Sub

Private Sub cmd_reset_Click()
    txt_user.Text = ""
    txt_pass.Text = ""
    txt_user.SetFocus
End Sub

Private Sub cmd_cancel_Click()
    Application.DisplayAlerts = False
    frm_login.Hide
    ActiveWorkbook.Close
End Sub

Step 3:
Add these lines of code for hiding whats there in your excel file. So that the user cannot see the stuff behind the login screen before logging into the file. lockScreen is the sheetname of the worksheet we'll use as a default screen.

Private Sub UserForm_Activate()
    lockScreen.Visible = xlSheetVisible
    lockScreen.Select
End Sub

Private Sub UserForm_Terminate()
    ActiveWorkbook.Close
    lockScreen.Visible = xlSheetVeryHidden
End Sub

The user id password for using the example file is:
user id: sunny
password: admin

 

It also keeps track of all the user login instances with time stamp. In a hidden log Sheet.

Link for the example file

Split String into Alphabets and Digits - Type 1

Description :

Here's an example of a User Defined Function for extracting Alphabets and Digits from any given string.

Code :

Option Explicit

Public Function splitString(ByVal inputStr As String, ByVal outputType As Integer)
    Dim i As Long
    Dim numPart As String
    Dim strPart As String
    
    ' if outputType = 0 then display digits
    ' if outputType = 1 then display Alphabets
    
    For i = 1 To Len(inputStr)
        If Asc(Mid(inputStr, i, 1)) > 47 And Asc(Mid(inputStr, i, 1)) < 58 Then
            numPart = numPart & Mid(inputStr, i, 1)
        ElseIf Asc(Mid(inputStr, i, 1)) > 64 And Asc(Mid(inputStr, i, 1)) < 91 Then
            strPart = strPart & Mid(inputStr, i, 1)
        ElseIf Asc(Mid(inputStr, i, 1)) > 96 And Asc(Mid(inputStr, i, 1)) < 123 Then
            strPart = strPart & Mid(inputStr, i, 1)
        End If
    Next i
    
    If outputType = 0 Then
        splitString = numPart
    ElseIf outputType = 1 Then
        splitString = strPart
    End If

End Function

Output :

input	digits	string
sdfds3243	3243	sdfds
sefd323	323	sefd
sfd 32423	32423	sfd
fds  4323	4323	fds
f g j  6 899  9	68999	fgj

input	digits	string
sdfds3243	=splitString(A2,0)	=splitString(A2,1)

Example File Link :

Example File

Extracting Output in Other Format Type 1

If your have data in the given structure and you want it to restructure your data into a given format then you can use this code to restructure and format your new data block.

Input data Structure:

Output Data Structure:

Code :

Option Explicit

Sub split()
    
    Dim i, j, k, splitRow, splitCol, blockCount, columnCount, segmentCol As Long
    Dim dataBlock(500) As String
    Dim dataBlockCol(500) As Integer
    
    Dim summaryRow, countGFS As Integer
    summaryRow = ((Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row - 2) * 2) + 1
    
    splitRow = 2
    splitCol = 1
    segmentCol = 1
    blockCount = 0
    Sheet2.Cells.Clear
    
    'finding data names and location of individual data blocks
    For i = 2 To Sheet1.UsedRange.Columns.Count
        If Sheet1.Cells(1, i).Value <> "" Then
            blockCount = blockCount + 1
            dataBlock(blockCount) = CStr(Sheet1.Cells(1, i).Value)
            dataBlockCol(blockCount) = i
        End If
    Next i
    
    countGFS = 2
    'spliting cell contents
    For j = 1 To blockCount
        Sheet2.Cells(1, splitCol).Value = dataBlock(j)
        
        Sheet2.Cells(summaryRow - 1, splitCol).Value = "-"
        Sheet2.Cells(summaryRow, splitCol).Value = dataBlock(j)
        Sheet2.Cells(summaryRow + 1, splitCol).Value = "ASDF"
        
        columnCount = 0
        For i = 3 To Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row - 1

            Sheet2.Cells(1, splitCol + 1).Value = "Col" & CStr(columnCount)

            Sheet2.Cells(splitRow, segmentCol).Value = Sheet1.Range("A" & CStr(i)).Value
            Sheet2.Cells(splitRow, splitCol + 1).Value = Sheet1.Cells(i, dataBlockCol(j) + 4).Value
            Sheet2.Cells(splitRow + 1, segmentCol).Value = Sheet1.Range("A" & CStr(i)).Value
            Sheet2.Cells(splitRow + 1, splitCol + 1).Value = Sheet1.Cells(i, dataBlockCol(j) + 5).Value
            
            Sheet2.Cells(summaryRow - 1, splitCol + 1).Value = "-"
            Sheet2.Cells(summaryRow, splitCol + 1).Value = Sheet1.Cells(2, countGFS).Value
            Sheet2.Cells(summaryRow + 1, splitCol + 1).Value = Sheet1.Cells(Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row, countGFS).Value
            
            splitRow = splitRow + 2
            splitCol = splitCol + 1
            columnCount = columnCount + 1
            countGFS = countGFS + 1
            
            If i = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row - 1 Then
                Sheet2.Cells(summaryRow, splitCol + 1).Value = Sheet1.Cells(2, countGFS).Value
                Sheet2.Cells(summaryRow + 1, splitCol + 1).Value = Sheet1.Cells(Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row, countGFS).Value
            End If
            
            
        Next i
        
        countGFS = countGFS + 1
        splitRow = 2
        splitCol = Sheet2.UsedRange.Columns.Count + 4
        segmentCol = splitCol

    Next j
    
    'copying formatting
    Sheet2.Select
    For j = 1 To blockCount
        If j = 1 Then
            Range("A1").Select
        Else
            Cells.Find(What:=dataBlock(j), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
            , SearchFormat:=False).Activate
        End If
        
        
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Sheet1.Select
        Cells.Find(What:=dataBlock(j), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Select
        Selection.Copy
        Sheet2.Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        
        Selection.End(xlDown).Select
        Selection.Copy
        Selection.End(xlToRight).Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        ActiveCell.Offset(-1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Range("A1").Select
        Application.CutCopyMode = False
    Next j
    
    Rows(summaryRow - 1).Clear
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    
    Sheet1.Select
    Range("A1").Select
    
    Sheet2.Select
    MsgBox "Split complete"
    
End Sub

Explanation :

In the above mentioned code
Sheet1 is the Input Data Sheet
Sheet2 is the Output Data Sheet

Example File link :

Example File

Update Pivot Table using VBA (Change Data Source and Add New Data into Pivot)

If you have a data set in which a new column is added at a specified time interval then refreshing the pivot every time becomes monotonous.

Excel macro can be used to automate this process.

1. Changing the old data source and adding the new column into the data set.

2. Manipulating the table structure and and adding the new columns into 

the rows or columns or filters or values.

3. Refreshing the table structure with the new data.

Given below is the code to do the same.

1. Adding the new column in values section

Sub()


Sheet1.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Raw data!R3C1:R21C" & CStr(Sheet2.UsedRange.Columns.Count) _
        , Version:=xlPivotTableVersion14)
    
    Sheet1.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields(fieldName), "Sum of " & fieldName, xlSum

    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

    ActiveWorkbook.RefreshAll


The new field can also be added to the xlRowField, xlColumnField and xlPageField

Please find example file here.
https://drive.google.com/file/d/0B20WcznMWRTfMzBnd29JeG9LalE/view?usp=sharing

Dynamic table with vertical and horizontal scrolling options in Excel

In this article we will go through adding a dynamic grid view type table structure with vertical and horizontal scroll bar control

Sometimes we might come across a situation where we have to display an entire dataset by using both horizontal and vertical scroll bar control. Take for example if we have to display student records for a given class consisting of more than 100 students and also you just have a display window of 20 rows and 4 columns at a time.

The simplest options to do this would be create a table like structure by using cell formatting and then populate the inner cells by 20 rows of data. The data population would be triggered by the changing of the scroll bar control.

Since there is no grid view control in MS Excel and we are just dealing with data filled worksheets instead of databases therefore this could be used as an easy and simple trick.

Step 1:

Arrange all your data into a sheet in the same table structure as you would want to display in the table.

We have used a dataset of 151 rows, consisting of demo student details spread across 11 columns in a single datasheet.

Step 2:

In another worksheet, use cell formatting with background colors to prepare a table like structure. Keep exact number of blank rows for data you would like to show.

Step 3:

Go to developer tab and add a vertical and horizontal scroll bar ActiveX Control. Set the min, max and value properties of the scroll bar controls while workbook_open event triggers

tool is the object name of the worksheet that contains the table structure. data is the object name of the worksheet that contains the data structure. usedrange gets the number of cells that contains data. sb_horizontal is the object name of the horizontal scroll bar control. sb_vertical is the object name of the vertical scroll bar control.

For the vertical scroll bar control; set the min value as the row number of the 1st row of your dataset. The max value should be last row number of your dataset minus the number of rows in the table structure.

For the horizontal scroll bar control; set the min value as the column number of the 1st column of your dataset. The max value should be last column number of your dataset minus the number of columns in the table structure.

The max value is this because we would want the table to display till the last row and column when the scroll bars are at their bottom most position.

Every instance of scroll bar change results in displaying 20 rows and 4 columns (number of blank rows and columns in the table structure)

Step 4:

Open the VBA window and in the change event of the activex scroll bar control write the following lines of code.

For the horizontal scroll bar control

1. Range H28 and K28 will contain the column reference of the first and last column position set through the horizontal scroll bar control.
2. Then the heading of the dataset is copied from the data sheet and pasted in the set range H4:K4
3. And the vertical scroll bar’s change event is called to refresh the data structure.

For the vertical scroll bar control

1. CStr(Mid(tool.Range("H28").Value, 2, 1) takes the column reference value from the H28 cell
2. CStr(Mid(tool.Range("K28").Value, 2, 1) takes the column reference value from the K28 cell
3. CStr(sb_vertical.Value + 20) for selecting plus 20 rows from the initial row number
4. Range H5:K25 is the range which is used as the reference to where the data is to be pasted

Step 5:

Save the file as required and then click scroll bar to see the effect.

Example file link:

https://drive.google.com/file/d/0B20WcznMWRTfV3p5bGx1Q3dsRk0/view?usp=sharing

Trick to make a dynamic table in MS Excel

In this article we will go through adding a dynamic grid view type table structure with scroll bar control

Sometimes we might come across a situation where we have to display an entire dataset by using a scroll bar control. Take for example if we have to display student records for a given class consisting of more than 100 students and also you just have a display window of 20 rows at a time.

The simplest options to do this would be create a table like structure by using cell formatting and then populate the inner cells by 20 rows of data. The data population would be triggered by the changing of the scroll bar control.

Since there is no grid view control in MS Excel and we are just dealing with data filled worksheets instead of databases therefore this could be used as an easy and simple trick.

The task can be done by following the given easy to understand steps.

Step 1:

Arrange all your data into a sheet in the same table structure as you would want to display in the table.

I have used a dataset of 151 rows, consisting of demo student name, roll, etc in a single datasheet.

Step 2:

In another datasheet, use cell formatting with background colors to prepare a table like structure.

Keep exact number of blank rows for data you would like to show.

Step 3:

Go to developer tab and add a scroll bar ActiveX Control and set its properties, i.e., min max values.

Set the min value as the row number of the 1^st row of your dataset. The max value should be last row number of your dataset minus the number of blank rows in your table structure.

The max value is this because we would want the table to display till the last row when the scroll bar is at the bottom most position.

Every instance of scroll bar change results in displaying 20 rows (number of blank rows)

Step 4:

Open the VBA window and in the change event of the activex scroll bar control write the following lines of code.

• selectColRange = "A:D"
• Set copyRange = data.Range(selectColRange)
• copyRange.Range("A" + CStr(ScrollBar1.Value) + ":D" + CStr(ScrollBar1.Value + 20)).Copy ThisWorkbook.Sheets("Summary").Range("C7")

The 1^st line is used for declaring the column range which you want to display

The 2^nd line is used for setting the copy range. Here data is the name of the sheet where we have our dataset. If you didn’t set the data sheet name then data object name can be replaced with Sheets(“<Sheet Name>”).

The 3^rd line is used for setting the copy range in the dataset.

• ("A" + CStr(ScrollBar1.Value) gives the 1^st cell coordinate of the copy range.
• D" + CStr(ScrollBar1.Value + 20) gives the last cell coordinate of the copy range.
• ScrollBar1.Value + 20 is used because we want a set of 20 rows to be displayed in the table structure.
• ThisWorkbook.Sheets("Summary").Range("C7"): The table structure is on Summary sheet. And Range C7 is the cell coordinate where the copy range block will be copied to.

Step 5:

Save the file as required and then click scroll bar to see the effect.

Example File link:
https://drive.google.com/file/d/0B20WcznMWRTfcVRWTjJKODRZbFk/view?usp=sharing