Controls on sheet OTF

[CicoMico]

Hello!

Is it possible to make controls (check box) on every row in column X on the fly with some code added to it (options: yes, no, possible)?

thanx

[Bob Phillips]

How can you have 3 options with a 2 state checkbox?

Here is an alternate approach, it puts a tick mark in a cell when double-clicking it



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
Select Case .Value
Case "": .Value = "a"
Case "a": .Value = ""
End Select
End With
Cancel = True
End If
err_handler:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[CicoMico]

How can you have 3 options with a 2 state checkbox?

Here is an alternate approach, it puts a tick mark in a cell when double-clicking it



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
Select Case .Value
Case "": .Value = "a"
Case "a": .Value = ""
End Select
End With
Cancel = True
End If
err_handler:
Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

ups! sorry! i didn't mean check box... I mean combo box... anyway thanx for code, i'll try it

[rory]

You can do that with Data Validation - just set 'Allow' option to 'List' and enter this as source:Yes, No, Possible