VBA open worksheet based on formula value in a cell

[blossomthe2nd]

Hi All

I am using a formula in excel cell (E13) to determine what spreadsheet should be opened.

Then I have a Drop down in Cell D13 that either triggers the open or not . See code below but I am getting an error on the varCellValue - any guidence would really be appreciated

Thanks


A

Sub Worksheet_Change(ByVal Target As Range)
 
    Dim WatchRange          As Range
    Dim IntersectRange      As Range
    
    Dim varCellvalue As Long
    varCellvalue = Range("E13").Value
    Set WatchRange = Range("D13")
    Set IntersectRange = Intersect(Target, WatchRange)
    
 If Not (IntersectRange Is Nothing) And (Target = "Capitalisation") Then
        Workbooks.Open Filename:= _
        "S:\Business Support\" & varCellvalue & ".xls"
        
  End If
    On Error GoTo 0
    

If Not (IntersectRange Is Nothing) And (Target = "Part Payment") Then
        Workbooks.Open Filename:= _
        "S:\Business Support\" & varCellvalue & ".xls" 
       
 End If
    On Error GoTo 0

End Sub

 

[JoeMo]

What is the error? What is varCellValue when you get the error? varCellValue is dim'd as a long - do your workbooks really have just a number as a file name (e.g. 2014.xls)?

 

[blossomthe2nd]

When I run the debugger its like the varCellValue is 0 rather than the text result of the formula

 

[JoeMo]

When I run the debugger its like the varCellValue is 0 rather than the text result of the formula

Can you answer my other questions? If the formula is returning text then you don't want to dimension varCellValue as a Long.

 

[blossomthe2nd]

Hey Joe

Sorry I was away for weekend - yes it is giving text back - so I changed varCellValue to string and the code worked -

Thanks for your help

A