Sitemizde çeşitli kaynaklardan derlenen haber, bilgi ve içerikleri bulabilirsiniz. Şikayet ve telif bildirimleriniz için lütfen tıklayın.
Using Visual Basic to Initialise, Control and Configure a UserForm in Excel 2007 and Excel 2010UserForms allow you to design and create fully customisable dialogue boxes for users of your workbooks. T...
UserForms allow you to design and create fully customisable dialogue boxes for users of your workbooks. They enable you to:
Design a User Interface that is easy and intuitive for users to use Reduce the amount of manual data entry required when entering data and also the number of errors in your data Control data entry by error checking data before it is entered into your workbook Finally, a well designed UserForm is visually impactful and will make the workbook look professionalHowever, a UserForm without Visual Basic controlling the configuration of all the controls you have added (combo boxes, command buttons etc.) will have no functionality and the user will be unable to interact with it, or use it.
Before beginning to code a UserForm, you will need to create one. I have a hub in which I create a Hotel Reservation Interface which uses several controls including Command Buttons, Combo Boxes, Text boxes, Spin Bars and finally Option and Radio Buttons. That hub can be found here:
http://robbiecwilson.hubpages.com/hub/User-Interface-design-using-a-UserForm-in-Excel-2007-and-Excel-2010
In this hub, we will write a number of scripts:
First for when the UserForm is initialized (in other words when the user clicks on the Reservations button which launches our Hotel Reservation Interface and therefore the UserForm) Next, we need a script for when a user clicks the Add Another Reservation button A further script is needed for when the user selects the Close Window button Finally, we need a script for the Scroll Bar so that the user's selection is displayed on screen and also stored so that it can be entered into the worksheet along with the other details of the reservation Example of a Hotel Reservation User Interface created with Visual Basic and a UserForm in Excel 2007 or Excel 2010. | SourceExample of the output from a UserForm in a worksheet in Excel 2007 or Excel 2010. | SourceTo access the code assigned to our UserForm,
On the Developer tab click the View Code button in the Controls Group Scroll down to Forms and double click on the UserForm you created Adding Visual Basic code to a UserForm in Excel 2007 or Excel 2010. | SourceWhen you double click the UserForm it will open up the code page. Change the first line to the following and then add all the subsequent lines:
Private Sub UserForm_Initialize()
Nationality.Value = ""
Names.Value = ""
With Guests
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
End With
CarNo = True
BreakfastNo = True
Nights.Max = 20
End Sub
The figure below shows the code with remarks on each line to show what that line does.
Initialisation script for a UserForm created in Visual Basic in Excel 2007 or Excel 2010. | SourceThe script does a number of things:
First, it resets the Nationality and Name textboxes back to blanks Then it configures the Combo Box for the number of guests Next it resets the Option or Radio buttons for whether a guest has a Car or wants Breakfast to No Finally, it sets the maximum number of Nights a guest can stay to 20If you are a complete beginner in Visual Basic and you would like to know more about how to get started writing basic code, I have a hub that starts at the very beginning and introduces a number of useful commands as well as how to deal with errors which can be found here:
http://robbiecwilson.hubpages.com/hub/Visual-Basic-for-Excel-2007-and-Excel-2010-A-Beginners-Guide
Our Hotel Reservation User Interface uses two Command Buttons, the Add Another Reservation button and the Close button. These scripts will be run when the user clicks on one of the buttons
First, we will look at the script that activates when the user clicks Add Another Reservation
Private Sub CommandButton1_Click()
Sheets("Reservations").Activate
Dim sourceCol As Integer, rowCount As Integer
Dim currentRow As Integer
Dim currentRowValue As String
sourceCol = 1
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row
For currentRow = 1 To rowCount
currentRowValue = Cells(currentRow, sourceCol).Value
If IsEmpty(currentRowValue) Or currentRowValue = "" Then
Cells(currentRow, sourceCol).Select
End If
Next
Cells(currentRow, 1).Value = Names.Value
Cells(currentRow, 2).Value = Nationality.Value
Cells(currentRow, 3).Value = Guests.Value
Cells(currentRow, 4).Value = Number_of_Nights.Value
If CarYes.Value = True Then
Cells(currentRow, 5).Value = "Yes"
End If
If CarNo.Value = True Then
Cells(currentRow, 5).Value = "No"
End If
If BreakfastYes.Value = True Then
Cells(currentRow, 6).Value = "Yes"
End If
If BreakfastNo.Value = True Then
Cells(currentRow, 6).Value = "No"
End If
Nationality.Value = ""
Names.Value = ""
CarNo = True
BreakfastNo = True
Guests = ""
Nights.Value = 0
Number_of_Nights.Value = ""
End Sub
Visual Basic script used to configure a command button in a UserForm in Excel 2007 or Excel 2010. | SourceThis script is the most complicated that we will write for our Hotel Reservation User Interface. It is divided into four sections, as shown in the figure above:
The first part sets up the variables we will use to find the first available blank row and tells Excel which sheet we are working off. The second looks for the first empty row to add the next reservation. This ensures that a reservation is never over written The third part adds the data that has been entered into the spreadsheet The final part re-initiliases the UserForm so that a new client can be added without having to clear the details of the previous clientThe next script is used when the user selects the Close Window button
It reuses the code to add the details of the client's reservation into Excel that we wrote for the initialisation script and then it closes the UserForm
Private Sub CommandButton2_Click()
Sheets("Reservations").Activate
Dim sourceCol As Integer, rowCount As Integer
Dim currentRow As Integer
Dim currentRowValue As String
sourceCol = 1
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row
For currentRow = 1 To rowCount
currentRowValue = Cells(currentRow, sourceCol).Value
If IsEmpty(currentRowValue) Or currentRowValue = "" Then
Cells(currentRow, sourceCol).Select
End If
Next
Cells(currentRow, 1).Value = Names.Value
Cells(currentRow, 2).Value = Nationality.Value
Cells(currentRow, 3).Value = Guests.Value
Cells(currentRow, 4).Value = Number_of_Nights.Value
If CarYes.Value = True Then
Cells(currentRow, 5).Value = "Yes"
End If
If CarNo.Value = True Then
Cells(currentRow, 5).Value = "No"
End If
If BreakfastYes.Value = True Then
Cells(currentRow, 6).Value = "Yes"
End If
If BreakfastNo.Value = True Then
Cells(currentRow, 6).Value = "No"
End If
Unload Me
End Sub
Visual Basic script used to configure a button to close a UserForm in Excel 2007 or Excel 2010. | SourceAs before, the above figure shows the code, along with comments on each line illustrating what that line does.
The final script that we have to write is a script that captures the value the user selects with the scroll bar and stores this in a variable so that this can be displayed in the text box alongside the scroll bar and added along with the other reservation details into the workbook:
Private Sub Nights_Change()
Number_of_Nights.Text = Nights.Value
End Sub
To initiate our Hotel Reservation system, we need a button for the user to click when they want to add a reservation to the Excel workbook. To do this, we will use a Command button:
On the Developer tab, select the Insert button from the Controls group Under ActiveX Controls, select Command Button The cursor will change to a cross, use this to position and size the new button Command button created in Excel 2007 or Excel 2010. | Source Next, select the Design Mode button Right click on the new button and select Properties Select Caption and change CommandButton1 to Reservations Change Name to Reservations Configuring a Command button for use with a UserForm in Excel 2007 or Excel 2010. | SourceIf you would like to learn more about how to create, configure and use Command Buttons, I have a hub dedicated to them which can be found here:
http://robbiecwilson.hubpages.com/hub/Create-a-Command-Button-in-Excel-2007
Now we need to add the Visual Basic code to our button so that it launches the reservation system when it is clicked:
Select the button once more Click the View Code buttonYou will see the following text
Private Sub Reservations_Click()
End Sub
Add the following on a line between those two lines of codeReservations_Interface.Show
You will end up with the following:
Visual Basic script used to configure a Command button to launch a UserForm in Excel 2007 or Excel 2010. | Source Close the Visual Basic window Select Design Mode once more Test that the button launches our Reservation InterfaceBeing able to code in Visual Basic enables you to extend what Excel can do to more closely meet your and your user's needs. Today, we looked at creating code to bring a User Interface created using a UserForm to life. Users of my spreadsheet will now have a fully functional Hotel Reservation Interface which will allow them to enter a customer's reservation details into Excel quickly and easily and with fewer errors. These details will then be automatically added into a worksheet.
In today's hub:
We created a script that sets up our User Interface for use (configures all the buttons and bars) Next, we wrote scripts for our Command Buttons to allow users to add another reservation or close the Interface Code was added to a Scroll Bar to store and display the user's selection Finally, we added a Command Button to our workbook to launch the User InterfaceMany thanks for reading; I hope that you have found this hub useful and informative. I learnt a lot during the research of this hub and also writing the code for my Hotel Reservation Interface. Please feel free to leave a comment below and happy coding!
© 2013 Robbie C Wilson
Sign in or sign up and post using a HubPages Network account.
0 of 8192 characters usedPost CommentNo HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.
sendingNo comments yet.