Bob Phillips: Program Right Mouse clicks?
 

Mr. Phillips:

Thank you so much for your help these last few days, I really
appreciate it.

By boss wants input in the spreadsheet to depend on whether you left or
right click in a cell. Solve this example below, and I can build the
actual solution:

Left click on cell A1, the color changes and a number "1" is inserted
in the cell B1. (you helped me with this yesterday).

If you then RIGHT click on cell A1, a number "2" is inserted in cell
C1.

Thanks!


---
Message posted from http://www.ExcelForum.com/

Bob Phillips: Program Right Mouse clicks?
 

Jason,

I have some code for you, but there are a few gaps that need sorting.

You say if the cell contains 1, right-click changes it tgo 2.
What do we do if it is already 2 and you right-click?
What do we do if it is empty and we right-click?

You should also understand that every time you select a cell to right-click,
the selecting is effectively a left -click. B y this I mean, that say A5 is
yellow/1 and you want to turn it to yellow/2. By selecting it again, it will
go clear.

Also just re-read your other post follow-ups and you mention cycling
through values. Is that still required or not now? If so, what do we do with
those other values if we then right-click?

Here's what we've got so far anyway.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)

If Not Intersect(Target, Range("A1:A12")) Is Nothing Then
If Target.Count 1 Then
Cancel = True
ElseIf Target.Offset(0, 1) = 2 Then
Target.Interior.ColorIndex = xlColorIndexNone
Target.Offset(0, 1).Value = ""
ElseIf Target.Offset(0, 1) = 1 Then
Target.Interior.ColorIndex = 6
Target.Offset(0, 1) = 2
End If
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jasonsweeney " wrote in
message ...
Mr. Phillips:

Thank you so much for your help these last few days, I really
appreciate it.

By boss wants input in the spreadsheet to depend on whether you left or
right click in a cell. Solve this example below, and I can build the
actual solution:

Left click on cell A1, the color changes and a number "1" is inserted
in the cell B1. (you helped me with this yesterday).

If you then RIGHT click on cell A1, a number "2" is inserted in cell
C1.

Thanks!


---
Message posted from http://www.ExcelForum.com/

Bob Phillips: Program Right Mouse clicks?
 

Ok.

Yes, there will be cycles of colors....

Here is the solution I am working on:

In cells A1:A50 there is a series of questions regarding the business
e.g. one is "Are your transaction cost data networked with your ER
software?" (there are other questions indentical to the set-u
desribed below in other columns)

If the answer is yes, the user clicks on that cell once (turning th
cell green, and producing the value "1" in Cell B1). However, if th
user wants to set this issue as a priority for the next quarter, h
clicks the cell again (turning it Red, and producing a value "2" i
Cell B1.) If it is to be an issue in future quarters, he clicks agai
(turning the cell blue, and producing a value "3" in Cell B1). Th
fourth click is basically a reset (no color, value 0 in Cell B1). Thi
part I have done and it looks great.

