This worked ok for me--it stopped it each time the shift key was held (is that
what you wanted?):

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
MsgBox "not this time buddy!"
Exit Sub
End If

Set myShape = ActiveSheet.Shapes(Application.Caller)

MsgBox myShape.TopLeftCell.Address

End Sub




Sheldon wrote:

Well, I figured out that I can use GetKeyState. All I need now is to reset
the KetState. In other words, the first time the macro works correctly. But
after that, the macro runs whether the shift key is pressed or not.

So How do I reset?

Thanks

"Sheldon" wrote:

In a worksheet I have a drawing object (rectangle) tied to macro. I want to
macro to run ONLY if the SHIFT key is pressed and the drawing object is
clicked. How can I do this?

Thanks

--

Dave Peterson

Actually, I had the code backwards:

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
Set myShape = ActiveSheet.Shapes(Application.Caller)
MsgBox myShape.TopLeftCell.Address
Else
Exit Sub
End If

End Sub

But I didn't have to reset any keys to make it not work if the shift key were
not held.

Any chance your shiftkey is sticking????

I used xl2002 and win98 and it worked fine.

Sheldon wrote:

Almost what I want. One I press the shift key and click on the shape, the
assigned macro does run correctly. However, the next time I click the shape,
I don't need to depress the shift key. The macro still runs. It should not.
I need a way, in code, to reset the shift key. If the user hits the shift
key, and then later clicks on the shape, the macro does not run (correct)
because the shift was not depressed at the same time the shape was clicked.

I hope that explains what I need.

"Dave Peterson" wrote:

This worked ok for me--it stopped it each time the shift key was held (is that
what you wanted?):

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
MsgBox "not this time buddy!"
Exit Sub
End If

Set myShape = ActiveSheet.Shapes(Application.Caller)

MsgBox myShape.TopLeftCell.Address

End Sub




Sheldon wrote:

Well, I figured out that I can use GetKeyState. All I need now is to reset
the KetState. In other words, the first time the macro works correctly. But
after that, the macro runs whether the shift key is pressed or not.

So How do I reset?

Thanks

"Sheldon" wrote:

In a worksheet I have a drawing object (rectangle) tied to macro. I want to
macro to run ONLY if the SHIFT key is pressed and the drawing object is
clicked. How can I do this?

Thanks

--

Dave Peterson



--

Dave Peterson

You may want to post the code you're using.

Sheldon wrote:

I'm running Excel 2000 on Windows 2000.
I don't know how yours is working.
Declare Function GetKeyState Lib "User32" (ByVal vKey As Integer) As Integer

Before I ever press the shift key, the return value for
GetKeyState(vbKeyShift) = 0.
After I press the shift key the return value for GetKeyState(vbKeyShift) = 1.

Your code checks for a return value of less than 0?????????

Also I'm sure the key is not sticking. Any keys I alpha keys I press after
the event displays as lower case.

"Dave Peterson" wrote:

Actually, I had the code backwards:

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
Set myShape = ActiveSheet.Shapes(Application.Caller)
MsgBox myShape.TopLeftCell.Address
Else
Exit Sub
End If

End Sub

But I didn't have to reset any keys to make it not work if the shift key were
not held.

Any chance your shiftkey is sticking????

I used xl2002 and win98 and it worked fine.

Sheldon wrote:

Almost what I want. One I press the shift key and click on the shape, the
assigned macro does run correctly. However, the next time I click the shape,
I don't need to depress the shift key. The macro still runs. It should not.
I need a way, in code, to reset the shift key. If the user hits the shift
key, and then later clicks on the shape, the macro does not run (correct)
because the shift was not depressed at the same time the shape was clicked.

I hope that explains what I need.

"Dave Peterson" wrote:

This worked ok for me--it stopped it each time the shift key was held (is that
what you wanted?):

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
MsgBox "not this time buddy!"
Exit Sub
End If

Set myShape = ActiveSheet.Shapes(Application.Caller)

MsgBox myShape.TopLeftCell.Address

End Sub




Sheldon wrote:

Well, I figured out that I can use GetKeyState. All I need now is to reset
the KetState. In other words, the first time the macro works correctly. But
after that, the macro runs whether the shift key is pressed or not.

So How do I reset?

Thanks

"Sheldon" wrote:

In a worksheet I have a drawing object (rectangle) tied to macro. I want to
macro to run ONLY if the SHIFT key is pressed and the drawing object is
clicked. How can I do this?

Thanks

--

Dave Peterson



--

Dave Peterson



--

Dave Peterson

OK, here's a reduced version

