Auto opening of Validation list

I have a simple data validation rule applied to a cell which has the
validation set as List and references approx 50 cells.
What I would like to occur is that, when the sheet is selected, the
drop-down list is revealed with the mouse hovering over the down arrow of
the cell.
Although there's no problem selecting the cell when the sheet is selected, I
cannot get a macro to record the selecting of the cell dropdown list and
cannot find a way to do it.  It may not be possible for this to happen at
all, but I would appreciate anyone's assistance if such a procedure can be
done.

Rob


0
Joe5607 (20)
5/25/2004 6:32:32 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
594 Views

Similar Articles

[PageSpeed] 4

Hi
AFAIK this is not possible

-- 
Regards
Frank Kabel
Frankfurt, Germany
0
frank.kabel (11126)
5/25/2004 6:36:46 AM
Thanks for your prompt reply Frank.  I thought that would have been the case
but you and some others in this newsgroup do come up with what the rest
think are impossibilities, so I needed to ask.
Rob

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:uC%23xMKiQEHA.3748@TK2MSFTNGP09.phx.gbl...
> Hi
> AFAIK this is not possible
>
> -- 
> Regards
> Frank Kabel
> Frankfurt, Germany


0
Joe5607 (20)
5/25/2004 6:42:59 AM
Joe

This code will open the drop-down box.

Sub test()
Range("A1").Select
SendKeys "%{UP}"
'sends ALT + Up Arrow
End Sub

Does NOT position the Mouse pointer over the scroll bar of the drop-down.

Gord Dibben Excel MVP

On Tue, 25 May 2004 16:02:32 +0930, "Joe" <Joe@Joes.com> wrote:

>I have a simple data validation rule applied to a cell which has the
>validation set as List and references approx 50 cells.
>What I would like to occur is that, when the sheet is selected, the
>drop-down list is revealed with the mouse hovering over the down arrow of
>the cell.
>Although there's no problem selecting the cell when the sheet is selected, I
>cannot get a macro to record the selecting of the cell dropdown list and
>cannot find a way to do it.  It may not be possible for this to happen at
>all, but I would appreciate anyone's assistance if such a procedure can be
>done.
>
>Rob
>

0
Gord
5/25/2004 5:15:55 PM
And stealing from Gord....

Right click on the worksheet tab that should have this behavior.  Then select
view code.  Paste this version of Gord's macro into the code window:

Option Explicit
Private Sub Worksheet_Activate()
    Application.EnableEvents = False
    Me.Range("A1").Select
    Application.EnableEvents = True
    SendKeys "%{UP}"  'sends ALT + Up Arrow
End Sub



Whenever you click to another sheet, then come back, the macro will run.

Joe wrote:
> 
> I have a simple data validation rule applied to a cell which has the
> validation set as List and references approx 50 cells.
> What I would like to occur is that, when the sheet is selected, the
> drop-down list is revealed with the mouse hovering over the down arrow of
> the cell.
> Although there's no problem selecting the cell when the sheet is selected, I
> cannot get a macro to record the selecting of the cell dropdown list and
> cannot find a way to do it.  It may not be possible for this to happen at
> all, but I would appreciate anyone's assistance if such a procedure can be
> done.
> 
> Rob

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/25/2004 11:28:36 PM
And when you think it can't be done.....then presto!!

Thanks for the steal, Dave.
And thankyou Gord!

Rob

"Dave Peterson" <ec35720@msn.com> wrote in message
news:40B3D6A4.9DBA07F1@msn.com...
> And stealing from Gord....
>
> Right click on the worksheet tab that should have this behavior.  Then
select
> view code.  Paste this version of Gord's macro into the code window:
>
> Option Explicit
> Private Sub Worksheet_Activate()
>     Application.EnableEvents = False
>     Me.Range("A1").Select
>     Application.EnableEvents = True
>     SendKeys "%{UP}"  'sends ALT + Up Arrow
> End Sub
>
>
>
> Whenever you click to another sheet, then come back, the macro will run.
>
> Joe wrote:
> >
> > I have a simple data validation rule applied to a cell which has the
> > validation set as List and references approx 50 cells.
> > What I would like to occur is that, when the sheet is selected, the
> > drop-down list is revealed with the mouse hovering over the down arrow
of
> > the cell.
> > Although there's no problem selecting the cell when the sheet is
selected, I
> > cannot get a macro to record the selecting of the cell dropdown list and
> > cannot find a way to do it.  It may not be possible for this to happen
at
> > all, but I would appreciate anyone's assistance if such a procedure can
be
> > done.
> >
> > Rob
>
> -- 
>
> Dave Peterson
> ec35720@msn.com


