Playing with UserForms

Playing with UserForms

Input Box

An input box is a simple dialog box that allows the user to make a single entry. For example, you can use an input box to let the user enter text or a number or even select a range. You can generate an InputBox in two ways: by using a VBA function and by using a method of the Application object.

The VBA InputBox function

The syntax for VBA’s InputBox function is

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile, context])

·         prompt: Required. The text displayed in the InputBox.

·         title: Optional. The caption of the InputBox window.

·         default: Optional. The default value to be displayed in the dialog box.

·         xpos, ypos: Optional. The screen coordinates of the upper-left corner of the window.

·         helpfile, context: Optional. The help file and help topic.

The InputBox function prompts the user for a single piece of information. The function always returns a string, so your code may need to convert the results to a value.

The prompt may consist of up to 1,024 characters. In addition, you can provide a title for the dialog box and a default value and specify its position on the screen. You can also specify a custom Help topic; if you do, the input box includes a Help button. The following example, which generates the dialog box shown in Figure , uses the VBA InputBox function to ask the user for his full name. The code then extracts the first name and displays a greeting in a message box.

Sub GetName()

Dim UserName As String

Dim FirstSpace As Integer

Do Until UserName <> ""

UserName = InputBox("Enter your full name: ", _

"Who Are You?")

Loop

FirstSpace = InStr(UserName, " ")

If FirstSpace <> 0 Then

UserName = Left(UserName, FirstSpace - 1)

End If

MsgBox "Hello " & UserName

End Sub

Notice that this InputBox function is written in a Do Until loop to ensure that something is entered when the input box appears. If the user clicks Cancel or doesn’t enter any text, UserName contains an empty string, and the input box reappears. The procedure then attempts to extract the first name by searching for the first space character (by using the InStr function) and then using the Left function to extract all characters before the first space. If a space character isn’t found, the entire name is used as entered.

The Excel InputBox method

Using Excel’s InputBox method offers three advantages over VBA’s InputBox function:

·         You can specify the data type returned.

·         The user can specify a worksheet range by dragging in the worksheet.

·         Input validation is performed automatically.

The syntax for the Excel InputBox method is

InputBox(Prompt [,Title][,Default][,Left][,Top][,HelpFile, HelpContextID] [,Type])

Prompt: Required. The text displayed in the input box.

·         Title: Optional. The caption in the input box window.

·         Default: Optional. The default value to be returned by the function if the user enters nothing.

·         Left, Top: Optional. The screen coordinates of the upper-left corner of the window.

·         HelpFile, HelpContextID: Optional. The Help file and Help topic.

·         Type: Optional. A code for the data type returned, as listed in Table Below.

Table: Codes to Determine the Data Type Returned by Excel’s Inputbox Method

Code	Meaning
0	A formula
1	A number
2	A string (text)
4	A logical value (True or False)
8	A cell reference, as a range object
16	An error value, such as #N/A
64	An array of values

The EraseRange procedure, which follows, uses the ExcelInputBox method to allow the user to select a range to erase. The user can either type the range address manually or use the mouse to select the range in the sheet.

The InputBox method with a type argument of 8 returns a Range object (note the Set keyword). This range is then erased (by using the Clear method). The default value displayed in the input box is the current selection’s address. The On Error statement ends the procedure if the input box is canceled.

Sub EraseRange()

Dim UserRange As Range

On Error GoTo Canceled

Set UserRange = Application.InputBox _

(Prompt:=”Range to erase:”, _

Title:=”Range Erase”, _

Default:=Selection.Address, _

Type:=8)

UserRange.Clear

UserRange.Select

Canceled:

End Sub

The VBA MsgBox Function

VBA’s MsgBox function is an easy way to display a message to the user or to get a simple response (such as OK or Cancel).

The syntax for MsgBox is as follows:

MsgBox(prompt[,buttons][,title][,helpfile, context])

·         prompt: Required. The text displayed in the message box.

·         buttons: Optional. A numeric expression that determines which buttons and icon are displayed in the message box. See Table

·         title: Optional. The caption in the message box window.

·         helpfile, context: Optional. The help file and Help topic.

You can easily customize your message boxes because of the flexibility of the buttons argument. (Table listed below the many constants that you can use for this argument.) You can specify which buttons to display, whether an icon appears, and which button is the default.

Constants Used for Buttons in the Msgbox Function in next post

Some Useful Macro Procedure-2

Some Useful Macro Procedure-2

Getting a list of fonts

If you need to get a list of all installed fonts, you’ll find that Excel doesn’t provide a direct way to retrieve that information. The technique described here takes advantage of the fact that (for compatibility purposes) Excel 2007 still supports the old CommandBar properties and methods. These properties and methods were used in pre-Excel 2007 versions to work with toolbars and menus. The FontsList macro displays a list of the installed fonts in column A of the active worksheet. It creates a temporary toolbar (a CommandBar object), adds the Font control, and reads the font names from that control. The temporary toolbar is then deleted.

