Re: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office 2010 x64
Here it is (I used the new google groups ) -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel mouse test.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office 2010 x64
Im not using the right mouse. If you suggest that I use the right mouse, I'd need to disable the format popup if I am in my target cell. IE If Target.Count = 1 and Target.Column = 34 and Target.Value > 0 And (GetAsyncKeyState(vbKeyLButton) = 1 And &H8000) Then ' my logic End If On Feb 9, 9:25 am, ashish koul wrote: > r u using right button also > > > > > > > > > > On Wed, Feb 9, 2011 at 7:53 PM, ashish koul wrote: > > can you share your workbook > > > On Wed, Feb 9, 2011 at 6:57 PM, rcamarda wrote: > > >> Ashish, > >> 1. Your code worked from your example. > >> 2. in my other tests, I added added the "= 1" I had, and some of my > >> test sheets worked. > >> 3. It didnt work when i put it into my sheet where I needed it, > >> GetAsyncKeyState(vbKeyLButton) = 1 And &H8000 but it didnt work > >> 4. I opened a fresh excel, pasted your code and it worked. > >> 5. My sheet has macro enabled, so I saved your test book as xlsm and > >> your test still works. > >> 6. I can create a new sheet in my excel, and the code works on that > >> sheet. so some sheets it works, others it does not. > >> 7. On the sheet I need to trap the mouse, all code in your test works, > >> except the left mouse. In other sheets it traps everything > >> 8. When I use only > >> MsgBox GetAsyncKeyState(vbKeyLButton) > >> in my Worksheet_SelectionChange, it shows just 0 (zero). > > >> > >> *rips hair out* > >> > > >> Anyone have ideas on how to determine why my sheet doesnt work, but a > >> blank fresh one does? > > >> -- > > >> --- > >> --- > >> Some important links for excel users: > >> 1. Follow us on TWITTER for tips tricks and links : > >>http://twitter.com/exceldailytip > >> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 > >> 3. Excel tutorials athttp://www.excel-macros.blogspot.com > >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > >> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > >> To post to this group, send email to excel-macros@googlegroups.com > > >> <><><><><><><><><><><><><><><><><><><><><><> > >> Like our page on facebook , Just follow below link > >>http://www.facebook.com/discussexcel > > > -- > > *Regards* > > * * > > *Ashish Koul* > > *akoul*.*blogspot*.com <http://akoul.blogspot.com/> > > *akoul*.wordpress.com <http://akoul.wordpress.com/> > > My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> > > > P Before printing, think about the environment. > > -- > *Regards* > * * > *Ashish Koul* > *akoul*.*blogspot*.com <http://akoul.blogspot.com/> > *akoul*.wordpress.com <http://akoul.wordpress.com/> > My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> > > P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office 2010 x64
Ashish, 1. Your code worked from your example. 2. in my other tests, I added added the "= 1" I had, and some of my test sheets worked. 3. It didnt work when i put it into my sheet where I needed it, GetAsyncKeyState(vbKeyLButton) = 1 And &H8000 but it didnt work 4. I opened a fresh excel, pasted your code and it worked. 5. My sheet has macro enabled, so I saved your test book as xlsm and your test still works. 6. I can create a new sheet in my excel, and the code works on that sheet. so some sheets it works, others it does not. 7. On the sheet I need to trap the mouse, all code in your test works, except the left mouse. In other sheets it traps everything 8. When I use only MsgBox GetAsyncKeyState(vbKeyLButton) in my Worksheet_SelectionChange, it shows just 0 (zero). *rips hair out* Anyone have ideas on how to determine why my sheet doesnt work, but a blank fresh one does? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office 2010 x64
Sorry for the rabbit hole...I posted too quickly and once it was sent,
I couldnt retract.
I still can not get it to work.
Latest I tried is this, which always says left click has been pressed
code:
On a module I have: Public Declare Function GetAsyncKeyState Lib
"user32" (ByVal vKey As Long) As Integer
(some postings change the return type and the parameter types)
In my sheet I have:
Sub Worksheet_SelectionChange(ByVal Target As Range)
If (GetAsyncKeyState(vbKeyRButton)) Then
MsgBox "You have clicked the right button!" 'when you click right
button
ElseIf (GetAsyncKeyState(vbKeyLButton) ) Then
MsgBox "You have clicked the left button!" 'when you click left button
End If
End Sub
I checked out:
http://itknowledgeexchange.techtarget.com/beyond-excel/back-to-controlling-the-cursor/
and noticed how the author "cheated" when it came to mouse :)
I've added the &H8000 bit mask, but still nothing on the left mouse
click.
On Feb 9, 3:52 am, ashish koul wrote:
> hi dave
>
> check this link see if
> helpshttp://itknowledgeexchange.techtarget.com/beyond-excel/back-to-contro...
>
> On Wed, Feb 9, 2011 at 2:08 PM, Dave Bonallack
> wrote:
>
>
>
>
>
> > Thanks Ashish,
> > Works great.
> > Regards - Dave.
>
> > --
> > Date: Wed, 9 Feb 2011 10:32:26 +0530
>
> > Subject: Re: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7
> > office 2010 x64
> > From: koul.ash...@gmail.com
> > To: excel-macros@googlegroups.com
>
> > try this
>
> > On Wed, Feb 9, 2011 at 10:27 AM, ashish koul wrote:
>
> > @ dave Try this
>
> > Wed, Feb 9, 2011 at 9:55 AM, Dave Bonallack
> > wrote:
>
> > Hi Ashish,
> > Thanks for sending the file. Excellent. But for some reason, selecting a
> > cell with the left mouse button isn't recognised. I tried adding Rob's code,
> > but it didn't work properly either. It only worked when using the left mouse
> > button to 'OK' a previous message box.
> > Regards - Dave
>
> > --
> > Date: Wed, 9 Feb 2011 09:03:03 +0530
> > Subject: Re: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7
> > office 2010 x64
> > From: koul.ash...@gmail.com
>
> > To: excel-macros@googlegroups.com
>
> > hi dave,
>
> > see if this helps
>
> > On Wed, Feb 9, 2011 at 5:52 AM, Dave Bonallack
> > wrote:
>
> > Hi Rob,
> > I am interested in the trapping of a left mouse click, but couldn't
> > understand it from your posts. Could you please attach a workbook
> > with working code?
> > Regards - Dave.
>
> > > Date: Tue, 8 Feb 2011 05:14:53 -0800
> > > Subject: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7
> > office 2010 x64
> > > From: robert.a.cama...@gmail.com
> > > To: excel-macros@googlegroups.com
>
> > > solved it:
> > > I had to add a bit mask (i think thats what its called).
>
> > > This returned true when I clicked on a cell:
>
> > > If CBool(GetAsyncKeyState(1) & 128) Then
> > > MsgBox ("left click trapped")
> > > End If
>
> > > (1) is the constant for left mouse click
>
> > > On Feb 7, 11:28 am, rcamarda wrote:
> > > > Hi,
> > > > I am trying to trap a left mouse click. I found lots of uses of
> > > > GetAsyncKeyState, however, even something simple is not working.
>
> > > > Private Declare Function GetAsyncKeyState Lib "user32.dll" (ByVal vKey
> > > > As Long) As Integer
>
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> > > > If GetAsyncKeyState(VK_LBUTTON) Then
> > > > MsgBox ("left click trapped")
> > > > End If
>
> > > > If GetAsyncKeyState(VK_BACK) Then
> > > > MsgBox ("back space")
> > > > End If
>
> > > > If GetAsyncKeyState(VK_RETURN) Then
> > > > MsgBox ("enter key")
> > > > End If
>
> > > > End Sub
>
> > > > Should I use a different library when using x64 or Office 2010??
> > > > TIA
> > > > Rob
>
> > > --
>
> > ------
> > > Some important links for excel users:
> > > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > 3. Ex
$$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office 2010 x64
solved it:
I had to add a bit mask (i think thats what its called).
This returned true when I clicked on a cell:
If CBool(GetAsyncKeyState(1) & 128) Then
MsgBox ("left click trapped")
End If
(1) is the constant for left mouse click
On Feb 7, 11:28 am, rcamarda wrote:
> Hi,
> I am trying to trap a left mouse click. I found lots of uses of
> GetAsyncKeyState, however, even something simple is not working.
>
> Private Declare Function GetAsyncKeyState Lib "user32.dll" (ByVal vKey
> As Long) As Integer
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> If GetAsyncKeyState(VK_LBUTTON) Then
> MsgBox ("left click trapped")
> End If
>
> If GetAsyncKeyState(VK_BACK) Then
> MsgBox ("back space")
> End If
>
> If GetAsyncKeyState(VK_RETURN) Then
> MsgBox ("enter key")
> End If
>
> End Sub
>
> Should I use a different library when using x64 or Office 2010??
> TIA
> Rob
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
$$Excel-Macros$$ GetAsyncKeyState not working window 7 office 2010 x64
Hi,
I am trying to trap a left mouse click. I found lots of uses of
GetAsyncKeyState, however, even something simple is not working.
Private Declare Function GetAsyncKeyState Lib "user32.dll" (ByVal vKey
As Long) As Integer
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If GetAsyncKeyState(VK_LBUTTON) Then
MsgBox ("left click trapped")
End If
If GetAsyncKeyState(VK_BACK) Then
MsgBox ("back space")
End If
If GetAsyncKeyState(VK_RETURN) Then
MsgBox ("enter key")
End If
End Sub
Should I use a different library when using x64 or Office 2010??
TIA
Rob
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Click on Cell - show filtered data on another sheet
Paul,
I found an unintentional aspect. when I use the arrow keys and move
over the cell, my sub fires.
I'd like this event to only happen with a mouse click. Can I trap that
event so it doesn't fire when I use the arrow keys?
Thanks,
Rob
On Feb 4, 12:45 pm, Paul Schreiner wrote:
> It's actually very easy!
>
> It's called a Selection_Change event.
>
> on the worksheet, right-click on the sheet "tab" name and select View Code.
> in the VB Edit window, select the pull-down on the upper left and select
> Worksheet
>
> The default event is the Worksheet_SelectionChange event!
> the event gets passed the Range as the varible Target.
>
> I would suggest first checking to see if more than one cell is selected:
>
> If (target.count = 1) then
>
> next, check to see if the selected cell is in the appropriate column:
>
> if (target.column = 4) then
> would check to see if the cell is in column "D".
>
> If it could be in a range of cells: use something like:
>
> If (Not Intersect(Range(Target.Address), Range("D2:K50")) Is Nothing) Then
>
> checks to see if the selected cell is in colums D-K
> and rows 2 through 50
>
> If these tests are met, then you can call the subroutine that changes to the
> data sheet and filters the data based on the Target.Value
>
> let me know if you need further examples.
>
> Paul
>
>
> From: rcamarda
> To: MS EXCEL AND VBA MACROS
> Sent: Fri, February 4, 2011 10:15:53 AM
> Subject: $$Excel-Macros$$ Click on Cell - show filtered data on another sheet
>
> Hi,
> I'm not sure if this is possible, so I thought I would cast a wide
> net.
> Description: I have an Excel spreadsheet with many sheets of summary
> and detail information of employees.
> On a summary sheet, I have rows that list job titles and the quantity
> of employees with that job title in the adjacent cell.
> I'd like to click on the summary number, and then change to the detail
> sheet that shows just the employees with that job title.
> My data is in Tables. I have already programmed setting and clearing
> filters and navigating about the sheets, updating SQL, so I could
> create a sub that does what I want, but I don't know what to search
> about clicking a cell then start the process. Visually I'd like
> something simple like a hyperlink.
> The part that I don't know if possible is the click on a cell then
> have something happen.
> TIA
> Rob
>
> --
> ---
> ---
>
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links
> :http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
> linkhttp://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
$$Excel-Macros$$ Use defined names to return column numbers
Hi, Many time I find code that the argument is an integer. IE checking if the selected cell is a particular column number: If (Target.Column = 34) The trouble is that my sheet layout can change, and 34 becomes 35. Another example of a formula i use in a sheet: =COUNTIF(T_AllEmp[JOB ROLE REF ID],[@[Job Role ID]]) This works great as I can change the column layout of the table and not mess up the formula. I use tables and define names of my column headings. How can I convert the defined names to values? TIA Rob -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Click on Cell - show filtered data on another sheet
Hi Paul,
I'll give your tips a try and get back.
Thanks!
Rob
On Feb 4, 12:45 pm, Paul Schreiner wrote:
> It's actually very easy!
>
> It's called a Selection_Change event.
>
> on the worksheet, right-click on the sheet "tab" name and select View Code.
> in the VB Edit window, select the pull-down on the upper left and select
> Worksheet
>
> The default event is the Worksheet_SelectionChange event!
> the event gets passed the Range as the varible Target.
>
> I would suggest first checking to see if more than one cell is selected:
>
> If (target.count = 1) then
>
> next, check to see if the selected cell is in the appropriate column:
>
> if (target.column = 4) then
> would check to see if the cell is in column "D".
>
> If it could be in a range of cells: use something like:
>
> If (Not Intersect(Range(Target.Address), Range("D2:K50")) Is Nothing) Then
>
> checks to see if the selected cell is in colums D-K
> and rows 2 through 50
>
> If these tests are met, then you can call the subroutine that changes to the
> data sheet and filters the data based on the Target.Value
>
> let me know if you need further examples.
>
> Paul
>
>
> From: rcamarda
> To: MS EXCEL AND VBA MACROS
> Sent: Fri, February 4, 2011 10:15:53 AM
> Subject: $$Excel-Macros$$ Click on Cell - show filtered data on another sheet
>
> Hi,
> I'm not sure if this is possible, so I thought I would cast a wide
> net.
> Description: I have an Excel spreadsheet with many sheets of summary
> and detail information of employees.
> On a summary sheet, I have rows that list job titles and the quantity
> of employees with that job title in the adjacent cell.
> I'd like to click on the summary number, and then change to the detail
> sheet that shows just the employees with that job title.
> My data is in Tables. I have already programmed setting and clearing
> filters and navigating about the sheets, updating SQL, so I could
> create a sub that does what I want, but I don't know what to search
> about clicking a cell then start the process. Visually I'd like
> something simple like a hyperlink.
> The part that I don't know if possible is the click on a cell then
> have something happen.
> TIA
> Rob
>
> --
> ---
> ---
>
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links
> :http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
> linkhttp://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
$$Excel-Macros$$ Click on Cell - show filtered data on another sheet
Hi, I'm not sure if this is possible, so I thought I would cast a wide net. Description: I have an Excel spreadsheet with many sheets of summary and detail information of employees. On a summary sheet, I have rows that list job titles and the quantity of employees with that job title in the adjacent cell. I'd like to click on the summary number, and then change to the detail sheet that shows just the employees with that job title. My data is in Tables. I have already programmed setting and clearing filters and navigating about the sheets, updating SQL, so I could create a sub that does what I want, but I don't know what to search about clicking a cell then start the process. Visually I'd like something simple like a hyperlink. The part that I don't know if possible is the click on a cell then have something happen. TIA Rob -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
