Archive of Mr Excel Message Board

Hi alfredo

You can Activate an open Workbook like:

Workbooks("Book1.xls").Activate

To deactivate all you would need to do is use:

Workbooks("Book2.xls").Activate

..and Book1.xls would no longer be Active.

Dave

OzGrid Business Applications

Hi Dave!: Thank you for your answer, if you don't mind, I would like to know if it is possible to assign the name of workbook to activate/deactivate via VBA, I've tried but I can't make programatic assign of workbook to act/deact.

not too sure what you mean, but try this:

Dim SThisbookName As String

SThisbookName = ThisWorkbook.Name
Workbooks("Book2.xls").Activate
Windows(SThisbookName).Activate

This will Deactivate the Workbook it is run from the Activate it again.

Dave

OzGrid Business Applications

Dave: maybe if I tell the whole story, you can help me in what i intend to do: Im trying to create a command bar and buttons to change from an open workbook to another, Instead of using the annoying Window/selection menu. As today, I have acomplished two or three tricks, a command bar is created and it shows every open workbook as a button(labeled as workbook's name). The problem is that I can assign each workbook to a button, but one of two thing happens:1. Excel continues to change from book to book endlesly -and adding buttons to infinite or 2.it does create the buttons but they don't work at all. Do you think this is possible ?.

Hi alfredo

I guess almost anthing is possible. what code do you have so far ?

Dave
OzGrid Business Applications

Dave:Thanks for replying, I have these two sets of code, the first keeps jumping from book to book and I have to stop it with Ctrl-Alt-Del; the second behaves better but answers with "unable to locate NAME macro". Hope you can work it out. (NAME is workbook's name)
FIRST SET:
Private Sub CountOpenWorkbooks()
Dim WBooks As Workbook, count As Integer
Dim ThisBook As String, bhoja As Worksheet
Dim cbar1 As Object, nombre As String
Dim menu As CommandBarButton, nsub As String
Hoja4.Columns(6).Clear
'count = 1

' ---->>>Set cbar1 = CommandBars.Add(Name:="Personalizada1", Position:=msoBarBottom)
' ---->>>cbar1.Visible = True

For Each WBooks In Application.Workbooks

nombre = WBooks.Name
'nsub = "(" + nombre + ")"
'Set Menu = Application.CommandBars("archivos").Controls.Add(Type:=msoControlButton, _
'Before:=count)
'With Menu
'.Style = msoButtonCaption
'.Caption = WBooks.Name
'.OnAction = Windows(nombre).Activate
'End With
'count = count + 1
Next WBooks
End Sub (Note I have to put apostrophe to test separate code)

SECOND SET:

Sub maybe()
Dim menu As CommandBarButton, nombre As String
Dim WBooks As Workbook, count As Integer, comas As String
count = 4
comas = Chr(34)
For Each WBooks In Application.Workbooks
Hoja4.Range("f65536").End(xlUp).Offset(1, 0) = WBooks.Name
nombre = WBooks.Name
nombre = "Windows(" + comas + nombre + comas + ").Activate"

Set menu = Application.CommandBars("archivos").Controls.Add(Type:=msoControlButton, _
Before:=count)
With menu
.Style = msoButtonCaption
.Caption = WBooks.Name
.OnAction = nombre
End With
count = count + 1
Next WBooks
End Sub

Hi alfredo

I did post a response somewhere but I cannot find it now ????

Anyway looks like the problem lies with the lines.
.OnAction = Windows(nombre).Activate
and
.OnAction = nombre

When assigning a macro to a button via VBA you must use the name of the macro you wish to assign as a String, eg;

.OnAction = "MyCode"

Would run a macro called "MyCode" that resides in a standard module.

Dave

OzGrid Business Applications