Please see the “Assignment 4” screencast for a detailed demonstration of what you are trying to create in this assignment.
In the starter file, we have the seven dwarves and their phone numbers:
Obviously, with so few records it would be easy just to edit the cells/spreadsheet directly. However, if you had hundreds or thousands of records (rows) and perhaps a dozen or two columns of data, it would be more difficult to modify/update the data. Therefore, it’s nice to manage/organize the data in a central user form. The drop-down list here could easily be modified to be a search tool by utilizing what you have learned in the course.
Module1 of the file “Assignment 4 – STARTER.xlsm” has 5 separate subroutines:
• RunForm – the on-sheet button is linked to this sub, which simply opens up the NameForm user form.
• PopulateComboBox – Prior to opening NameForm, this sub should populate ComboBox1 on NameForm with the names in column A of the spreadsheet.
• AddName – This sub should enable the user to add another record (row). The name of the new contact is entered into the NewName text box and when the AddButton is clicked, an input box should ask the user for the phone number of the new contact. The new name and phone number should be placed in the next empty row of the spreadsheet with the other data.
• DeleteItem – After selecting a name from the combo box, the user can delete that contact by clicking on the DeleteButton, which will run the DeleteItem sub. Make sure to remove the deleted item from ComboBox1; otherwise, the deleted item will remain on the drop-down list until the user form is closed. See the second part of the screencast "Introduction to combo boxes" for hints on how to delete items.
• UpdateNumber – The user can select a name from ComboBox1 and modify/update the phone number of that contact by clicking on the PhoneButton. This should run the UpdateNumber sub, which will ask the user for the new number and replace that contact’s old number on the spreadsheet with the new number.
I have made everything work, but the grader isn't reading my code, can anyone help me go through my code?
Option Explicit
Sub RunForm()
PopulateComboBox
NameForm.Show
End Sub
Sub PopulateComboBox()
'Place your code here!
Dim Names() As String, n As Integer, i As Integer
n = WorksheetFunction.CountA(Columns("A:A"))
ReDim Names
As String
For i = 1 To n
Names(i) = Range("A1:A" & n).Cells(i, 1)
NameForm.ComboBox1.AddItem Names(i)
Next i
End Sub
Sub AddName()
Dim nRows As Integer, i As Integer, pn As String
'Input validation
If NameForm.NewName = "" Then
MsgBox "Name field cannot be left blank!"
Exit Sub
End If
pn = InputBox("Please Enter " & NameForm.NewName & "'s phone number.", "New Name")
nRows = Application.WorksheetFunction.CountA(Range("A:A"))
Range("A" & nRows + 1) = NameForm.NewName
Range("B" & nRows + 1) = pn
'Place your code here!
End Sub
Sub DeleteItem()
Dim RemoveIndex As Integer, Ans As Integer
Ans = MsgBox("Are you sure you want to delete this record?!", 20)
If Ans = 6 Then
'Place your code here!
'HINT: Whenever you delete an entire row, you'll want to
'add an entire row below all data; otherwise, the on-sheet
'button will move position on the sheet until it is deleted
RemoveIndex = NameForm.ComboBox1.ListIndex
NameForm.ComboBox1.RemoveItem RemoveIndex
Rows(RemoveIndex + 1 & ":" & RemoveIndex + 1).Select
Selection.Delete Shift:=xlUp
End If
Range("A1").Select
NameForm.ComboBox1.Value = Range("A1")
End Sub
Sub UpdateNumber()
Dim Ans As String, Index As Integer
Ans = InputBox("What is " & NameForm.ComboBox1.Value & "'s new phone number?")
If Ans <> "" Then 'Protects against empty input OR cancel button
Index = NameForm.ComboBox1.ListIndex + 1
Cells(Index, 2) = WorksheetFunction.Transpose(Ans)
'Place your code here!
End If
End Sub
In the starter file, we have the seven dwarves and their phone numbers:
Obviously, with so few records it would be easy just to edit the cells/spreadsheet directly. However, if you had hundreds or thousands of records (rows) and perhaps a dozen or two columns of data, it would be more difficult to modify/update the data. Therefore, it’s nice to manage/organize the data in a central user form. The drop-down list here could easily be modified to be a search tool by utilizing what you have learned in the course.
Module1 of the file “Assignment 4 – STARTER.xlsm” has 5 separate subroutines:
• RunForm – the on-sheet button is linked to this sub, which simply opens up the NameForm user form.
• PopulateComboBox – Prior to opening NameForm, this sub should populate ComboBox1 on NameForm with the names in column A of the spreadsheet.
• AddName – This sub should enable the user to add another record (row). The name of the new contact is entered into the NewName text box and when the AddButton is clicked, an input box should ask the user for the phone number of the new contact. The new name and phone number should be placed in the next empty row of the spreadsheet with the other data.
• DeleteItem – After selecting a name from the combo box, the user can delete that contact by clicking on the DeleteButton, which will run the DeleteItem sub. Make sure to remove the deleted item from ComboBox1; otherwise, the deleted item will remain on the drop-down list until the user form is closed. See the second part of the screencast "Introduction to combo boxes" for hints on how to delete items.
• UpdateNumber – The user can select a name from ComboBox1 and modify/update the phone number of that contact by clicking on the PhoneButton. This should run the UpdateNumber sub, which will ask the user for the new number and replace that contact’s old number on the spreadsheet with the new number.
I have made everything work, but the grader isn't reading my code, can anyone help me go through my code?
Option Explicit
Sub RunForm()
PopulateComboBox
NameForm.Show
End Sub
Sub PopulateComboBox()
'Place your code here!
Dim Names() As String, n As Integer, i As Integer
n = WorksheetFunction.CountA(Columns("A:A"))
ReDim Names
As StringFor i = 1 To n
Names(i) = Range("A1:A" & n).Cells(i, 1)
NameForm.ComboBox1.AddItem Names(i)
Next i
End Sub
Sub AddName()
Dim nRows As Integer, i As Integer, pn As String
'Input validation
If NameForm.NewName = "" Then
MsgBox "Name field cannot be left blank!"
Exit Sub
End If
pn = InputBox("Please Enter " & NameForm.NewName & "'s phone number.", "New Name")
nRows = Application.WorksheetFunction.CountA(Range("A:A"))
Range("A" & nRows + 1) = NameForm.NewName
Range("B" & nRows + 1) = pn
'Place your code here!
End Sub
Sub DeleteItem()
Dim RemoveIndex As Integer, Ans As Integer
Ans = MsgBox("Are you sure you want to delete this record?!", 20)
If Ans = 6 Then
'Place your code here!
'HINT: Whenever you delete an entire row, you'll want to
'add an entire row below all data; otherwise, the on-sheet
'button will move position on the sheet until it is deleted
RemoveIndex = NameForm.ComboBox1.ListIndex
NameForm.ComboBox1.RemoveItem RemoveIndex
Rows(RemoveIndex + 1 & ":" & RemoveIndex + 1).Select
Selection.Delete Shift:=xlUp
End If
Range("A1").Select
NameForm.ComboBox1.Value = Range("A1")
End Sub
Sub UpdateNumber()
Dim Ans As String, Index As Integer
Ans = InputBox("What is " & NameForm.ComboBox1.Value & "'s new phone number?")
If Ans <> "" Then 'Protects against empty input OR cancel button
Index = NameForm.ComboBox1.ListIndex + 1
Cells(Index, 2) = WorksheetFunction.Transpose(Ans)
'Place your code here!
End If
End Sub