If GetKeyState(vbKeyShift) < 0 Then
ActiveWindow.NewWindow
ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlArrangeStyleVertical
Windows(ActiveWorkbook.Name & ":1").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Calculations").Select
ActiveWindow.DisplayWorkbookTabs = False
With ActiveWindow 'Calculations Worksheet
.Top = 1
.Left = 496
.Width = 270
.Height = Application.UsableHeight
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayHeadings = False
End With
Windows(ActiveWorkbook.Name & ":2").Activate
With ActiveWindow
.Width = 494
.Height = Application.UsableHeight
End With
End If

End Sub

"Dave Peterson" wrote:

You may want to post the code you're using.

Sheldon wrote:

I'm running Excel 2000 on Windows 2000.
I don't know how yours is working.
Declare Function GetKeyState Lib "User32" (ByVal vKey As Integer) As Integer

Before I ever press the shift key, the return value for
GetKeyState(vbKeyShift) = 0.
After I press the shift key the return value for GetKeyState(vbKeyShift) = 1.

Your code checks for a return value of less than 0?????????

Also I'm sure the key is not sticking. Any keys I alpha keys I press after
the event displays as lower case.

"Dave Peterson" wrote:

Actually, I had the code backwards:

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
Set myShape = ActiveSheet.Shapes(Application.Caller)
MsgBox myShape.TopLeftCell.Address
Else
Exit Sub
End If

End Sub

But I didn't have to reset any keys to make it not work if the shift key were
not held.

Any chance your shiftkey is sticking????

I used xl2002 and win98 and it worked fine.

Sheldon wrote:

Almost what I want. One I press the shift key and click on the shape, the
assigned macro does run correctly. However, the next time I click the shape,
I don't need to depress the shift key. The macro still runs. It should not.
I need a way, in code, to reset the shift key. If the user hits the shift
key, and then later clicks on the shape, the macro does not run (correct)
because the shift was not depressed at the same time the shape was clicked.

I hope that explains what I need.

"Dave Peterson" wrote:

This worked ok for me--it stopped it each time the shift key was held (is that
what you wanted?):

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
MsgBox "not this time buddy!"
Exit Sub
End If

Set myShape = ActiveSheet.Shapes(Application.Caller)

MsgBox myShape.TopLeftCell.Address

End Sub




Sheldon wrote:

Well, I figured out that I can use GetKeyState. All I need now is to reset
the KetState. In other words, the first time the macro works correctly. But
after that, the macro runs whether the shift key is pressed or not.

So How do I reset?

Thanks

"Sheldon" wrote:

In a worksheet I have a drawing object (rectangle) tied to macro. I want to
macro to run ONLY if the SHIFT key is pressed and the drawing object is
clicked. How can I do this?

Thanks

--

Dave Peterson



--

Dave Peterson



--

Dave Peterson

If I changed one line:

from:
If GetKeyState(vbKeyShift) < 0 Then
to:
If GetKeyState(vbKeyShift) < 0 Then

it worked fine.

I'm not smart enough to know why it returned 1 when there were multiple windows.


Sheldon wrote:

OK, here's a reduced version

If GetKeyState(vbKeyShift) < 0 Then
ActiveWindow.NewWindow
ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlArrangeStyleVertical
Windows(ActiveWorkbook.Name & ":1").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Calculations").Select
ActiveWindow.DisplayWorkbookTabs = False
With ActiveWindow 'Calculations Worksheet
.Top = 1
.Left = 496
.Width = 270
.Height = Application.UsableHeight
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayHeadings = False
End With
Windows(ActiveWorkbook.Name & ":2").Activate
With ActiveWindow
.Width = 494
.Height = Application.UsableHeight
End With
End If

End Sub

"Dave Peterson" wrote:

You may want to post the code you're using.

Sheldon wrote:

I'm running Excel 2000 on Windows 2000.
I don't know how yours is working.
Declare Function GetKeyState Lib "User32" (ByVal vKey As Integer) As Integer

Before I ever press the shift key, the return value for
GetKeyState(vbKeyShift) = 0.
After I press the shift key the return value for GetKeyState(vbKeyShift) = 1.

Your code checks for a return value of less than 0?????????

Also I'm sure the key is not sticking. Any keys I alpha keys I press after
the event displays as lower case.

"Dave Peterson" wrote:

Actually, I had the code backwards:

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
Set myShape = ActiveSheet.Shapes(Application.Caller)
MsgBox myShape.TopLeftCell.Address
Else
Exit Sub
End If

End Sub

But I didn't have to reset any keys to make it not work if the shift key were
not held.

Any chance your shiftkey is sticking????

I used xl2002 and win98 and it worked fine.

Sheldon wrote:

