This is because the
Worksheets object Item Property returns an object (or in VB speak a Variant data type). The code completion dropdown doesn't know which methods to show because the return is not strongly typed to a
Worksheet object. Short of Excel either changing the code to use a strongly typed collection or heuristically figuring out the data type there is no way to change this behavior for the Item property.
You can however assign the returned object to a typed variable and the code completion will work. For example:
Worksheet Sheet1 = Worksheets(1)
You will get the completion list when you type the period after Sheet1. This is because the returned object is now cast as a
Worksheet type and Excel knows what methods are associated with that type.