The following form is creating problem when I delete an entire row.
The combo box works fine when I open the form the first time, then
when I delete a row it should recalculate the range to populate the
combo box, but it does not. The procedure stops at this level
highlighted in red (CmyLastARow = LastCell(Worksheets
("EnterCowData")).Row ). I do not know what to do, do you have any
hint?
Thanks.
Marie-Joelle
Option Explicit
Dim FirstAIDate As Variant
Dim SecondAIDate As Variant
Dim ThirdAIDate As Variant
Dim CmyLastRow, CmyProbRange, CmyLastCell, CmyLastARow As Variant
Dim DeleteRange As Variant
Dim CmyRange As Variant
Dim CowID As Integer
Dim CowListStart, CmyLastProbRow, CowListProbStart As range
Dim i, j, k As Integer
Dim CowStart As range
Dim message As Variant
Private Sub UserForm_initialize()
Worksheets("EnterCowData").Activate
CmyLastRow = LastCell(Worksheets("EnterCowData")).Address
CmyRange = "A3:" & CmyLastRow
'sort the range by CowID
range(CmyRange).Select
Selection.Sort Key1:=range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Worksheets("EnterCowData").range(CmyRange).Name = "Options"
cboCowList.RowSource = "Options"
cboCowList.BoundColumn = 1
cboCowList.ColumnCount = 1
Application.ScreenUpdating = True
End Sub
Private Sub cboCowList_Click()
Worksheets("EnterCowData").Activate
CmyLastARow = LastCell(Worksheets("EnterCowData")).Row
CmyRange = "A3: A" & CmyLastARow
Set CowListStart = Worksheets("EnterCowData").range("A3")
CowID = cboCowList.Value
i = 0
Do Until i = CmyLastARow + 1
If CowListStart.Offset(i, 0).Value = CowID Then
k = i
End If
i = i + 1
Loop
End Sub
Private Sub cmdProblemDelete_Click()
CowID = cboCowList.Value
DeleteRange = "A" & CowListStart.Offset(k, 0).Row
message = "Are you sure you want to delete cow " & CowID & "?"
If MsgBox(message, vbQuestion + vbYesNo, _
"Confirm Delete") = vbYes Then
' Delete current row:
Worksheets("EnterCowData").Activate
range(DeleteRange).EntireRow.Delete
End If
End Sub