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]

But what do you need?
Do you want to open a book with the data that is in the cell?

 

[ojmeier]

But what do you need?
Do you want to open a book with the data that is in the cell?

Thanks DanteAmor. Yes, I would like to open on DoubleClick a n existing workbook with the result of cell H1. In the present code I can open a worksheet within the workbook. In other words once the result of cell H1 is determined I would like to go an open a Workbook named exactly as the result of H1. (There will be about 2,000 workbooks with a possible number combination in H1). Thanks for your help.

 

[DanteAmor]

Try 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\" & Target & ".xlsx"
    If Dir(sName) <> "" Then
      Workbooks.Open sName
    Else
      MsgBox "File not found"
    End If
  End If
End Sub

 

[ojmeier]

Thank you DanteAmor. I got an compile error message. Did I mess up?

 

[DanteAmor]

Sorry, change the line for this:

sName = "D:\ExcelVBA\" & Target.Value & ".xlsx"

Change "D:\ExcelVBA\" by the name of your folder

 

[ojmeier]

thank you DanteAmor. the file(s) I want to open are in the Folder "D:\ExcelVBA\"
All files are in that very same folder.
The line
sName = "D:\ExcelVBA\" & Target.Value & ".xlsx" should be the right target folder.
thanks. below the "startpage where I want to take H1 as the cell to determine the targeted Workbook

 

[DanteAmor]

below the "startpage where I want to take H1 as the cell to determine the targeted Workbook

I did not understand that part.
The macro you asked for is to open a book, the name of the book is in cell H1, so double click on cell H1 and open the book.
The macro opened the book, does it work for you already?

 

[ojmeier]

Thanks, When I double click H1 the above error pops up.

 

[ojmeier]

I made a new folder and in that folder (D:\ExcelVBA\Test) I have the Start page with that H1 cell and in the same folder I have the WB "341.xlsx".When I double click H1 I get the same error message. Where am I wrong? I am sorry to be so dumb ...