Run Time Error 9

[markcranmer]

Hi All

I'm pretty new to VBA and have run into a stubbon error when working on a textbook example.

The sample code is supposed to write settings from an add-in to the User Interface Worksheets.

The error is 'Run Time Error 9, Subscript Out of Range'

Any help with this would be greatly appreciated.

(Excel 2007 & XP Pro)

Mark


The code is:

Option Explicit

Private Const msFILE_TEMPLATE As String = "testworkbook.xltx"
Private Const msRNG_NAME_LIST As String = "tblRangeNames"
Private Const msRNG_SHEET_LIST As String = "tblSheetNames"


Public Sub WriteSettings()

    Dim rngSheet As Range
    Dim rngSheetList As Range
    Dim rngName As Range
    Dim rngNameList As Range
    Dim rngSetting As Range
    Dim sSheetTab As String
    Dim wkbTemplate As Workbook
    Dim wksSheet As Worksheet
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set wkbTemplate = Application.Workbooks(msFILE_TEMPLATE)
    
    Set rngSheetList = UISettings.Range(msRNG_SHEET_LIST)
    
    Set rngNameList = UISettings.Range(msRNG_NAME_LIST)
    
    For Each rngSheet In rngSheetList
        
        sSheetTab = sSheetTabName(wkbTemplate, rngSheet.Value)
        Set wksSheet = wkbTemplate.Worksheets(sSheetTab) <Run Time Error 9 HERE>
        
        For Each rngName In rngNameList
        
            Set rngSetting = Intersect(rngSheet.EntireRow, _
                                            rngName.EntireColumn)
                                            
            If Len(rngSetting.Value) > 0 Then
                wksSheet.Names.Add rngName.Value, _
                                            "=" & rngSetting.Value
            End If
            
        Next rngName
        
    Next rngSheet
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub



Public Function sSheetTabName(ByRef wkbProject As Workbook, _
                           ByRef sCodeName As String) As String
                
    Dim wksSheet As Worksheet

    For Each wksSheet In wkbProject.Worksheets
        If wksSheet.CodeName = sCodeName Then
            sSheetTabName = wksSheet.Name
            Exit For
        End If
    Next wksSheet
End Function