0
Joe5607 (20)
5/27/2004 1:07:04 AM
Gord, thanks very much!
I already answered to Dave P in another message.  Don't know how this got
outa sync.
PS. Having the mouse or curser hover over the arrow was not that
important.....would have been a nice touch if it was possible.
Rob

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:ngv6b053530mknrir4b5fcqm7rob5dlt5f@4ax.com...
> Joe
>
> This code will open the drop-down box.
>
> Sub test()
> Range("A1").Select
> SendKeys "%{UP}"
> 'sends ALT + Up Arrow
> End Sub
>
> Does NOT position the Mouse pointer over the scroll bar of the drop-down.
>
> Gord Dibben Excel MVP
>
> On Tue, 25 May 2004 16:02:32 +0930, "Joe" <Joe@Joes.com> wrote:
>
> >I have a simple data validation rule applied to a cell which has the
> >validation set as List and references approx 50 cells.
> >What I would like to occur is that, when the sheet is selected, the
> >drop-down list is revealed with the mouse hovering over the down arrow of
> >the cell.
> >Although there's no problem selecting the cell when the sheet is
selected, I
> >cannot get a macro to record the selecting of the cell dropdown list and
> >cannot find a way to do it.  It may not be possible for this to happen at
> >all, but I would appreciate anyone's assistance if such a procedure can
be
> >done.
> >
> >Rob
> >
>


0
Joe5607 (20)
5/29/2004 4:41:35 AM
Reply:

Similar Artilces:

cannot open or reply to a message or new message
Have just upgraded to windows 7 and now when we are in outlook 2007 we recieve mail but cannot reply, open or forward mail. It is open in the task bar but will not open up full screen. Any help here would be grateful. Close Outlook. Open it in safe mode. Start > Start Search > outlook.exe /safe (please note the space between outlook.exe and /safe) Does this behaviour continue? "Cazz" <Cazz@discussions.microsoft.com> wrote in message news:355C8102-A769-4B6C-886B-FC8FE61D6495@microsoft.com... > Have just upgraded to windows 7 and now when we are in ...

Auto Filter problem
I am using the auto filter for a particular column to sort out differen medical programs. When I click the drop down and click a program, i doesnt show me ALL the lines that say this particular program. Why is this? -- Message posted from http://www.ExcelForum.com Hi maybe some hidden characters in these lines 8e.g. additional space characters, etc.) -- Regards Frank Kabel Frankfurt, Germany "jkb724 >" <<jkb724.19rasx@excelforum-nospam.com> schrieb im Newsbeitrag news:jkb724.19rasx@excelforum-nospam.com... > I am using the auto filter for a particular column to so...

Subclassing List Control
I want to place a list control object in my dialog window; however I have some requirements on how the control is to appear. For example, the control is to appear with a black background, certain columns in the control are to be painted with specific foreground colors and a specific font is to be used. Can someone post an example on how I can alter the control's background color and how to paint specific columns using different foreground colors and fonts? Thanks Chris Take a look at www.codeguru.com for samples. List controls supports NM_CUSTOMDRAW which allows you to control each...

Auto-Editing
Word 2003 has a very annoying default. It assumes too much! If I reformat a few words in a document, it changes the entire document to that formatting. Grr-rrr-r! I have to constantly press undo to get what I want. I know there's a feature that's causing this annoyance but I can't find it to turn it off! Can anyone help, please? See http://word.mvps.org/faqs/formatting/wholedocumentreformatted.htm. -- Stefan Blom Microsoft Word MVP "Connie Martin" <ConnieMartin@discussions.microsoft.com> wrote in message news:CD959D82-F81B-4A9D-993E-73...

