0

I have a sub that accepts data for creating and populating a column, which includes Autofilling a given string. I was looking to include an argument for the Autofill type, and have a dropdown list pop up when prompted to select a type. From what I've read, this is possible by declaring an enumeration, and using it in the argument list, which I found in the following links.

VBA Function argument list select

http://www.cpearson.com/excel/Enums.aspx

However, despite following these sites, I cannot get a dropdown list for the enum argument, and I'm not sure what I'm doing wrong.

Sub Test()
Call AutoFillNewColumn(, , , 0)
End Sub

Public Sub AutoFillNewColumn(ColumnHeader As Range, ColumnTitle As String, ByVal Formula As String, fillType As AutofillType)
'Purpose: To quickly populate a column with a header and common formula.

'Declare variables.  Used for acquiring specific workbook/worksheet information for later.
    Dim WB, WS As String
        WB = ColumnHeader.Worksheet.Parent.Name
        WS = ColumnHeader.Worksheet.Name
'Set column header.
    ColumnHeader.value = ColumnTitle
'Input formula in 2nd cell of column.
    ColumnHeader.Offset(1, 0).value = Formula
'Autofill formula.  Looks complicated, but this is all designed so it can work regardless of what workbook or worksheet it is being used on.
    If IsEmpty(Workbooks(WB).Sheets(WS).Range("A3")) = False Then
        ColumnHeader.Offset(1, 0).AutoFill _
            Destination:=Workbooks(WB).Sheets(WS).Range(ColumnHeader.Offset(1, 0), _
            Workbooks(WB).Sheets(WS).Cells(Workbooks(WB).Sheets(WS).Cells.Find _
            (What:="*", After:=Workbooks(WB).Sheets(WS).Cells(1), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).row, _
            ColumnHeader.Column)), Type:=typeFill
    End If
End Sub

    Public Enum AutofillType
        xlFillDefault = 0
        xlFillCopy = 1
        xlFillDays = 2
        xlFillFormats = 3
        xlFillValues = 4
        xlFillDays = 5
        xlFillWeekdays = 6
        xlFillMonths = 7
        xlFillYears = 8
        xlLinearTrend = 9
        xlGrowthTrend = 10
        xlFlashFill = 11
    End Enum

My result

FEZ_R
  • 5
  • 3
  • 2
    Move the `Enum` to the top. Also, `xlFillDays` is occurring twice. The `Call` keyword is considered deprecated: just use `AutoFillNewColumn , , , 0`. In the line `Dim WB, WS As String` `WB` is declared as `Variant`. Use `Dim WB As String, WS As String` if you need to declare in one line. Also, these variable names are most often used for objects, so e.g. `wbName` and `wsName` would be more appropriate. – VBasic2008 Jul 02 '21 at 16:00
  • That did the trick, thanks! I'm looking back at the Pearson link now and I completely glossed over this. 'The Enum must be declared at the module-level; that is, before and outside of any procedure declaration." – FEZ_R Jul 02 '21 at 16:06

0 Answers0