Sub FontsLists()

Dim FontList As CommandBarControl

Dim TempBar As CommandBar

Dim i As Long

' Create temporary CommandBar

Set TempBar = Application.CommandBars.Add

Set FontList = TempBar.Controls.Add(ID:=1728)

' Put the fonts into column A

Range("A:A").ClearContents

For i = 0 To FontList.ListCount - 1

Cells(i + 1, 1) = FontList.List(i + 1)

Cells(i + 1, 1).Font.Name = FontList.List(i + 1)

Next i

' Delete temporary CommandBar

TempBar.Delete

End Sub

Retrieving a value from a closed workbook

This procedure reads 1,200 values (100 rows and 12 columns) from a closed file and then places the values into the active worksheet.

Private Function GetValue(path, file, sheet, ref)

‘ Retrieves a value from a closed workbook

Dim arg As String

‘ Make sure the file exists

If Right(path, 1) <> “\” Then path = path & “\”

If Dir(path & file) = “” Then

GetValue = “File Not Found”

Exit Function

End If

‘ Create the argument

arg = “’” & path & “[“ & file & “]” & sheet & “’!” & _

Range(ref).Range(“A1”).Address(, , xlR1C1)

‘ Execute an XLM macro

GetValue = ExecuteExcel4Macro(arg)

End Function

Sub GetFileValue()

Dim p As String, f As String

Dim s As String, a As String

Dim r As Long, c As Long

p = “c:\Files\”

f = “Activity.xlsx”

s = “Sheet1”

Application.ScreenUpdating = False

For r = 1 To 100

For c = 1 To 12

a = Cells(r, c).Address

Cells(r, c) = GetValue(p, f, s, a)

Next c

Next r

End Sub

In Next Section We will Talk About Builtin User Form in Excel

Some Useful Macro Procedure-1

Some Useful Macro Procedure

Copying a variably sized range

In many cases, you need to copy a range of cells, but you don’t know the exact row and column dimensions of the range.

The following macro demonstrates how to copy this range from Sheet1 to Sheet2 (beginning at cell A1). It uses the CurrentRegion property, which returns a Range object that corresponds to the block of cells around a particular cell (in this case, A1).

Sub CopyCurrentRegion2()

Range(“A1”).CurrentRegion.Copy Sheets(“Sheet2”).Range(“A1”)

End Sub

Prompting for a cell value

The following procedure demonstrates how to ask the user for a value and then insert it into cell A1 of the active worksheet:

Sub GetValue()

Range(“A1”).Value = InputBox(“Enter the value”)

End Sub

This procedure has a problem, however. If the user clicks the Cancel button in the input box, the procedure deletes any data already in the cell. The following modification takes no action if the Cancel button is clicked:

Modified version of above Example

Sub GetValue ()

Dim UserEntry As Variant

UserEntry = InputBox(“Enter the value”)

If UserEntry <> “” Then Range(“A1”).Value = UserEntry

End Sub

Entering a value in the next empty cell (DATA ENTRY)

A common requirement is to enter a value or data into the next empty cell in a column or row. The following example prompts the user for a name and a value and then enters the data into the next empty row.

Sub DataEntry()

Dim NextRow As Long

Dim Entry1 As String, Entry2 As String, Entry3 As String, Entry4 As String, Entry5 As String

Do

‘Determine next empty row

NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

'Creating Header

Cells(1, 1).Value = "Name"

Cells(1, 2).Value = "Gender"

Cells(1, 3).Value = "Address"

Cells(1, 4).Value = "Phone Number"

Cells(1, 5).Value = "EMail ID"

‘ Prompt for the data

Entry1 = InputBox(“Enter the Name”)

If Entry1 = “” Then Exit Sub

Entry2 = InputBox(“Gender”)

If Entry2 = “” Then Exit Sub

Entry3 = InputBox(“Address”)

If Entry3 = “” Then Exit Sub

Entry4 = InputBox(“Phone Number”)

If Entry4 = “” Then Exit Sub

Entry5 = InputBox(“Email ID”)

If Entry5 = “” Then Exit Sub

‘ Write the data

Cells(NextRow, 1) = Entry1

Cells(NextRow, 2) = Entry2

Cells(NextRow, 3) = Entry3

Cells(NextRow, 4) = Entry4

Cells(NextRow, 5) = Entry5

Loop

End Sub

Get a user-selected range

In some situations, you may need an interactive macro. For example, you can create a macro that pauses while the user specifies a range of cells. The procedure in this section describes how to do this with Excel’s InputBox method.

