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.

You could use Sendkeys which sends keystrokes to another application but it's
not very reliable.

the codes for sendkeys are here.

http://msdn2.microsoft.com/en-us/library/8c6yea83.aspx

Mike

"Jimbob" wrote:

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

Jimbob wrote:
>
> 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.

--

Dave Peterson

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?

"Dave Peterson" wrote:

> 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.
>
> Jimbob wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>

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



Jimbob wrote:
>
> 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?
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > Jimbob wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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>)

Dave Peterson wrote:
>
<<snipped>>
--

Dave Peterson

Bravo Mr P. You are my Number 1 Star.
Thank you so much (and far easier than all that mularky earlier!)

"Dave Peterson" wrote:

> 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>)
>
> Dave Peterson wrote:
> >
> <<snipped>>
> --
>
> Dave Peterson
>

I don't know. All that malarky did some nice checking.

But glad you have something you can use.

Jimbob wrote:
>
> Bravo Mr P. You are my Number 1 Star.
> Thank you so much (and far easier than all that mularky earlier!)
>
> "Dave Peterson" wrote:
>
> > 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>)
> >
> > Dave Peterson wrote:
> > >
> > <<snipped>>
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

"Dave Peterson" wrote:

> I don't know. All that malarky did some nice checking.
>
> But glad you have something you can use.
>
> Jimbob wrote:
> >
> > Bravo Mr P. You are my Number 1 Star.
> > Thank you so much (and far easier than all that mularky earlier!)
> >
> > "Dave Peterson" wrote:
> >
> > > 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>)
> > >
> > > Dave Peterson wrote:
> > > >
> > > <<snipped>>
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

No apology necessary. I think it just depends on how much "clutter" or extra
stuff you want.



Jimbob wrote:
>
> 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
>
> "Dave Peterson" wrote:
>
> > I don't know. All that malarky did some nice checking.
> >
> > But glad you have something you can use.
> >
> > Jimbob wrote:
> > >
> > > Bravo Mr P. You are my Number 1 Star.
> > > Thank you so much (and far easier than all that mularky earlier!)
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 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>)
> > > >
> > > > Dave Peterson wrote:
> > > > >
> > > > <<snipped>>
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson