|
I'm testing the GetCursorPos function as the code below reflects. The issue I'm running into is that the Worksheet_SelectionChange event for Sheet1 is not capturing the changing X and Y's for the mouse when I'm changing cell positions with the arrow keys. If I click on a new cell, the values change fine. But I need new X and Y computed when I'm moving the cursor with the arrow keys.
<br />
'This is in Module1<br />
Option Explicit<br />
<br />
Private Type POINTAPI<br />
X As Long<br />
Y As Long<br />
End Type<br />
<br />
Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long<br />
Private Declare Function ScreenToClient Lib "user32" (ByVal hWnd As Long, lpPoint As POINTAPI) As Long<br />
<br />
Function MouseX(Optional ByVal hWnd As Long) As Long<br />
Dim lpPoint As POINTAPI<br />
GetCursorPos lpPoint<br />
If hWnd Then ScreenToClient hWnd, lpPoint<br />
MouseX = lpPoint.X<br />
End Function<br />
<br />
Function MouseY(Optional ByVal hWnd As Long) As Long<br />
Dim lpPoint As POINTAPI<br />
GetCursorPos lpPoint<br />
If hWnd Then ScreenToClient hWnd, lpPoint<br />
MouseY = lpPoint.Y<br />
End Function<br />
<br />
'This is in Sheet1<br />
Option Explicit<br />
<br />
Private Sub Worksheet_SelectionChange(ByVal Target As Range)<br />
Range("A1").Value = MouseX<br />
Range("A2").Value = MouseY<br />
End Sub<br />
|
|
|
|
|
After reading your description, I'm a bit confused. The cursor position returned by GetCursorPos is the mouse pointer, not the selected cell in Excel. It sounds like you're trying to get the screen coordinates of the selected cell??
|
|
|
|
|
Hi Dave: Yes, I'm trying to get the mouse coordinates of the selected cell. I understand that GetCursorPos returns the mouse position. I'm just displaying those values into Range("A1") as a test as I learn how to use this particular function. (Later, I'm going to use the mouse coordinates to change the position of a command button as the user moves around the sheet.) The issue I'm having is that the values in "A1" don't change when I move the around the sheet using the arrow keys. They only change when I click on a cell on the spreadsheet.
|
|
|
|
|
If the mouse doesn't move as your change the selected cell with the arrow keys, the values returned won't change.
If you want to find out how to get the coords of a cell, read this[^]. It's not as simple as what your code suggests.
|
|
|
|
|
Dave: Yes! You're right. Thanks. I'm new to mouse events so I was fixating on the the arrow keys. So I think I need to use a different method to accomplish what I need which is to have a CommandButton's location change based on the user's movements around a sheet. A draggable button would allow the user to move the button as needed. I've got some code for a drag/drop process but ran into an immediate issue. I put CommandButton1 on a sheet and put the code below into the MouseDown event handler. When I click on the button, however, I get a "Variable not defined" pointing to 'vbRightButton'. Any idea what's causing this? (BTW, I'm running VB6.5 and Excel 2003)
Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Button = vbRightButton And Shift = vbCtrlMask Then
Range("A1") = "test"
End If
End Sub
|
|
|
|
|
You're not using VB6.5 (there's no such thing - it stopped at VB6.) You're using VBA (Visual Basic for Applications) in Excel. VBA doesn't have all the same constants defined as VB6 does. vbRightButton doesn't exist in VBA. You have to write the code to define these constants yourself, which you can find here[^].
|
|
|
|
|
Thanks, Dave. I figured it must be a version incompatibility. I'll refer to the link you provided. BTW, I looked at the Help item on the the VBA menu and this is what it lists:
Microsoft Visual Basic 6.5
Copyright 1987-2006 Microsoft Corp.
Version 1024
VBA: Retail 6.5.1024
Forms3: 12.0.6025.500
But that's a minor point. I get what you're saying. More after a bit of study. Thanks again.
|
|
|
|
|
VBA went to 6.5 and is still in use in Office 2007, maybe even 2010 - I don't have a copy to check.
If you mention Visual Basic (alone) or VB6, everyone will assume it's the full Visual Studio 6.0 version. If you're working in Office, you have to say VBA, otherwise, everyone will think you're doing Office interop from a VB6 app.
|
|
|
|
|
Not relevant to this post just to let you know: VBA version in Office 10 is 7.0.1590 and Form3 is 14.0.4747.100.
Tosch
|
|
|
|
|
Right, got it. So the code I've been consulting for VB6 is not going to work for VBA. Can you suggest a drag/drop method for a CommandButton that will work in VBA?
|
|
|
|
|
Then buttons don't work the same way in Office as they do in the "real world". I don't think you're going to be able to drag the button itself, but you can drag a container that the button is in, such as a user form.
|
|
|
|
|
That's a good idea. Any suggestions on code that makes a user form draggable in VBA?
|
|
|
|
|
You don't need any code. A UserForm is already draggable.
|
|
|
|
|
That will work. The only thing is I'd like to hide the top blue border as well as the 'X' button. Can you suggest methods that will hide both?
|
|
|
|
|
You can't get rid of the title bar without stopping the ability to drag the form. You'd have to write new code to put back in the ability to drag the form, but there's a problem. If the user tries to drag the form around and clicks on one of the controls on it, your code will never know that the user is trying to drag.
As for the close box, you can add code to cancel closing the form, but you can't remove it.
|
|
|
|
|
Thank you, Dave, this has been a huge help. I'm going to go with a user form and put the button control in it. This will appear on all sheets. The control is a bit large with the top bar and 'X' but it works fine functionally. Thanks again.
|
|
|
|
|
please see this
http://wastuae.com/uae/adv.aspx?id=470299
|
|
|
|
|
Hi,
I have a form with TabControl object at which there are 5 tabpages
What I want to do is that when user click the tab, the new tabpage is showing on form, at this time I want to show some message use msgbox "".
Now I use TabControl1_Selected event, the message is always shown before the actually tabpage is displayed, I want the message is shown after tabpage is displayed.
How can I do it?
Thanks!
|
|
|
|
|
The Selected event may be the wrong one. IMO you should use the SelectedIndexChanged event, and wire it after the Form is completely ready, i.e. in its Shown handler.
|
|
|
|
|
Thanks! SelectedIndexChanged event works if the tabpage is not the first one.
If the tabpage is the first page (index = 0), the message still cannot be shown.
|
|
|
|
|
You might also try handling the TabPage.Enter event.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Thanks for reply!
TabPage.Enter event doesn't work.
|
|
|
|
|
Hi all,
I am using a list view in my project to display some data.
The listview has over 5000 element to display.but
when i run my program through debug mode then it gives the following error
"The CLR has been unable to transition from COM context 0x1a1010 to COM context 0x1a1180 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping"
but when i run the exe then it is working fine.
can any body give me any clue,why it is happening ?
|
|
|
|
|
how are you loading the data?
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
souravghosh18 wrote: can any body give me any clue,why it is happening ?
Are you doing that processing on the UI-thread? Have you considered moving it to a backgroundworker?
I are Troll 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
