Macro button to open different workbooks depending on a cell value?

[dplumlee12]

Is there a way to use a cell value in one workbook to address a macro opening another workbook?

So in workbook Source.xlsm i have a cell that will have a value 1-10. Can I have a macro button that will open the appropriate destination workbook? Destination1.xlsm or Destination2.xlsm... depending on the cell value from source.xlsm?

 

[JoeMo]

Can be done. Are the workbooks you want to open in the same folder as the workbook that will contain the macro and button? If not, what is the file path to those workbooks?

 

[dplumlee12]

Yes the workbooks will be in the same folder.

Can be done. Are the workbooks you want to open in the same folder as the workbook that will contain the macro and button? If not, what is the file path to those workbooks?

 

[JoeMo]

Yes the workbooks will be in the same folder.

Try this. Please note the comment pertaining to the cell address where you will have the workbook name. I'll leave it to you to assign the macro to a button on the sheet that contains the name of the workbook to be opened.

Sub OpenWorkbook()
'To be run from the workbook/sheet housing the number of workbook to open
'Assumes workbook name to open is in cell A1 - change to suit
Const sNam As String = "Dimension"
wbNam = sNam & Range("A1").Value & ".xlsm"  'Change range to suit
If Not WorkbookOpen(wbNam) Then
    Workbooks.Open FileName:=ThisWorkbook.Path & _
        Application.PathSeparator & wbNam
End If
End Sub
Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

 

[dplumlee12]

First I want to thank you for helping me with this.
When I run the code I am getting a compile error: ByRef argument type mismatch. I highlighted the section that the debugger is highlighting.

Try this. Please note the comment pertaining to the cell address where you will have the workbook name. I'll leave it to you to assign the macro to a button on the sheet that contains the name of the workbook to be opened.

Sub OpenWorkbook()
'To be run from the workbook/sheet housing the number of workbook to open
'Assumes workbook name to open is in cell A1 - change to suit
Const sNam As String = "Dimension"
wbNam = sNam & Range("A1").Value & ".xlsm"  'Change range to suit
If Not WorkbookOpen(wbNam) Then
    Workbooks.Open FileName:=ThisWorkbook.Path & _
        Application.PathSeparator & wbNam
End If
End Sub
Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

 

[JoeMo]

First I want to thank you for helping me with this.
When I run the code I am getting a compile error: ByRef argument type mismatch. I highlighted the section that the debugger is highlighting.

Add this line immediately after the line: Const sNam As String = "Dimension"

Dim wbNam as String

 

[dplumlee12]

Ok got past that point but getting another compile error due to the end sub in the middle of the code. Was I supposed to put that there?

Add this line immediately after the line: Const sNam As String = "Dimension"

Dim wbNam as String

 

[JoeMo]

Ok got past that point but getting another compile error due to the end sub in the middle of the code. Was I supposed to put that there?

End Sub ends the subroutine. What's below that is a function used to see if the workbook you want to open is already open. In the VB editor there should be a line between End Sub and Function indicating that Excel recognizes the termination of one and start of the other. Hopefully, you copied both directly from the post and pasted them in one window. What exactly does the compile error say and what does it highlight?

 

[dplumlee12]

My mistake... double entry of end function. Works like a dream. Thank you