How do I change the code to reflect the result of Cell H1 dynamically?

[ojmeier]

I am trying to change the following code to open an existing WB depending on the result of cell H1. :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Address(0, 0) = "H1" And Target <> "" Then

Cancel = True

If Evaluate("isref('" & Target & "'!a1)") Then

Workheets(CStr(Target)).Select

'for opening new WB line below would be working. but it is not depending on result of H1!!

'Workbooks.Open "D:\ExcelVBA\341.xlsx"

Else

MsgBox "Sheet not found"

End If

End If

End Sub


Thanks

 

[DanteAmor]

Try again with this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Address(0, 0) = "H1" And Target <> "" Then
    Dim sName As String
    Cancel = True
    If Evaluate("isref('" & Target & "'!a1)") Then
      Sheets(CStr(Target.Value)).Select
    Else
      MsgBox "Sheet not found"
    End If
    '
    sName = "D:\ExcelVBA\test\" & Target.Value & ".xlsx"
    If Dir(sName) <> "" Then
      Workbooks.Open sName
    Else
      MsgBox "File not found"
    End If
  End If
End Sub

 

[ojmeier]

Thanks again for your input and solution. I tried it and it worked.. the only snafu was that I got the message "Sheet not found" and after clicking "OK" on the message it opened the WB.

 

[ojmeier]

I took out some of the lines as per below, It works perfectly well. Thank you very much for your help. You helped an old guy to feel good. Thanks again.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(0, 0) = "H1" And Target <> "" Then
Dim sName As String
Cancel = True
'If Evaluate("isref('" & Target & "'!a1)") Then
' Sheets(CStr(Target.Value)).Select
'Else
'MsgBox "Sheet not found"
'End If
'
sName = "D:\ExcelVBA\test\" & Target.Value & ".xlsx"
If Dir(sName) <> "" Then
Workbooks.Open sName
Else
MsgBox "File not found"
End If
End If
End Sub

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[ojmeier]

I am so impressed by your engagement for the task. Thanks again and all the best.... Let me know if I can do anything for you.