Monthly Archives: November 2011

How to force proper case in textbox

Proper Text is nothing but each word will have it's first letter in caps. If you want to change every first letter of each word in a sentence then you can use StrConv function of VB in VBA. Syntax: String(Mandatory) String is the String which you want to make in Proper Case. Conversion as StrConv(Mandatory): This is set of Conversion [...]

Textbox first Letter in Caps and Rest other in Lowercase

If you want to automatically make first letter in Uppercase and rest of the characters in lowercase. No matter what case you are typing-in but it will be automatically done by Copying and Pasting the below Code under KeyPress Event of the Textbox Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case Asc("A") To Asc("Z") If InStr(1, TextBox1.Text, Chr(KeyAscii)) [...]

How can I force input to Uppercase or Lowercase in a Textbox

At some point of time you may need user to force to Enter in a particular Case in your Excel Macro Text Box. It could be either in a Lower Case or Upper Case. Unfortunately in VBA Text Box, you don't have any inbuilt settings to do so. However by writing few lines code under Textbox Change Event , you [...]

SQL Connection using Excel Macros

Dear Friends, Using Excel Macros (VBA) you can connect to Databases like SQL, Oracle or Access DB. In this Article, I am going to teach you, how you can do a connection with a SQL Server. SQL databases has mainly two type of authentication : 1. Windows Authentication 2. SQL Server authentication. Here in this article, I will share VBA [...]

How to Create Drop Down List in Excel

Drop down list is made in Excel Cell to limit the Entry in to a Cell from a predefined set of values. It means you are restricted to select one value out of set of Predefined values. On creating a drop down list on a cell or cell-range, one drop-down will be displayed with set of predefined values, on selecting [...]

Show all Formulas in an Excel Sheet

To view all the formula on the worksheet Press Ctrl and ` The ' is the left single quote usually found on the key to left of number 1. To go back to the Normal View, Press the same Key Combination.

By |Categories: Excel Tips|Tags: , , |0 Comments

File Name Formula

There are few instances when we want to store the File Name or Current Workbook Name in a particular Cell. This is very easy. This can be done by using Cell() function as shown below: Syntax: =CELL("filename") Returned Value: C:\Users\Vish\Desktop\[Test.xlsx]Sheet1 Note: The problem with this is that it gives the complete path including Drive and folders with Sheet Name. To [...]

Hide Horizontal or Vertical Scrollbar in Excel Workbook

In one of the Article, i had shown how to hide or display the Horizontal and Vertical Scroll Bar of an Excel Workbook. Now here I am going to show how can we DISPLAY or HIDE, Horizontal or Vertical Scroll Bar in an Excel Workbook. Excel 2003 || Excel 2007 Excel 2003 In Excel 2003, follow below steps to hide [...]

Conditional Formatting based on Condition in other Cell

In previous post we saw the normal conditional Formatting. It means based on the condition of a cell, changing the format of same cell is normal conditional formatting. But here we are going to see the conditional formatting of a cell based on some other cell condition. Suppose if you want to change the formatting of a cell based on [...]

How to Edit Existing Conditional Formatting

For editing Conditional Formatting of an Excel Workbook, follow following steps. It is different for Excel 2003 and 2007. So we will see the steps for both Excel 2003 and 2007. Excel 2007 || Excel 2003 […]