1 / 18

In The Name Of Allah

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.

hisano
Download Presentation

In The Name Of Allah

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. In The Name Of Allah UserForms on VBA Lab 06

  2. Objectives -Using offset() method. -Work more with ranges. -Example on UserForm.

  3. 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.

  4. 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.

  5. Example: ActiveCell.Offset (1,0) = 1  Place a "1" one row under the Active cell (E6) Reham AlMukhallafi - IS 424

  6. UserForm -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.

  7. 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: Label TextBox ComboBox ListBox CheckBox OptionButton, Frame, CommandButton, SpinButton Image

  8. 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.

  9. Create an Excel UserForm 1-Alt+F11 to open Visual Basic Editor. 2- Insert | UserForm

  10. Add Controls to the Excel UserForm

  11. 1- Initialising the Form View-> Code / or Enter F7 -From Object menu Choose UserForm -From Procedure menu choose Initialize

  12. Private Sub UserForm_Initialize() txtName.Value = "" txtPhone.Value = "" WithcboDepartment .AddItem "Sales" .AddItem "Marketing" .AddItem "Administration" .AddItem "Design" .AddItem "Advertising" .AddItem "Transportation" End With cboDepartment.Value = "" WithcboCourse .AddItem "Access" .AddItem "Excel" .AddItem "PowerPoint" .AddItem "Word" .AddItem "FrontPage" End With cboCourse.Value = "" chFullTime = False End Sub

  13. Private SubcmdOK_Click() Worksheets("Course Bookings").Activate Range("A1").Select Do IfIsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select EndIf 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

  14. If optIntroduction = True Then ActiveCell.Offset(0, 4).Value = "Intro" Else IfoptIntermediate = True Then ActiveCell.Offset(0, 4).Value = "Intermed" Else ActiveCell.Offset(0, 4).Value = "Adv" End If IffullTime = True Then ActiveCell.Offset(0, 5).Value = "Yes" Else ActiveCell.Offset(0, 5).Value = "No" End If Range("A1").Select End Sub

  15. Add code to the buttons Private SubcmdClearForm_Click() Call UserForm_Initialize End Sub Private SubcmdCancel_Click() Unload Me End Sub

  16. Test the Excel UserForm

  17. Create a Button to open the Excel UserForm Sub OpenCourseBookingForm() frmCourseBooking.Show End Sub

  18. Any Question ?

More Related