How to select option in pop-up list????

[23izkool]

================= VBA CODE ==================
Sub RightClick()
Dim wshshell As Object
Dim i as Integer

Set wshshell = VBA.CreateObject("WScript.Shell")
wshshell.SendKeys ("+{F10}"), True

End Sub

================= VBA CODE ==================

Upon executing the above macro, a drop-down list appears (so the macro actually simulates a right-click on the mouse) --> What I am trying to do is to get the macro to choose 'Copy' in the list. I am able to do so by typing 'C'. However, when I tried to get the macro to do it by adding [SendKeys "C", True] in the macro, the letter 'C' is immediately fed into the excel sheet and appears as an entry 'C' in 1 of the cells in the sheet.

Thinking that it may be because SendKeys execute too fast such that the typing of C by sendkeys go faster than the drop-down list can detect, I added 'Application.wait Now +TimeValue ("00:00:01"), so the edited macro looks like below:

=================Edited VBA CODE ==================
Sub RightClick()
Dim wshshell As Object
Dim i as Integer

Set wshshell = VBA.CreateObject("WScript.Shell")
wshshell.SendKeys ("+{F10}"), True

' New Edit
Application.Wait Now + TimeValue("00:00:01")
SendKeys "C", True

End Sub

================= Edited VBA CODE ==================

However, this causes the macro to simply hang --> the drop-down list does not even show up. I have to end the macro to stop it.

Hope that some VBA gurus can help me with this!!!!!


SideNote:
I am trying to do the above because I ultimately intend to use this mouse --> rightclick --> select option in dropdown list macro on another independent software which can only copy entries by this method of mouseclicking (i.e. no option to choose 'Edit'--> Copy & Shortcuts like 'Ctrl-C' etc cannot be detected)

Sorry if writing too much --> cos hope to provide as much details as possible to get some help!!! Thanks a lot!!! Appreciate it!!!

[Jan Karel Pieterse]

does adding {DOWN} after the {F10} move the cursor down in the menu? If so, you might use that?

[23izkool]

Hi Jan,

Tks a lot! U r a genius...i got it to copy (i think) by adding {down}

Can i understand what adding {down} does?
Cos I figure it means moving down the pop-up menu but then I tried to move more entries down the menu by writing

wshshell.SendKeys ("+{F10}{down}{down}{down}{down}{down}{down}"), True

to move down 6 entries but it still only seems to be copying

What should I write to move down the drop down list?

[Jan Karel Pieterse]

{down} simply means hit the down arrow key. I was hoping it would move the highlighted menuchoice one down the list. So if Copy is the fifth entry in the menu, you'd need:
{DOWN 4}

Then {Enter} to choose that menu entry:
wshshell.SendKeys("+{F10}{down 4}{Enter}"), True

Why are you using wshshell in front of the sendkeys? VBA has its own sendkeys command, so you should be able to use:
SendKeys "+{F10}{down 4}{Enter}"

[23izkool]

Yes Jan, you are so absolutely right!!!

I have managed to get the macro to right-click & copy,
In order to test that it works, I did a sendkeys to tab and paste into another cell.

O god, this is really a great informative place with such efficient replies from gurus like you! haha....thanks so much!

I will try this out and adapt to the software tomorrow & hope it works...then try to share it with everyone here...Thanks so much again Jan!!!

Will keep everyone posted about the result to share the knowledge!!!

[23izkool]

Hi Jan,

Yes it works as well! VBA was able to interact with the other program!

But just checking - I am currently using SendKeys to simulate Alt-Tab to switch between that other program and excel.

The problems with this are:
(1) Sometimes even though I start from excel then Alt-Tab --> the macro is supposed to then right-click & copy, then Alt-Tab. But this last step of switching is inconsistent. It usually works when I run it for the first time but sometimes if I repeat it, it just stays at the screen of the other program. I have heard of some peculiar behavior / inconsistencies in SendKeys --> Is this the issue?

(2) Is there a way to use SendKeys to switch specifically to the other program? Because if I have more than 2 open (i.e. some programs other than the excel + my program), SendKeys sometimes switch wrongly (by Alt-Tab) to the wrong program, like maybe my email when it's open.

Is there a way to switch specifically to that program I want?

Hope someone can throw some light on this!! Thanks!!

[Jan Karel Pieterse]

You can use AppActivate from VBA to activate another window and then the SetActiveWindow API to re-activate Excel:

Declare Function FindWindow32 Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function SetActiveWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
Sub Demo()
    Dim hwndXL As Long
    AppActivate "Text in window Titlebar of your application"
    'Sendkeys stuff goes here
    hwndXL = FindWindow32("XLMAIN", Application.Caption)
    SetActiveWindow hwndXL
End Sub