How to Select a Named Range on the Active Worksheet

To select the named range "StudentDetails" on the active worksheet, you can use either of the following examples:

Range("StudentDetails").Select

Application.Goto "StudentDetails"

How to Select a Range of Cells on a Worksheet in a Different Workbook

To select the range E5:J10 on a worksheet (Sheet5) in a different workbook (Book5.xlsx), you can use either of the following examples:

Application.Goto Workbooks("BOOK5.xlsx").Sheets("Sheet5").Range("E5:J10")

Application.Goto Workbooks("BOOK5.xlsx").Sheets("Sheet5").Range("E5", "J10")

Or, you can activate the worksheet, and then use method 4 above to select the range:

Workbooks("BOOK5.xlsx").Sheets("Sheet5").Activate
ActiveSheet.Range(Cells(5, 5), Cells(10, 10)).Select

How to Select a Range of Cells on Another Worksheet in the Same Workbook

To select the range E5:J10 on another worksheet (Sheet5) in the same workbook, you can use either of the following examples:

Application.Goto ActiveWorkbook.Sheets("Sheet5").Range("E5:J10")

Application.Goto ActiveWorkbook.Sheets("Sheet5").Range("E5", "J10")

Or, you can activate the worksheet, and then use method 4 above to select the range:

Sheets("Sheet5").Activate
ActiveSheet.Range(Cells(5, 5), Cells(10, 10)).Select

How to Select a Cell on Another Worksheet in the Same Workbook

To select cell F7 on another worksheet (Sheet3) in the same workbook, you can use either of the following examples:

Application.Goto ActiveWorkbook.Sheets("Sheet3").Cells(7, 6)

   -or-
 

Application.Goto (ActiveWorkbook.Sheets("Sheet3").Range("F7"))

You can also activate the worksheet, and then use method 1 above to select the cell:

Sheets("Sheet3").Activate
ActiveSheet.Cells(7, 6).Select

How to Select a Cell on a Worksheet in a Different Workbook

To select cell D4 on a worksheet (Sheet2) in a different workbook (Book2.xlsx), you can use either of the following examples:

Application.Goto Workbooks("Book2.xlsx").Sheets("Sheet2").Cells(4, 4)

    -or-

Application.Goto Workbooks("Book2.xlsx").Sheets("Sheet2").Range("D4")

You can also activate the worksheet, and then use method 1 above to select the cell:

Workbooks("Book2.xlsx").Sheets("Sheet2").Activate
ActiveSheet.Cells(4, 4).Select

How to Select a Range of Cells on the Active Worksheet

To select the range B2:D4 on the active worksheet, you can use any of the following examples:

ActiveSheet.Range(Cells(2, 2), Cells(4, 4)).Select

ActiveSheet.Range("B2:D4").Select

ActiveSheet.Range("B2", "D4").Select

How to Select a Cell on the Active Worksheet

To select cell C4 on the active worksheet, you can use either of the following examples:

ActiveSheet.Cells(4, 3).Select

-or-

ActiveSheet.Range("C4").Select

Selection of a specific sheet from a workbook

Hello Everyone,

The use case is as follows:
Data file which contains separate sheets for separate data. You need to select one sheet from all the available sheets in the file. 

In this example, 
1. User is asked to browse the data file
2. An userform shows a list of all the available sheets in the file
3. You select the required sheet and the data will be copied to your Tool workbook.

This may not be useful alone but you can combine this to your existing automation's where you are required to copy paste a data from a specific sheet in a file.

CODE in the module
===============
Sub fileOpen()
    toolWorkbook = ActiveWorkbook.Name
    On Error Resume Next
    data.Select
    data.Cells.Clear
    data.Range("A1").Select
    Dim strFileToOpen As String
    strFileToOpen = Application.GetOpenFilename(Title:="Please select an Excel file to open", FileFilter:="Excel Files *.xls* (*.xls*),")
    If strFileToOpen = "False" Then
        MsgBox "No file selected.", vbExclamation, "Status"
        Exit Sub
    Else
        Workbooks.Open Filename:=strFileToOpen
    End If
    dataWorkbook = ActiveWorkbook.Name
    frm_sheetSelect.Show
End Sub

Sub copyData()
    Dim tempName As String
    Workbooks(dataWorkbook).Activate
    ActiveSheet.Select
    
    tempName = ActiveSheet.Name
    
    Cells.Select
    Selection.Copy
    Workbooks(toolWorkbook).Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ActiveSheet.Name = tempName
    
    Workbooks(dataWorkbook).Activate
    ActiveWorkbook.Close False
    MsgBox "Data Import Complete", vbInformation, "Status"
End Sub

CODE in the userform

===================

Private Sub UserForm_Activate()

    Dim sh As Worksheet

    Workbooks(Module1.dataWorkbook).Activate

    For Each sh In ActiveWorkbook.Sheets

        frm_sheetSelect.lb_sheetNames.AddItem sh.Name

    Next sh

End Sub

Private Sub cmd_select_Click()

    If lb_sheetNames.Value <> "" Then

        Sheets(lb_sheetNames.Value).Activate

    Else

        MsgBox "Please select the sheet (containing the raw data) from the list to continue.", vbExclamation, "Status"

        Exit Sub

    End If

    Unload Me

    Call Module1.copyData

End Sub

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.

LINK FOR THE EXAMPLE FILE

LINK FOR THE DATA FILE

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

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