Run time error 438 - Hide Columns Macro

[dgilbert]

Hi,

I am encountering a run time error 438 "Object doesn't support this property or method" when opening a spreadsheet containing the following macro:


Sub ShowHideColumns()
Const NameRow As Long = 7
Dim DataEntrySheet As Worksheet
Dim sSupervisorName As String
Dim LastColumn As Long
Dim lColumn As Long
Dim ColLetter As String

Application.ScreenUpdating = False
Set DataEntrySheet = Worksheets("Data Entry")
sSupervisorName = Worksheets("Data Entry").cmbSupervisorName.List(Worksheets("Data
Entry").cmbSupervisorName.ListIndex)

Cells.Select
Selection.EntireColumn.Hidden = False
LastColumn = FindLastColumn(DataEntrySheet, NameRow)
ColLetter = ColumnLetter(LastColumn)
If sSupervisorName <> "All" Then
For lColumn = 5 To LastColumn
If DataEntrySheet.Cells(NameRow, lColumn) <> sSupervisorName Then
DataEntrySheet.Columns(lColumn).Hidden = True
End If
Next lColumn
End If
Application.ScreenUpdating = True
DataEntrySheet.Range("A1").Select
Set DataEntrySheet = Nothing
End Sub

Public Function FindLastColumn(ByVal WS As Worksheet, lRow As Long) As Long
FindLastColumn = WS.Cells(lRow, Columns.Count).End(xlToLeft).Column
End Function

Public Function ColumnLetter(ByVal lngCol As Long) As String
'This function will return a Column Number to a Column Letter
' =ColumnLetter(10) This will return "J"
Dim vArr As Variant

vArr = Split(Cells(1, lngCol).Address(True, False), "$")
ColumnLetter = vArr(0)
End Function





Private Sub Workbook_Open()
' Add names here
Me.Worksheets("Data Entry").cmbSupervisorName.List = _
Array("Cheryl Hargens", "Cody McDonald", "Dennis Fish", "Don Rees", "Doug Finken", "Fiona Arvidson", "Gloria
Norris", "Jace Foley", "James Nelson", "Jeff Huff", "Jeremy Loeding", "Joe Rhodd", "John Anderson", "Laurie
Musick", "Lucy Campbell", "Matt Suk", "Pouchama Lothi", "Mike Holland", "Paul Mindermann", "Ryan Schnathorst",
"Shane Stille", "Shiela Raygor", "Solomon Brownlee", "Tre Scott", "Troy Stuart", "Vince Lovanh", "All")
End Sub



When I debug the error it stops at the blue highlighted area. This is code that I got from a thread I posted to this forum on August 20, 2014 ("Hide columns based on cell value") and it worked fine until recently. This macro hides certain columns depending on the name entered in a combox corresponding to supervisor names on row 7 in the worksheet.

Any help on fixing this is appreciated.

Thanks!!

[Aflatoon]

I would be 99% certain you've been hit by this: http://excelmatters.com/2014/12/10/office-update-breaks-activex-controls/

[dgilbert]

This was helpful and fixed the problem. Thank you very much!