outlook2003 forwarding (always attaches rather than open)
I have a friend that has the same Outlook 2003 that I do. But when they forward, the email client always attaches the message as an attachment so they can't clean it up prior to forwarding. I can't find the option to make theirs work like mine (which doesn't attach forwarded messages). Thanks!!!! "joe" <bry333@bellsouth.net> wrote in message news:xFc4k.4086$LL4.1727@bignews7.bellsouth.net... >I have a friend that has the same Outlook 2003 that I do. > But when they forward, the email client always attaches the > message as an attachment so they can&#...

SUBTOTAL A LIST
Hi - Is there a way to subtotal a list...once I create a list the subtotal feature is not available. I also fail to see the difference between a list and the auto filter function, can someone explain? Thanks, Jim Jim, Here is the technique that I use. Insert some lines above the headers in your spreadsheet. Example: Row 1 Row 2 Row 3 =SUBTOTAL(9,D6:D1000) Row 4 Row 5 First Name Last Name Sales Region Sales ($) Etc Lets say you want to filter just the midwest sales ...

Auto-Calculate
I have a spreadsheet that utilize the stock quote add-in from MSN. On a daily basis I refresh the add-in functions for the Highs, Lows, Last, Volume, etc. On occasion the spreadsheet reverts to manual caculation settings, and I have to press the F9 even after I updated the quotes. I've always preferred the any spreadsheets I open, blank or otherwise, the automatic setting selected for calculations. I consider this an annoyance. Has anyone experienced this glitch? Is there a fix for it? Ron_D Ron Excel takes the calculation mode from the first workbook that opens in a session. Sub...

auto copy self
How do I arrange for Outlook to automatically copy myself on every new, reply or forward email? If you mean keep a copy of all sent, see settings under Tools/Options/Email Options and Advanced Options David "rich" <steamuk@aol.com> wrote in message news:055c01c3664f$dadecd30$a001280a@phx.gbl... > How do I arrange for Outlook to automatically copy myself > on every new, reply or forward email? ...

When opening MS Outlook 2002 a window opens asking for network server?
Office XP Professional When I open MS Outlook a window opens up that is titled Microsoft LDAP Directory It asks for a server name, user name etc. This is a stand alone computer. Not on a network. Is there anyway of stopping this window from opening up each time I start Outlook? Thank You for your time Barry Barry, Your Outlook is configured to use an LDAP server for some reason. To remove the settings for an LDAP server, goto: 1) Tools 2) Email-Accounts Once in the Email-Accts window you will see four radio buttons(2 for E-mail and 2 for Directory) Click on the button next ...

Auto correct or Auto complete
We are all familiar witht he auto correct or auto complete functions found in MS applications. Of course that means it is available in Access also. My question is this ... can this function be replicated within the DB, so that the set of auto correct items are part of the package rather than something that would have to be set up on each individual desktop system? It's use, for me, would primarily be within a memo field where medical abreviations, used as a shortcut, would convert to the true meaning as the user types them in. Ex: "prn" without quotes would change to. &qu...

Auto Scale Chart Macro -- Any improvements please?
This is the best way I've found to correctly scale a chart -- the only way I found to exclude a chart interpolating with #NA cells was to delete those cells containing #NA, and run the chart. This takes a long time on my computer. Wondering if there is a better way. This macro is directly taken from 2 excel mvps and modified; i believe a mehta and ron bovarty, but could be wrong. Thanks for taking a look. Sub AutoScaleYAxes() Dim ValuesArray(), SeriesValues As Variant Dim Ctr As Integer, TotCtr As Integer Application.Run "Extend_Stock_Data" Applic...

Opening another user mailbox
I am trying to setup outlook to open another user mailbox in addition to my own. In the Outlook I do this: Tools->Options->Mail Setup->Email accounts->View or Change->Change->More settings->Advanced->Open these additional mailboxes->Add The problem is if the user is not in the GAL - I cannot perform this operation. As soon as I uncheck "Hide from GAL" in the user properties I can open his mailbox. I need to hide user from GAL but still get access to his mailbox. How can I do it? ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet...

data missing in charts original file 97-2003 opened 2007 and then.
I have a file that is created in 97-2003, after it is opened in excel 2007 and then saved back to 97-2003 version when it is reopened the charts that are included are not showing any of the text data or information that was added to the charts. Anyone else have this problem or know how to fix it other than manually updating the charts. Did you add the information with XL2007? If so maybe you used components that are available only in that version. Did you get a warning that saving in the older format will lead to loss of information? If you don't remember you may want to go thr...

Cannot open files.
I am having trouble opening files after saving them. I keep getting the message "cannot be opened because there are problems with the contents. Details says "Unspecified error" followed with the location in the "document.xml" file. ...

OWA auto login
I am trying to use the credentials from the forms authentication login to automaticaly log in to Outlook Web. I redirect to a URL with this format: http://UserName:Password@mailserver/exchange I have seen this example in other posts but this does not work for me. I still get the Outlook Windows Login prompt. Perry Perecli Manole wrote: > I am trying to use the credentials from the forms authentication > login to automaticaly log in to Outlook Web. I redirect to a URL with > this format: http://UserName:Password@mailserver/exchange > I have seen this example in other posts but t...

eConnect
I am developing an interface screen that will take a CSV file full of sales order information and load it into GP using eConnect. My app currently loads the orders on screen. The user can review and then click a button to send them to GP. I'd like to provide a "Validate" button so that they could find any errors (such as invalid terms code) without any orders getting updated. Is it possible to call eConnect to validate the XML without having it update anything? How? Chris, eConnect does not allow for Validation without updating. You could modify the Post proced...

