Access DoCmd.OpenForm Method to open Access Form

Posted on March 7, 2015April 14, 2015 by Wyman W

This tutorial explains how to use Access DoCmd.OpenForm Method to open Access Form in Access VBA Access DoCmd.OpenForm Method - open Access Form DoCmd.OpenForm Method is used to open Access Form. Syntax of Access DoCmd.OpenForm Method- open Access Form DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs) FormName Name of Form (in String) View Optional. How you want to view the Form Name Value Description acDesign 1 The for...

Return Access Form input to Access Report or Form or Query

Posted on March 6, 2015March 22, 2015 by Wyman W

This tutorial explains how to return Access Form input to Access Query, Access Report, Access Form MS Access Search Form In order to explains how to return Access Form input to Access Query, Access Report, Access Form, , I will illustrate with a room booking system example. To goal of the below example is to allow employee search which room he has booked in the system. Step 1 - Create a room booking database In this database, I created four fields, each employee can book multiple ro...

Access VBA DoCmd.RunSQL Method and Error 2342

Posted on March 6, 2015March 6, 2015 by Wyman W

This tutorial explains how to use Access VBA DoCmd.RunSQL Method command and cause of Error 2342 (due to using non-action query such as Select statement) Access VBA DoCmd.RunSQL Method and Error 2342 DoCmd.RunSQL Method runs specified SQL statement. The SQL statement can only be action query (such as create table, modifying data), but not Select statement. Syntax of DoCmd.RunSQL Method DoCmd.RunSQL(SQLStatement, UseTransaction) SQLStatement A string expression that's a valid SQL st...

Excel VBA Custom Function check if worksheet exists

Posted on March 5, 2015December 26, 2016 by Wyman W

This tutorial explains how to use Excel VBA Custom Function to check if worksheet exists, and add new worksheet if worksheet doesn't exist Excel VBA Custom Function check if worksheet exists Sometimes we want to manipulate worksheet in a workbook but we are not sure if the worksheet exists. For example, we want to add a worksheet, if it already exists, skip the adding worksheet code. VBA code - Custom Function to check if worksheet exists There are many Functions on the internet that do the ...

Excel VBA Worksheets.Add Method to add new worksheet

Posted on March 5, 2015July 16, 2015 by Wyman W

This tutorial explains how to add new work sheets using Worksheets.Add Method in Excel VBA, or add new worksheets at the back or before specific worksheet You may also want to read: Excel VBA Worksheets.Copy Method to copy worksheet Excel VBA Worksheets.Add Method to add new worksheet Excel VBA Worksheets.Add Method is to add new worksheet in a workbook. Syntax of Excel VBA Worksheets.Add Method Worksheets.Add([Before],[After],[Count],[Type]) Before Optional. Add new worksheet ...

Excel VBA loop worksheets in the same workbook

Posted on March 4, 2015May 19, 2016 by Wyman W

This tutorial explains how to loop worksheets in the same workbook, and delete worksheet, add worksheet based on worksheet name You may also want to read: Loop workbooks in folders and subfolders Excel VBA select multiple worksheets Excel VBA loop worksheets in the same workbook Loop though worksheets in the same workbook is useful when combined with If...Else statement. For example, you can loop worksheets to see which worksheet name contains a text in order to delete it, or add a wo...

VBA Excel Consolidate survey data

Posted on February 27, 2015May 12, 2016 by Wyman W

This tutorial explains how to use VBA Excel to consolidate survey data. Excel VBA Consolidate worksheets into one worksheet Excel VBA consolidate multiple workbooks into one workbook Excel VBA combine worksheets columns into one worksheet VBA Excel Consolidate survey data It is very difficult to describe what I meant to consolidate survey data, take a look at the below picture. The goal is to consolidate survey data as below. Normally, if you create online survey with Google, the r...

Excel Subtotal Function to calculate Auto Filter value

Posted on February 26, 2015 by Wyman W

Excel Subtotal Function to calculate Auto Filter value Excel Subtotal Function is a collection of aggregate function (Sum, Average, Min, etc), in which you can specify in argument which aggregate function to use. There are two major differences between Subtotal Function and normal aggregate Functions 1. Subtotal Function can calculate base on filtered value in Auto Filter, while normal aggregate Function calculates all values in a Range regardless of the criteria in Filter. 2. Subtot...

Excel VBA Custom Function to Get RGB Color

Posted on February 26, 2015December 26, 2016 by Wyman W

This tutorial explains how to create an Excel VBA custom Function to get RGB Color using Interior.Color Property. Excel VBA Custom Function to Get RGB Color RGB is a VBA Property that can set RGB color. Usually Property comes in a pair, one Method is to Set Property and another is Get Property. However, RGB does not seem to have the Get Method, therefore you cannot get the RGB color directly. To get the RGB Color, we need to make use Interior.Color Property to get Interior Color code, whic...

Excel VBA custom Function Find the first colored Cell value

Posted on February 26, 2015March 21, 2017 by Wyman W

This tutorial explains how to create an Excel VBA custom Function to find the first colored Cell value in a Range using ColorIndex Property. You may also want to read Excel VBA Function sum colored Cell/ count colored Cell Find the first colored Cell value using ColorIndex Property This custom Function was originally created for a question raised in Microsoft Community. It seems that many people have been asking similar questions like finding the first non-empty Cell, finding the first ...