How to count the number of items to be provided in Dynamic dropdown of RibbonX using lastrow in VBA-CodePudding

I have 2 dropdown in Excel customUI. 1st dropdown counts the number of items using .End(xlUp) while in 2nd dropdown number of items is directly given as 5 ie returnedVal2 = 5.The first dropdown Getitemcount1 gives me error while using .End(xlUp) formula. But in second dropdown as returnedVal2=5 is directly written so there is no error. What is the cause of the error here ?


Sub GetItemCount1(control1 As IRibbonControl, temp As Long, ByRef returnedVal1)
 Dim varItems As Long
varItems = Sheet1.Cells(Rows.Count, "A").End(xlUp) - 3
returnedVal1 = varItems
End Sub

Sub GetItemLabel1(control1 As IRibbonControl, index1 As Integer, ByRef returnedVal1)
returnedVal1 = Sheet1.Cells(index1   4, "A").Value
End Sub

Sub GetItemCount2(control2 As IRibbonControl, ByRef returnedVal2)
returnedVal2 = 5
End Sub

Sub GetItemLabel2(control2 As IRibbonControl, index2 As Integer, ByRef returnedVal2)
returnedVal2 = Sheet1.Cells(index2   4, "B").Value
End Sub

CodePudding user response：

Sheet1.Cells(Rows.Count, "A").End(xlUp) is a cell resp. a range and varItems = Sheet1.Cells(Rows.Count, "A").End(xlUp) - 3 will return the value of the cell and you subtract 3 which will not work in case the content of Sheet1.Cells(Rows.Count, "A").End(xlUp) is a text. You probably want the row number which is Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

PS It would be helpful if you said more about the error. Just stating gives me error is not helpful. You probably get a run time error 13 but that is just a guess.