In the name of allah
1 / 18

In The Name Of Allah - PowerPoint PPT Presentation

  • Uploaded on

In The Name Of Allah. UserForms on VBA Lab 06. Objectives. -Using offset() method. - Work more with ranges. -Example on UserForm . Using the offset method. Range object has an Offset property that can be useful when wants to move the active cell around.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'In The Name Of Allah' - hisano

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
In the name of allah
In The Name Of Allah

UserForms on VBA

Lab 06


-Using offset() method.

-Work more with ranges.

-Example on UserForm.

Using the offset method
Using the offset method

Range object has an Offsetproperty that can be useful when wants to move the active cell around.

For example , you might need to refer to the cell that’s two rows down and one column to the right of the active cell ,the offset method can do that.

In the name of allah

Range ( ) .offset (Rowoffset , Columnoffset)

Rowoffset : the number of rows to offset range ,use positive number to move down , negative number to move up.

Columnoffset: the number of columns to offset range , use positive number to move right , negative number to move left.

In the name of allah

ActiveCell.Offset (1,0) = 1 

Place a "1" one row

under the

Active cell (E6)

Reham AlMukhallafi - IS 424


-To make it easier for users to enter data in a workbook, you can create an Excel UserForm.

-When the message box is not sufficient any more to communicate with the user you need to start developing userforms.

In the name of allah

The form is used to require values, parameters and information from the user to feed the VBA procedure.

Different basic controls :

can be added to the userform they are called:











Set up the worksheet
Set up the worksheet

In this example:

User will enter data to fill information about his course, by opening the Excel UserForm, and filling the text box, and clicking a button.

Create an excel userform
Create an Excel UserForm

1-Alt+F11 to

open Visual Basic Editor.

2- Insert | UserForm

1 initialising the form
1- Initialising the Form

View-> Code / or Enter F7

-From Object menu Choose UserForm

-From Procedure menu choose Initialize

In the name of allah

Private Sub UserForm_Initialize()

txtName.Value = ""

txtPhone.Value = ""


.AddItem "Sales"

.AddItem "Marketing"

.AddItem "Administration"

.AddItem "Design"

.AddItem "Advertising"

.AddItem "Transportation"

End With

cboDepartment.Value = ""


.AddItem "Access"

.AddItem "Excel"

.AddItem "PowerPoint"

.AddItem "Word"

.AddItem "FrontPage"

End With

cboCourse.Value = ""

chFullTime = False

End Sub

In the name of allah

Private SubcmdOK_Click()

Worksheets("Course Bookings").Activate



IfIsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select


Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = txtName.Value

ActiveCell.Offset(0, 1) = txtPhone.Value

ActiveCell.Offset(0, 2) = cboDepartment.Value

ActiveCell.Offset(0, 3) = cboCourse.Value

In the name of allah

If optIntroduction = True Then

ActiveCell.Offset(0, 4).Value = "Intro"

Else IfoptIntermediate = True Then

ActiveCell.Offset(0, 4).Value = "Intermed"


ActiveCell.Offset(0, 4).Value = "Adv"

End If

IffullTime = True Then

ActiveCell.Offset(0, 5).Value = "Yes"


ActiveCell.Offset(0, 5).Value = "No"

End If


End Sub

Add code to the buttons
Add code to the buttons

Private SubcmdClearForm_Click()

Call UserForm_Initialize

End Sub

Private SubcmdCancel_Click()

Unload Me

End Sub

Create a button to open the excel userform
Create a Button to open the Excel UserForm

Sub OpenCourseBookingForm()


End Sub