Auto look through subfolders
Please help. I can write a macro that will extract data from all excel files in a specific folder. What I need to be able to do is write a macro that will automatically extract data from excel files within folders and sub folders and sub sub folders etc For example the data is all held on the following directory: H:\development forms\2006 cost sheets\ And within that directory by customer - for example Tesco, Boots, Asda etc. These customers are then subdivided into product ranges - eg 1,2,3,4 etc and some futher subdivided by national or exclusive. All the excel files are in exactly th...

Any way to get rid of an auto-suggestion in the login dialog box, for Exchange/OL using RPC over HTTP?
A client was using RPC over HTTP to two different Exchange servers (OL prompting for profile on startup). Both profiles were set up to prompt for credentials upon connection to Exchange. There's now only one Outlook profile needed; the other is gone and OL isn't prompting for the profile selection any longer, which is fine. However, Outlook is remembering domain1\user and domain2\user in the login dialog box, and I'd love to be able to get rid of the no-longer-valid credential dropdown suggestion. (The computer is not a member of a domain and hence always needs to be prom...

Data validation with a cell value linked to the same cell.
I Think that my post name is very confusing but I'll explain. I�m doing a data validation in cell f1 usig the data validation option the value entered in cell f1 need to be equal or greatter than th value in cell b2 and cell b2 is calculated with the formula =a2+c4-f1. What I'm trying to do is to prevent to enter a value in cell f1 tha I'll make cell b2 negative. But its not working, any suggetsions??? Thanks. Rodolf -- Message posted from http://www.ExcelForum.com Change your CF criterion to Formula is = F1 <= (A2+C4) In article <rodolform.161w07@excelforum-n...

An auto reply to an auto reply!
Had the funniest situation yesterday. Somebody emailed a public folder we have set up. On there we have a rule to send a reply saying thanks for your email -someone will be with you shortly (we have to have this message on - it is for some website that recently went live). Now that auto reply went back to the guy who emailed - who must have sent it from a public folder himself (as he forwarded the original email from a public folder). The guy received our auto reply - and promptly his email system sent an auto reply to us. As is the way it is set up - our email system sent him an auto ...

2 workbooks open
Hi - A co-worker created a spreadsheet - over the course of a few weeks, several tabs were added. Recently, when I received the spreadsheet via email, 2 identical workbooks opened. The 1st workbook was called 'Data (1)' and the 2nd was called 'Data (2)'. There wasn't a macro on either spreadsheet - I'm at a loss to why 2 identical workbooks opened. Any ideas or suggestions are appreciated. Regards, Close the second instance of the Workbook and then Save the file. Meg wrote: > Hi - > > A co-worker created a spreadsheet - over the cou...

Auto date
I am trying to create a field in a form that if anything is changed on it, it will automatically update the date to current date. Is this possible? Sojaminc wrote: >I am trying to create a field in a form that if anything is changed on it, it >will automatically update the date to current date. Is this possible? Make sure the last changed date/time field is in the form's record source table/query. Then use the form's BeforeUpdate event: Me.lastchanged = Now -- Marsh MVP [MS Access] Hi - You can do this in the On Dirty event of the form, which fires as soon as you m...

doc attachments won't open
I have Word 2007 and when someone sends a *.doc attachment it won't open. If you copy it to the desktop or somewhere else, it opens fine. I did check default programs and Word is set to open .doc files Opening an attachment without saving it first is asking for trouble. Any editing you do to it won't be saved, since it's just sitting in a temporary folder that gets deleted when you exit Word. On Nov 21, 6:32=A0pm, tomg88 <tom...@discussions.microsoft.com> wrote: > I have Word 2007 and when someone sends a =A0*.doc attachment it won't op= en. > If you...

files can't be open in excel2000
hi, I have some excel files which works fine on office 97 with windows nt. but when I try to open them in office 2000 with windows 2000 it just gives me a blank page. this file is with macros. pls help. thanks Hi does this only happen if you try to open this file from the Windows Explorer?. Have you tried to open this file from the Excel File menu? -- Regards Frank Kabel Frankfurt, Germany "shah" <rshahruk@csc.com> schrieb im Newsbeitrag news:9b6301c486bd$a14ac7b0$a501280a@phx.gbl... > hi, > I have some excel files which works fine on office 97 > with wi...

Why do my excel documents not open from the desktop?
Why do my excel documents not open from the desktop? I can only open documents when I am already in Excel. I assume some setting is off. Where should I look? Thanks Try the usual fix(es) for this problem. Tools>Options>General uncheck "Ignore other Applications" Exit Excel and try again If this doesn't work try to re-register Excel Close Excel first and On the Windows Taskbar 1) Start>Run "excel.exe /unregserver"(no quotes)>OK. 2) Start>Run "excel.exe /regserver"(no quotes)>OK. See the space between exe and /regserver You might have ...