Almost what I want. One I press the shift key and click on the shape, the
assigned macro does run correctly. However, the next time I click the shape,
I don't need to depress the shift key. The macro still runs. It should not.
I need a way, in code, to reset the shift key. If the user hits the shift
key, and then later clicks on the shape, the macro does not run (correct)
because the shift was not depressed at the same time the shape was clicked.

I hope that explains what I need.

"Dave Peterson" wrote:

This worked ok for me--it stopped it each time the shift key was held (is that
what you wanted?):

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
MsgBox "not this time buddy!"
Exit Sub
End If

Set myShape = ActiveSheet.Shapes(Application.Caller)

MsgBox myShape.TopLeftCell.Address

End Sub




Sheldon wrote:

Well, I figured out that I can use GetKeyState. All I need now is to reset
the KetState. In other words, the first time the macro works correctly. But
after that, the macro runs whether the shift key is pressed or not.

So How do I reset?

Thanks

"Sheldon" wrote:

In a worksheet I have a drawing object (rectangle) tied to macro. I want to
macro to run ONLY if the SHIFT key is pressed and the drawing object is
clicked. How can I do this?

Thanks

--

Dave Peterson



--

Dave Peterson



--

Dave Peterson



--

Dave Peterson

Thanks Dave..... It finally worked. AS you I don't understand why it
returned 1 with multiple windows.

Sheldon

"Dave Peterson" wrote:

If I changed one line:

from:
If GetKeyState(vbKeyShift) < 0 Then
to:
If GetKeyState(vbKeyShift) < 0 Then

it worked fine.

I'm not smart enough to know why it returned 1 when there were multiple windows.


Sheldon wrote:

OK, here's a reduced version

If GetKeyState(vbKeyShift) < 0 Then
ActiveWindow.NewWindow
ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlArrangeStyleVertical
Windows(ActiveWorkbook.Name & ":1").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Calculations").Select
ActiveWindow.DisplayWorkbookTabs = False
With ActiveWindow 'Calculations Worksheet
.Top = 1
.Left = 496
.Width = 270
.Height = Application.UsableHeight
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayHeadings = False
End With
Windows(ActiveWorkbook.Name & ":2").Activate
With ActiveWindow
.Width = 494
.Height = Application.UsableHeight
End With
End If

End Sub

"Dave Peterson" wrote:

You may want to post the code you're using.

Sheldon wrote:

I'm running Excel 2000 on Windows 2000.
I don't know how yours is working.
Declare Function GetKeyState Lib "User32" (ByVal vKey As Integer) As Integer

Before I ever press the shift key, the return value for
GetKeyState(vbKeyShift) = 0.
After I press the shift key the return value for GetKeyState(vbKeyShift) = 1.

Your code checks for a return value of less than 0?????????

Also I'm sure the key is not sticking. Any keys I alpha keys I press after
the event displays as lower case.

"Dave Peterson" wrote:

Actually, I had the code backwards:

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
Set myShape = ActiveSheet.Shapes(Application.Caller)
MsgBox myShape.TopLeftCell.Address
Else
Exit Sub
End If

End Sub

But I didn't have to reset any keys to make it not work if the shift key were
not held.

Any chance your shiftkey is sticking????

I used xl2002 and win98 and it worked fine.

Sheldon wrote:

Almost what I want. One I press the shift key and click on the shape, the
assigned macro does run correctly. However, the next time I click the shape,
I don't need to depress the shift key. The macro still runs. It should not.
I need a way, in code, to reset the shift key. If the user hits the shift
key, and then later clicks on the shape, the macro does not run (correct)
because the shift was not depressed at the same time the shape was clicked.

I hope that explains what I need.

"Dave Peterson" wrote:

This worked ok for me--it stopped it each time the shift key was held (is that
what you wanted?):

Option Explicit
Declare Function GetKeyState32 Lib "user32" Alias _
"GetKeyState" (ByVal vKey As Integer) As Integer
Sub testme()

Dim myShape As Shape
If GetKeyState32(16) < 0 Then
MsgBox "not this time buddy!"
Exit Sub
End If

Set myShape = ActiveSheet.Shapes(Application.Caller)

MsgBox myShape.TopLeftCell.Address

End Sub




Sheldon wrote:

Well, I figured out that I can use GetKeyState. All I need now is to reset
the KetState. In other words, the first time the macro works correctly. But
after that, the macro runs whether the shift key is pressed or not.

So How do I reset?

Thanks

"Sheldon" wrote:

In a worksheet I have a drawing object (rectangle) tied to macro. I want to
macro to run ONLY if the SHIFT key is pressed and the drawing object is
clicked. How can I do this?

Thanks

--

Dave Peterson



--

Dave Peterson



--

Dave Peterson



--

Dave Peterson