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.
UserForms on VBA
-Using offset() method.
-Work more with ranges.
-Example on UserForm.
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.
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.
ActiveCell.Offset (1,0) = 1
Place a "1" one row
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.
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:
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.
open Visual Basic Editor.
2- Insert | UserForm
View-> Code / or Enter F7
-From Object menu Choose UserForm
-From Procedure menu choose Initialize
Private Sub UserForm_Initialize()
txtName.Value = ""
txtPhone.Value = ""
cboDepartment.Value = ""
cboCourse.Value = ""
chFullTime = False
IfIsEmpty(ActiveCell) = False Then
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
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"
IffullTime = True Then
ActiveCell.Offset(0, 5).Value = "Yes"
ActiveCell.Offset(0, 5).Value = "No"