Visual Basic

27-Sep-10 18:59

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 />

Dave Kreskowiak28-Sep-10 1:42

28-Sep-10 1:42

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??

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Win32SF28-Sep-10 5:08

28-Sep-10 5:08

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.

Dave Kreskowiak28-Sep-10 6:03

28-Sep-10 6:03

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.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Win32SF28-Sep-10 12:32

28-Sep-10 12:32

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

Dave Kreskowiak28-Sep-10 16:41

28-Sep-10 16:41

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[^].

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Win32SF28-Sep-10 17:35

28-Sep-10 17:35

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.

Dave Kreskowiak28-Sep-10 18:26

28-Sep-10 18:26

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.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

tosch28-Sep-10 19:46

tosch

28-Sep-10 19:46

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

Win32SF28-Sep-10 20:00

28-Sep-10 20:00

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?

Dave Kreskowiak29-Sep-10 2:11

29-Sep-10 2:11

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.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Win32SF29-Sep-10 5:34

29-Sep-10 5:34

That's a good idea. Any suggestions on code that makes a user form draggable in VBA?

Dave Kreskowiak29-Sep-10 5:44

29-Sep-10 5:44

You don't need any code. A UserForm is already draggable.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Win32SF29-Sep-10 18:21

29-Sep-10 18:21

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?

Dave Kreskowiak30-Sep-10 2:00

30-Sep-10 2:00

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.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Win32SF2-Oct-10 7:17

2-Oct-10 7:17

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.