VBA - Switching between Workbooks

[jhol4]

Hi Guys,

I posted a question earlier about using a RefEdit to link to external workbooks, however it seems that this cannot be done.

So my new question is does anyone have a good way of alternating between workbooks using VBA where the name of the workbook is likely to change regularly.

I usually use Windows(filename.xls).activate, however if I don't know what the filename is, this becomes difficult.

Thanks in advance.

 

[malo1]

What do I think, you ask. I think I should have tested that more carefully.

Please substitute this macro, just take it out back and shoot it:

Private Sub RightKlickMenuMayker()
Dim objBar As Object, objBtn As Object, i As Integer
Run "RightKlickMenuReeset"
Set objBar = Application.CommandBars.Add("wbNavigator", msoBarPopup)
For i = 1 To Workbooks.Count
Set objBtn = objBar.Controls.Add
With objBtn
.Caption = IIf(wb.Name = ThisWorkbook.Name, wb.Name & " (this workbook)", wb.Name)
.Style = msoButtonCaption
.OnAction = "wbActivayte"
End With
Next i
End Sub

and put this one in its place:

Private Sub RightKlickMenuMayker()
Dim objBar As Object, objBtn As Object, wb As Workbook
Run "RightKlickMenuReeset"
Set objBar = Application.CommandBars.Add("wbNavigator", msoBarPopup)
For Each wb In Workbooks
Set objBtn = objBar.Controls.Add
With objBtn
.Caption = IIf(wb.Name = ThisWorkbook.Name, wb.Name & " (this workbook)", wb.Name)
.Style = msoButtonCaption
.OnAction = "wbActivayte"
End With
Next wb
End Sub

Then save the workbook and open a few other workbooks, go back to that subject workbook, right click on any worksheet cell, and it should be OK. Definitley let me know if you run into problems.

Hi Tom,
I have a similar question to the original poster. I want to list the visible worksheets in a workbook using a listbox and combobox. The listbox populates with the sheet names based on the workbook name selected from the combobox:

The code I have used is this:

[vba]
Dim WB as Workbook
Dim WS as Worksheet

For Each WB in Workbooks
If Windows(WB.Name).Visible Then
For Each WS in Worksheets
If WS.Visible = xlSheetVisible Then
ListBox1.AddItem WS.Name
End If
Next WS
ComboBox1.AddItem WB.Name
Next WB

End Sub
[/vba]

When I run this code, the listbox is initalized with the worksheet names of the open workbook twice. When I select another workbook from the combobox, the listbox output stays the same. I was wondering if anyone had any insight into this matter.

Thanks

 

[Martinusgny]

This question came up earlier today. This macro would go into the first workbook, which allow you to find out the name of, and activate, the "other workbook".

Tom,
I know this is a real old post. I pasted the VBA code into the VBA editor. The problem I have is that it just comes up with the name of the workbook I'm using not the other which I want to get to. I would rather have a combobox which would allow me to select the workbook I want to go to but I can deal with just going to the other one that's open.

I want to be able to use this function in the middle of a macro to go to the other open workbook and paste information automatically.

Thank you!

 

[borolo222]

Excelent Macro... very useful. thank you Tom. excactly what i was looking for. saved me one days work writing code and testing. I changed the wbActivayte sub to pass info to the other workbook. sweeet.

 

[Tom Urtis]

Thanks so much for your nice comment borolo...much appreciated.

 

[Hallaa]

What do I think, you ask. I think I should have tested that more carefully.

Please substitute this macro, just take it out back and shoot it:

Private Sub RightKlickMenuMayker()
Dim objBar As Object, objBtn As Object, i As Integer
Run "RightKlickMenuReeset"
Set objBar = Application.CommandBars.Add("wbNavigator", msoBarPopup)
For i = 1 To Workbooks.Count
Set objBtn = objBar.Controls.Add
With objBtn
.Caption = IIf(wb.Name = ThisWorkbook.Name, wb.Name & " (this workbook)", wb.Name)
.Style = msoButtonCaption
.OnAction = "wbActivayte"
End With
Next i
End Sub

