Code equivalent to Alt + Tab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a programmatical equivalent to Alt + Tab?
I need to switch between 2 open workbooks; 1 has the macro and the other has
all the data, no macros but has a huge variation in file names. It does have
a fixed format (the first sheet is named "Summary", cell A1 is the title
"ClientName") and users need only have the 2 open during execution.

Any clues gratefully received.
 
Why would the users have to switch back to the workbook with the code?

I would think that they could use Tools|macro|macros (or alt-f8) to run those
macros--and you could even save that workbook with the macros in a hidden state,
so the users don't even know it's there????

======
An alternative???

If you want to give the users a way to access the macros:

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

I'd even save this macro workbook as an addin (.xla) and then it would be
invisible to the user--except for the toolbar or menu.
 
Thanks for the response Dave.
Sorry I didn't explain, the workbook with the macros has some look up tables
which are used in the users workbook to calculate some of the results. The
macro switches between them to pick up different sections.
Of course, when I'm recording it, both workbook names are known.

Thanks again. Anymore ideas?
 
If you're trying to make entering formulas easier, then you're out of luck.
Macros that do anything won't be running when the user is editing the cell.

But you could use something like:

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim myWindow As Window
Dim KnownWkbkName As String
Dim FoundIt As Boolean

KnownWkbkName = "Book2.xls"

If SheetExists("summary", ActiveWorkbook) Then
'your in their workbook, so activate the otherone
Workbooks(KnownWkbkName).Activate
Else
FoundIt = False
For Each wkbk In Workbooks
If SheetExists("summary", wkbk) Then
For Each myWindow In wkbk.Windows
If myWindow.Visible = True Then
FoundIt = True
Exit For
End If
Next myWindow
End If
If FoundIt = True Then
Exit For
End If
Next wkbk

If FoundIt = True Then
myWindow.Activate
Else
MsgBox "No other workbook found!"
End If
End If

End Sub
Function SheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
SheetExists = CBool(Len(WB.Sheets(SheetName).Name) > 0)
End Function
 
Ps. If you just want to cycle through the open windows, you can use a macro
with a line like:

ActiveWindow.ActivateNext

(Slightly less code <vbg>)

<<snipped>>
 
Bravo Mr P. You are my Number 1 Star.
Thank you so much (and far easier than all that mularky earlier!)
 
I don't know. All that malarky did some nice checking.

But glad you have something you can use.
 
My very sincere apologies Dave. I didn't mean to disrespect your elegant code
or your efforts on my behalf. It's just that, being an ignorant sort, I
couldn't understand it at first and your shorter option worked first time.
Now that I've studied your first option, I can see it has some robust
features.

Apologies again and, once again, many many thanks for responding.
Regards
Jimbob
 
No apology necessary. I think it just depends on how much "clutter" or extra
stuff you want.


My very sincere apologies Dave. I didn't mean to disrespect your elegant code
or your efforts on my behalf. It's just that, being an ignorant sort, I
couldn't understand it at first and your shorter option worked first time.
Now that I've studied your first option, I can see it has some robust
features.

Apologies again and, once again, many many thanks for responding.
Regards
Jimbob
Click to expand...
 
Back
Top