(Solved) – Why does my form control randomly get an error in VBA on a macro in Excel and not at other times?


I am trying to create a macro in Excel which will insert a new line into the spreadsheet and also insert a few form controls on that line. The first form control I’ve made is a DropDown. Usually, the code will work and it inserts the DropDown box just fine and in functioning order. This is the code I’ve been using (although I’ve varied it a bit with the same result).

Dim Pages As Range
Set Pages = ActiveCell
Dim RLForm As Excel.Shape
Set RLForm = ActiveSheet.Shapes.AddFormControl (2, Pages.Offset(0, 1).Left, Pages.Offset(0, 1).Top, Pages.Offset (0, 1).Width, Pages.Offset(0, 1).Height)
With Selection
     .ListFillRange = "'Forms Material'!$B$1:$B$5"
     .LinkedCell = rgFound.Offset (-1, -1).Address
     .DropDownLines = 5
     .Display3DShading = False
End With

Sometimes, seemingly at random, I get the error message “Unable to set the ListFillRange property of the DropDown class.” The debugger identifies the problem at the first part of the “With Selection” set of lines. (If I delete the .ListFillRange line, then I get a similar message for the .LinkedCell line. Also, if I try skipping the “With Selection” syntax and instead do “RLForm.ListFillRange = …” then I get identical problems.) At other times, I get “Object doesn’t support this property or method.” Often times I will be working along on code several lines past this, on completely unrelated objects and such (lately I’ve been working on a new form control) and I will run the code and get those error messages. When I try to run it again, it will keep giving me the same error message. After a while of fiddling with it, it will work for no discernible reason. Often times if I close the VBA editor and then reopen it, the code will work again just fine for a while.

Neither the cause of the problem nor the solution seem to have any consistency that I can determine. Last time, I ran the code and it worked, then I changed the location of a new form control that I was adding several lines below from 20 points to 19, and then I got the error message. Similarly, it won’t work, then I will restart the VBA editor, and it will work.

It’s also really difficult to troubleshoot because I don’t have any control over when I get the error or when it works! :/

Leave a Reply

Your email address will not be published. Required fields are marked *