and put this one in its place:

Private Sub RightKlickMenuMayker()
Dim objBar As Object, objBtn As Object, wb As Workbook
Run "RightKlickMenuReeset"
Set objBar = Application.CommandBars.Add("wbNavigator", msoBarPopup)
For Each wb In Workbooks
Set objBtn = objBar.Controls.Add
With objBtn
.Caption = IIf(wb.Name = ThisWorkbook.Name, wb.Name & " (this workbook)", wb.Name)
.Style = msoButtonCaption
.OnAction = "wbActivayte"
End With
Next wb
End Sub

Then save the workbook and open a few other workbooks, go back to that subject workbook, right click on any worksheet cell, and it should be OK. Definitley let me know if you run into problems.

I do appreciate the code you have written Tom and find it very useful, but I get an error....

Run-time error '1004'
Cannot run the macro "RightKlickMenuReeset" The macro may not be available in this workbook or all macros may be disabled.

Any idea why this is causing an error. I am using Excel 2007. Thanks a bunch!

 

[Tom Urtis]

Just guessing because there are many possibilities as to why you are seeing that error. Some ideas:

• Are your security settings set to enable macros.
• Is your workbook in 2007 (I see you are using that version) saved with the extension .xlsm.
• Not uncommonly, error 1004 occurs when a worksheet is protected. Is your sheet protected or is the workbook protected.
• Did you place that RightKlickManyMayker macro in a standard module, not in a worksheet module and not in the workbook module, but a standard module such as macros and UDFs are housed.

The code worked when I tested it, so why you are seeing the error is probably due to something simple, such as the above 4 possibilities or maybe something else you might identify that is the reason. Take another look at your workbook, and please post back after that with whatever you have found that is the problem, or if you still have not found the cause. There is always the possibility that I am missing something in my code that you have come across which I did not anticipate, and that kind of information would be very useful for me to know about, in case I need to modify the code I posted.

Thanks for your comments.

 

[Hallaa]

Just guessing because there are many possibilities as to why you are seeing that error. Some ideas:

• Are your security settings set to enable macros.
• Is your workbook in 2007 (I see you are using that version) saved with the extension .xlsm.
• Not uncommonly, error 1004 occurs when a worksheet is protected. Is your sheet protected or is the workbook protected.
• Did you place that RightKlickManyMayker macro in a standard module, not in a worksheet module and not in the workbook module, but a standard module such as macros and UDFs are housed.

The code worked when I tested it, so why you are seeing the error is probably due to something simple, such as the above 4 possibilities or maybe something else you might identify that is the reason. Take another look at your workbook, and please post back after that with whatever you have found that is the problem, or if you still have not found the cause. There is always the possibility that I am missing something in my code that you have come across which I did not anticipate, and that kind of information would be very useful for me to know about, in case I need to modify the code I posted.

Thanks for your comments.

Hi Tom, I have all of the above correct as far as I can see. When debugging Excel referenced

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "RightKlickMenuReeset"
End Sub

and highlighted the Run "RightKlickMenuReeset" parse of the code from the thisworkbook step 7 from your initial instructions. I am still relatively new to the programming world, but this example intrigues me! Thanks for your help!

 

[Tom Urtis]

Something is going on, maybe it's the full moon. If you want, go ahead and send me your workbook so I can see what's happening. Phonetically, my email address is

tom
at
atlaspm
dot
com

 

[Tom Urtis]

Received your workbook. You never placed the RightKlickMenuReeset macro in a standard module. Here it is again, same as was posted in post #7 of this thread.


Private Sub RightKlickMenuReeset()
On Error Resume Next
Application.CommandBars("wbNavigator").Delete
Err.Clear
End Sub

 

[Hallaa]

Thanks for your patience as I am learning here! Oh and the benefit of doubt! Would you mind sending me the completed workbook back, so I can visually see where I goofed it up? You're a great help and resource for the learning and experienced community!