Re: Navigating between active sheets using the Window dropdown. by Jason
Jason
Fri Sep 07 07:01:22 PDT 2007
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:
> On 29 Aug, 18:18, Jason <skag...@gmail.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
> > 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:
>
> > > On 28 Aug, 21:59, Jason <skag...@gmail.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?
>
> > > > 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
> > > > > 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- Hide quoted text -
>
> > > > - 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- Hide quoted text -
>
> > - 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