Now my boss wants this: If you select "Yes" (green, #1), then he want
the user to be able to RIGHT click on Cell A1 and cycle through th
numbers 1-5 to indicate the "competency" of the business in achievein
this bullet point. If I can get the right click to cycle through th
values 1-5 in cell C1, I can use cell formulas to indicate th
competency rating in the actual cell.....<=If(B1=1,"("&C1&"
"&AA1,AA1). Where AA1 is the text of the statements....Thus the Cel
would be green in color and would read "(2) Are your transaction cos
data netwroked with your ERP software?"

If you select "Priority next quarter" (Red, #2) then he also wants
cycle between 1-5 stating the importance of the priority....

Thats whats going on here

--
Message posted from http://www.ExcelForum.com

Bob Phillips: Program Right Mouse clicks?
 

Jason,

OK this is what I suggest we do.

We'll test for selecting column A and cycle through values 0-4 and set the
colours and column B values as you suggest.

When selecting, we'll save the old value so that if you then right click, it
will be restored (overcoming the problem I mentioned last time). Any
right-click will also put that formula in C automatically. I presume this
formula
......<=If(B1=1,"("&C1&")"&AA1,AA1)
should have read
......<=If(B1=1,"("&C1&")"&A1,A1)

I'll post back with the results later.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jasonsweeney " wrote in
message ...
Ok.

Yes, there will be cycles of colors....

Here is the solution I am working on:

In cells A1:A50 there is a series of questions regarding the business,
e.g. one is "Are your transaction cost data networked with your ERP
software?" (there are other questions indentical to the set-up
desribed below in other columns)

If the answer is yes, the user clicks on that cell once (turning the
cell green, and producing the value "1" in Cell B1). However, if the
user wants to set this issue as a priority for the next quarter, he
clicks the cell again (turning it Red, and producing a value "2" in
Cell B1.) If it is to be an issue in future quarters, he clicks again
(turning the cell blue, and producing a value "3" in Cell B1). The
fourth click is basically a reset (no color, value 0 in Cell B1). This
part I have done and it looks great.

Now my boss wants this: If you select "Yes" (green, #1), then he wants
the user to be able to RIGHT click on Cell A1 and cycle through the
numbers 1-5 to indicate the "competency" of the business in achieveing
this bullet point. If I can get the right click to cycle through the
values 1-5 in cell C1, I can use cell formulas to indicate the
competency rating in the actual cell.....<=If(B1=1,"("&C1&")
"&AA1,AA1). Where AA1 is the text of the statements....Thus the Cell
would be green in color and would read "(2) Are your transaction cost
data netwroked with your ERP software?"

If you select "Priority next quarter" (Red, #2) then he also wants a
cycle between 1-5 stating the importance of the priority....

Thats whats going on here.


---
Message posted from http://www.ExcelForum.com/

Bob Phillips: Program Right Mouse clicks?
 

Jason,

Here's what I have so far.

This is what happens.

If you select any cell in A1:A50, it cycles through the colours green, red,
blue, none, and sets the column B cell to the value 1,2,3, or ""
accordingly. It then moves the selection over to column B (this was to
enable cycling through A without having to go and select other cells).

If you right-click any cell in A1:A50, it checks column B for a 1 or a 2. If
it is, it cycles column C through the values 1-5, and constructs the text
for that value. If C is already 5, nothing happens (should it revert to
blank?

One more question . What should happen if column A is red, and column C has
a value and the A is selected again. This will turn A blue, B to 3, but
should C be left alone or cleared?

Here's the code. Replace all the other code with this.

Option Explicit

Dim oldValue

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)

If Not Intersect(Target, Range("A1:A50")) Is Nothing Then
With Target
.Offset(0, 1) = oldValue
SetColour Target
If .Count 1 Then
Cancel = True
ElseIf .Offset(0, 1) = 1 Or _
.Offset(0, 1) = 2 Then
If .Offset(0, 2).Value = "" Then
.Offset(0, 2).Value = "(1) " & .Value
ElseIf Mid(.Offset(0, 2).Value, 2, 1) < 5 Then
.Offset(0, 2).Value = "(" & Mid(.Offset(0, 2).Value, 2,
1) + 1 & _
") " & .Value
End If
End If
End With
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A50")) Is Nothing Then
If Target.Count = 1 Then
With Target
oldValue = .Offset(0, 1).Value
Select Case .Offset(0, 1).Value
Case 1: .Offset(0, 1).Value = 2
Case 2: .Offset(0, 1).Value = 3
Case 3: .Offset(0, 1).Value = ""
Case Else: .Offset(0, 1).Value = 1
End Select
End With
SetColour Target
End If
Target.Offset(0, 1).Select
End If

End Sub

Private Sub SetColour(Target As Range)
With Target
Select Case .Offset(0, 1).Value
Case 1: .Interior.ColorIndex = 10 'Green
Case 2: .Interior.ColorIndex = 3 'Red
Case 3: .Interior.ColorIndex = 5 'Blue
Case Else: .Interior.ColorIndex = xlColorIndexNone
End Select
End With
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Jason,

OK this is what I suggest we do.

We'll test for selecting column A and cycle through values 0-4 and set the
colours and column B values as you suggest.

When selecting, we'll save the old value so that if you then right click,
it
will be restored (overcoming the problem I mentioned last time). Any
right-click will also put that formula in C automatically. I presume this
formula
.....<=If(B1=1,"("&C1&")"&AA1,AA1)
should have read
.....<=If(B1=1,"("&C1&")"&A1,A1)

I'll post back with the results later.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob Phillips: Program Right Mouse clicks?
 

Bob,

Thanks again so much for your help. To answer your questions:

(1) If the person has already left clicked and produced Green, #1, then
right clicks, and then goes back to the original cell and changes it,
the number in the C column should be reset.


---
Message posted from http://www.ExcelForum.com/

Bob Phillips: Program Right Mouse clicks?
 

Jason,

Do you want me to amend that, or will you?

That apart, is it doing what you wanted?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jasonsweeney " wrote in
message ...
Bob,

Thanks again so much for your help. To answer your questions:

(1) If the person has already left clicked and produced Green, #1, then
right clicks, and then goes back to the original cell and changes it,
the number in the C column should be reset.


---
Message posted from http://www.ExcelForum.com/

Bob Phillips: Program Right Mouse clicks?
 

Bob,

I'ss write the code to cancel out the C column on a change to A, than
you. I also edited my response to ask about whether you know how t
turn off the right-click drop down menu....ANy thoughts

--
Message posted from http://www.ExcelForum.com

Bob Phillips: Program Right Mouse clicks?
 

Jason,

This code disables the right-click menu

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
Cancel = True
End Sub

Put it in the ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jasonsweeney " wrote in
message ...
Bob,

I'ss write the code to cancel out the C column on a change to A, thank
you. I also edited my response to ask about whether you know how to
turn off the right-click drop down menu....ANy thoughts?


---
Message posted from http://www.ExcelForum.com/

Bob Phillips: Program Right Mouse clicks?
 

So I am having trouble programming the "delete" code to delete the value
in Column C when the user selects Option 3, or Option "" with the left
mouse button....

Its weird. The code works in terms of deleting the number in Column C,
but now a Red Selction (#2) freezes at "1" in Column C....In other
words. If the cell is red, you now can't cyle through the numbers
1-3.....it sticks at 1.

here is the code that doesn't work:

__________________
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A50")) Is Nothing Then
If Target.Count = 1 Then
With Target
oldValue = .Offset(0, 1).Value
Select Case .Offset(0, 1).Value
Case 1: .Offset(0, 1).Value = 2
Case 2: .Offset(0, 1).Value = 3
Case 3: .Offset(0, 1).Value = ""
Case Else: .Offset(0, 1).Value = 1
End Select
End With
SetColour Target
ZeroOut Target '<--------------------- Calls my
sub
End If
Target.Offset(0, 1).Select
End If
End Sub
__________
Private Sub ZeroOut(Target As Range)
If Not Intersect(Target, Range("A1:A50")) Is Nothing Then
With Target
If .Offset(0, 1).Value = 3 Then
..Offset(0, 2).Value = ""
ElseIf .Offset(0, 1).Value = "" Then
..Offset(0, 2).Value = ""
End If
End With
End If
End Sub
_____________


---
Message posted from http://www.ExcelForum.com/