Navigating between active sheets using the Window dropdown.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

etradeguru@hotmail.com - 28 Aug 2007 11:04 GMT

I have searched the Groups but cannot find a means by which I can call
the Window dropdown to allow a user to navigate between open excel
files to select the correct sheet for the macro to run on.

Basically, I want to be able to ensure that the user already has the
necessary file open and that it has focus, and to apply a means by
which they can go to the open file so that the macro runs on the
correct file rather than any other file that might be open.

so far i have the following: -

Sub versionmastermsb1()

Dim msb As Integer
msb = MsgBox("Is today's Log file open?", vbYesNo)

If msb = 6 Then

msb = MsgBox("Please make sure Log file has active focus")

ElseIf msb = 7 Then

Application.FindFile

End If

End Sub

When the condition is Yes, I need the user to have the file active.
How do I ensure this before the macro runs on?

Thanks

Mark

Reply to this Message

Jason - 28 Aug 2007 21:59 GMT

Could you make a user form that allows the user to select the correct
file if it's open or open the file open dialog if it isn't?

On Aug 28, 5:04 am, etradeg...@hotmail.com wrote:

> I have searched the Groups but cannot find a means by which I can call
> the Window dropdown to allow a user to navigate between open excel
[quoted text clipped - 30 lines]
>
> Mark

Reply to this Message

etradeguru@hotmail.com - 29 Aug 2007 13:06 GMT

> Could you make a user form that allows the user to select the correct
> file if it's open or open the file open dialog if it isn't?
[quoted text clipped - 37 lines]
>
> - Show quoted text -

That is an option.
My point is that VB should include the ability to allow you to
navigate around the available controls rather than having to reinvent
the wheel.
How hard can it be?
It also means more time spent creating a form for what should be a
default action.
Mark

Reply to this Message

Jason - 29 Aug 2007 18:18 GMT

I see. So do you want the _Window menu option to expand if the user
responds "Yes" to the message box that asks "Is today's log file
open?", or do you want something similar to the open file dialog box
to appear?

On Aug 29, 7:06 am, etradeg...@hotmail.com wrote:

> > Could you make a user form that allows the user to select the correct
> > file if it's open or open the file open dialog if it isn't?
[quoted text clipped - 46 lines]
> default action.
> Mark

Reply to this Message

etradeguru@hotmail.com - 31 Aug 2007 11:05 GMT

> I see. So do you want the _Window menu option to expand if the user
> responds "Yes" to the message box that asks "Is today's log file
[quoted text clipped - 55 lines]
>
> - Show quoted text -

Jason,
I have got around the issue using

Workbooks(2).Activate

which assumes that Pesronal.xls =1 and that the next workbook is the
one you want..

That is not really useful if the order is changed and the workbook you
want is not number 2.
What I want is what you refer to, something akin to the open file
dialog, or just being able to activate the Window dropdown list and
allow the user to select from the list. The equivalent to and [alt]+
[W] key combo.
If you record a macro using the key combo it doesnt register the key
strokes, just the result hardcoding the filename, which I dont want
because the filename changes everyday.

Does that explain?

Mark

Reply to this Message

Jason - 07 Sep 2007 15:01 GMT

Mark,
I apologize for the delay. Without making a new form, I think the
easiest way to do this is to use the sendkeys statement. The line in
your vba code would like like this:
SendKeys "%w"

The "%" represents the [alt] key, so this should just expand the
Window dropdown from the main toolbar.

Hope this helps.

On Aug 31, 5:05 am, etradeg...@hotmail.com wrote:

> > I see. So do you want the _Window menu option to expand if the user
> > responds "Yes" to the message box that asks "Is today's log file
[quoted text clipped - 77 lines]
>
> Mark

Reply to this Message