MrExcel Publishing
Your One Stop for Excel Tips & Solutions

activate/deactivate open workbooks?


Posted by alfredo sanchez on April 15, 2001 10:26 PM

Hi!
Somebody can tell me if there's a way to activate/ deactivate open workbooks from macro-button?.Thank you.


Posted by Dave Hawley on April 15, 2001 10:46 PM


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

Posted by alfredo sanchez on April 16, 2001 7:46 AM


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.

Posted by Dave Hawley on April 16, 2001 6:47 PM

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

Posted by alfredo sanchez on April 17, 2001 10:59 AM

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 ?.

Posted by Dave Hawley on April 17, 2001 3:30 PM


Hi alfredo

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

Dave
OzGrid Business Applications

Posted by alfredo sanchez on April 17, 2001 4:30 PM

code as far; the two sections

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

Posted by Dave Hawley on April 18, 2001 9:31 PM

Re: code as far; the two sections

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