Navigating between active sheets using the Window dropdown.

  • Thread starter Thread starter etradeguru
  • Start date Start date
E

etradeguru

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












- Show quoted text -
Click to expand...

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




- Show quoted text -
Click to expand...

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

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?
Click to expand...
On Aug 29, 7:06 am, (e-mail address removed) wrote:
Click to expand...
- Show quoted text -
Click to expand...

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
Click to expand...
 
Back
Top