FORM MENU call workbook

[sal21]

I have a form with many button named:
wb1
wb2
wb3
wb...
...

I need to assign for each button Wb, a command to aopen a excel workbook in c:\mydir\.
In effect button wb1 open the workbokk in c:\mydir\wb001.xls
... button wb2 open the workbokk in c:\mydir\wb2.xls
ecc...

How to?

but you should know that:

1) not possible to open a new workbook from the form until the last opned workbook not is closed.
2) all workbook have in Event Workbook Open a code to start other VBA for Excel code

[Rudi]

See if this will help..

Code: Select all

Private Sub wb1_Click()
Dim wBook As Workbook
    On Error Resume Next
    Set wBook = Workbooks("wb001.xls")
    If wBook Is Nothing Then    'Not open
        Workbooks.Open "c:\mydir\wb001.xls"
    Else
        MsgBox wBook.Name & " is still open", vbExclamation
        Exit Sub
    End If
End Sub

[HansV]

Sal, do you run this code from within Excel?

[sal21]

Sal, do you run this code from within Excel?

Sorry, yes in userform. I need to use VBA For Excel.

[HansV]

You could use code like this:

Code: Select all

Private Const strPath = "C:\MyDir\"
Private wbk As Workbook

Private Sub wb1_Click()
    OpenWb "wb001.xls"
End Sub

Private Sub wb2_Click()
    OpenWb "wb002.xls"
End Sub

Sub OpenWb(strWorkbook As String)
    Dim wsh As Worksheet
    On Error Resume Next
    Set wsh = wbk.Worksheets(1)
    On Error GoTo 0
    If wsh Is Nothing Then
        Set wbk = Workbooks(strPath & strWorkbook)
    Else
        MsgBox "Close " & wbk.Name & " first", vbExclamation
    End If
End Sub

The declarations at the beginning must be at the top of the userform module.

[sal21]

You could use code like this:

Code: Select all

Private Const strPath = "C:\MyDir\"
Private wbk As Workbook

Private Sub wb1_Click()
    OpenWb "wb001.xls"
End Sub

Private Sub wb2_Click()
    OpenWb "wb002.xls"
End Sub

Sub OpenWb(strWorkbook As String)
    Dim wsh As Worksheet
    On Error Resume Next
    Set wsh = wbk.Worksheets(1)
    On Error GoTo 0
    If wsh Is Nothing Then
        Set wbk = Workbooks(strPath & strWorkbook)
    Else
        MsgBox "Close " & wbk.Name & " first", vbExclamation
    End If
End Sub

The declarations at the beginning must be at the top of the userform module.

ERROR!!! See image

[HansV]

Select Tools | Options... in the Visual Basic Editor.
Activate the General tab.
Make sure that "Break in Class Module" is selected in the "Error Trapping" section, and not "Break on All Errors".

S0847.png

[sal21]

Select Tools | Options... in the Visual Basic Editor.
Activate the General tab.
Make sure that "Break in Class Module" is selected in the "Error Trapping" section, and not "Break on All Errors".

S0847.png

NOW ALL WORK PERFECT!
Tks.

Perpahs stupid question...
in the workbook i fill MyVar with "ok" is possible to return this value when i close the opened workbook end reuse for other code in the menu userform?

[HansV]

If you have a variable in a workbook and then close that workbook, that variable won't be available any more. You can only refer to variables in open workbooks.

[StuartR]

I use CustomDocumentProperties to store values that I want avaialble in VBA even after the workbook has been closed and reopened.

[Rudi]

That's actually an interesting thought.
They are just text fields and you can write anything to them (obviously only those not in use at the time).

Only backfires when someone comes along who uses these properties, opens the dialog and sees, Company Name: 56