Excel and Option Boxes

[newk]

Hello,

I am trying to learn how to use option boxes. Please refer to the attached spreadsheet.

In the column Priority in the s/sheet I would like the user to click into the cell and 1 click would highlight the first option box red, a second click would highlight the second option box amber and a third click the right option box turns green. Can this be done and how would I go about doing it?

Thanks for any help in advance

[Jacob Hilderbrand]

Maybe this is what you would like to do.

Put this code in the Worksheet Code Module that has the Option Buttons.


Option Explicit

Private Sub Image1_Click()
Me.OptionButton1.BackColor = ThisWorkbook.Colors(2)
Me.OptionButton2.BackColor = ThisWorkbook.Colors(2)
Me.OptionButton3.BackColor = ThisWorkbook.Colors(2)
Select Case True
Case Is = Me.OptionButton1.Value
Me.OptionButton2.Value = True
Me.OptionButton2.BackColor = ThisWorkbook.Colors(44)
Case Is = Me.OptionButton2.Value
Me.OptionButton3.Value = True
Me.OptionButton3.BackColor = ThisWorkbook.Colors(4)
Case Is = Me.OptionButton3.Value
Me.OptionButton1.Value = True
Me.OptionButton1.BackColor = ThisWorkbook.Colors(3)
Case Else
Me.OptionButton1.Value = True
Me.OptionButton1.BackColor = ThisWorkbook.Colors(3)
End Select
End Sub

Add an Image Control on top of the Option Buttons and make it transparent. That way you click the Image Control and not one of the Option Buttons.

Also 44 is the Color Index for Gold by default and not Amber.

See attached.

[newk]

Thanks DRJ.

If there were 100 rows of projects, would I have to add 3 option boxes plus script for each row? Could you think of a better way to represent the colour other than selecting the cell and colouring it red, amber, green.

[newk]

Also I've noticed that the buttons dissappear once selected. If I change sheet then click back they are there with the appropriate colour again. Why is this and how could I stop it

[Jacob Hilderbrand]

Thanks DRJ.

If there were 100 rows of projects, would I have to add 3 option boxes plus script for each row? Could you think of a better way to represent the colour other than selecting the cell and colouring it red, amber, green.If we don't use Option Buttons we have a much easier solution. I'll write it up and post an attachment in a few minutes.

[Jacob Hilderbrand]

Also I've noticed that the buttons dissappear once selected. If I change sheet then click back they are there with the appropriate colour again. Why is this and how could I stop itI notice that as well. Once you move the mouse, the Option Buttons reappear.

[Jacob Hilderbrand]

Ok, get rid of the Option Buttons and add this code to the Worksheet Code Module.



Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim TargetRange As Range
'This is the range we want to work with. Change as needed.
Set TargetRange = Range("I4:I65536")
'This is to check if the cell that is clicked on is in the range we want.
If Intersect(Target, TargetRange) Is Nothing Then
Exit Sub
End If
'Cancel the default double click action.
Cancel = True
'Check the current color of the cell.
Select Case Target.Interior.ColorIndex
Case Is = 3 'Red
Target.Interior.ColorIndex = 44
Target.Value = 2
Case Is = 4 'Green
Target.Interior.ColorIndex = 3
Target.Value = 1
Case Is = 44 'Gold
Target.Interior.ColorIndex = 4
Target.Value = 3
Case Else 'Anything else
Target.Interior.ColorIndex = 3
Target.Value = 1
End Select
End Sub

See attached.