Sub GetUserRange()

Dim UserRange As Range

Prompt = “Select a range for the random numbers.”

Title = “Select a range”

‘ Display the Input Box

On Error Resume Next

Set UserRange = Application.InputBox( _

Prompt:=Prompt, _

Title:=Title, _

Default:=ActiveCell.Address, _

Type:=8) ‘Range selection

On Error GoTo 0

‘ Was the Input Box canceled?

If UserRange Is Nothing Then

MsgBox “Canceled.”

Else

UserRange.Formula = “=RAND()”

End If

End Sub

Looping through a selected range efficiently

A common task is to create a macro that evaluates each cell in a range and performs an operation if the cell meets a certain criterion. The procedure that follows is an example of such a macro. The ColorNegative procedure sets the cell’s background color to red for cells that contain a negative value. For non-negative value cells, it sets the background color to none.

Sub ColorNegative3()

‘ Makes negative cells red

Dim FormulaCells As Range, ConstantCells As Range

Dim cell As Range

If TypeName(Selection) <> “Range” Then Exit Sub

Application.ScreenUpdating = False

‘ Create subsets of original selection

On Error Resume Next

Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers)

Set ConstantCells = Selection.SpecialCells(xlConstants, xlNumbers)

On Error GoTo 0

‘ Process the formula cells

If Not FormulaCells Is Nothing Then

For Each cell In FormulaCells

If cell.Value < 0 Then

cell.Interior.Color = RGB(255, 0, 0)

Else

cell.Interior.Color = xlNone

End If

Next cell

End If

‘ Process the constant cells

If Not ConstantCells Is Nothing Then

For Each cell In ConstantCells

If cell.Value < 0 Then

cell.Interior.Color = RGB(255, 0, 0)

Else

cell.Interior.Color = xlNone

End If

Next cell

End If

End Sub

Duplicating rows a variable number of times

The example in this section demonstrates how to use VBA to create duplicates of a row. Take an Example Column A contains the name, and column B contains the number of tickets purchased by each person.

Name	No of Ticket
Sumit	1
Punam	3
Sandeep	5
Amit	9

The goal is to duplicate the rows so that each person will have a row for each ticket purchased. For example, Sandeep purchased five tickets, so he should have five rows. The procedure to insert the new rows is shown below:

Sub InsertRows()

Dim cell As Range

' 1st cell with number of tickets

Set cell = Range("B2") 

Do While Not IsEmpty(cell)

If cell > 1 Then

Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, _

0)).EntireRow.Insert

Range(cell, cell.Offset(cell.Value - 1, 1)).EntireRow.FillDown

End If

Set cell = cell.Offset(cell.Value, 0)

Loop

End Sub

The cell object variable is initialized to cell B2, the first cell that has a number. The loop inserts new rows and then copies the row using the FillDown method. The cell variable is incremented to the next person, and the loop continues until an empty cell is encountered. Below is the output shows the worksheet after running this procedure.

Name	No of Ticket
Sumit	1
Punam	3
Punam	3
Punam	3
Sandeep	5
Sandeep	5
Sandeep	5
Sandeep	5
Sandeep	5
Amit	9
Amit	9
Amit	9
Amit	9
Amit	9
Amit	9
Amit	9
Amit	9
Amit	9

Writing Ranges

This code inserts the values into an array and then uses a single statement to transfer the contents of an

array to the range.

Sub ArrayFillRange()

‘ Fill a range by transferring an array

Dim CellsDown As Long, CellsAcross As Integer

Dim i As Long, j As Integer

Dim StartTime As Double

Dim TempArray() As Long

Dim TheRange As Range

Dim CurrVal As Long

‘ Get the dimensions

CellsDown = InputBox(“How many cells down?”)

If CellsDown = 0 Then Exit Sub

CellsAcross = InputBox(“How many cells across?”)

If CellsAcross = 0 Then Exit Sub

‘ Record starting time

StartTime = Timer

‘ Redimension temporary array

ReDim TempArray(1 To CellsDown, 1 To CellsAcross)

‘ Set worksheet range

Set TheRange = ActiveCell.Range(Cells(1, 1), _

Cells(CellsDown, CellsAcross))

‘ Fill the temporary array

CurrVal = 0

Application.ScreenUpdating = False

For i = 1 To CellsDown

For j = 1 To CellsAcross

TempArray(i, j) = CurrVal + 1

CurrVal = CurrVal + 1

Next j

Next i

‘ Transfer temporary array to worksheet

TheRange.Value = TempArray

‘ Display elapsed time

Application.ScreenUpdating = True

MsgBox Format(Timer - StartTime, “00.00”) & “ seconds”

End Sub

 More Example in  Some Useful Macro Procedure-2