Checkbox or Option Button

[grendel_42]

Good Morning,

I am working on a sheet in Excel that will include an option for the user to decide between three different states. These states will each be represented by a column and some marker representing the choice made.
For example something like this:

option 1 | option 2 | option 3
---------------------------------
___cb1__|__cb2__|__cb3___
___cb1__|__cb2__|__cb3___

My Problem is that I only want one of the options to be checked in other words I would need some kind of code unchecking the other two checkboxes or some code that enables me to only link the 3 option buttons (those in the same row).

Up until now I was experimenting with the following code:

Sub Process_type()



Dim LRow As Integer
Dim LCol As Integer
Dim L0Range As String
Dim L1Range As String
Dim L2Range As String

' finding the name of the CheckBox in use and its position in the sheet
LName = Application.Caller
LRow = ActiveSheet.DrawingObjects(LName).TopLeftCell.Row
LCol = ActiveSheet.DrawingObjects(LName).TopLeftCell.Column

' accessing the other columns (haven't yet found a way to use Row + Column instead of the "Letter" part)
L0Range = "G" & CStr(LRow)
L1Range = "H" & CStr(LRow)
L2Range = "I" & CStr(LRow)

' checking if the CheckBox is checked and then adding some text in the corresponding field, should later on check those fields for other CheckBoxes and change their state to 0
If ActiveSheet.DrawingObjects(LName).Value > 0 Then
ActiveSheet.Range(L0Range).Value = LRow
ActiveSheet.Range(L1Range).Value = LCol
ActiveSheet.Range(L2Range).Value = LName
Else
ActiveSheet.Range(L0Range).Value = Null
ActiveSheet.Range(L1Range).Value = Null
ActiveSheet.Range(L2Range).Value = Null
End If
End Sub




​

[btw I used the CheckBoxes from the Form bar]



As far as I have gotten I am pretty sure that I won't be able to complete the task in the way I started, as I would somehow need to access the names of the other two corresponding checkboxes to change their state. However I have run out of ideas as how to do that.

------
Thanks John

 

[grendel_42]

Ok I spend some more time with VB and Excel and am now down to the following bit of code.


Sub Process_type()
Dim LRow As Integer
Dim LCol As Integer
Dim L0Range As String
Dim L1Range As String
Dim L2Range As String
Dim BoxValue As Long
Dim BoxNumber As String

LName = Application.Caller
BoxNumber = Mid(LName, 10)
LRow = ActiveSheet.DrawingObjects(LName).TopLeftCell.Row
LCol = ActiveSheet.DrawingObjects(LName).TopLeftCell.Column
BoxValue = ActiveSheet.DrawingObjects(LName).Value

L0Range = "G" & CStr(LRow)
L1Range = "H" & CStr(LRow)
L2Range = "I" & CStr(LRow)

If ActiveSheet.DrawingObjects(LName).Value > 0 Then
ActiveSheet.DrawingObjects("Check Box 1").Value = 0
ActiveSheet.DrawingObjects("Check Box 7").Value = 0
ActiveSheet.Range(L0Range).Value = BoxNumber
ActiveSheet.Range(L1Range).Value = BoxValue
ActiveSheet.Range(L2Range).Value = LRow & " " & LCol
Else
ActiveSheet.Range(L0Range).Value = Null
ActiveSheet.Range(L1Range).Value = Null
ActiveSheet.Range(L2Range).Value = Null
End If
End Sub

The following bit enables me to read out the number of the checkbox that initialises the whole Makro.
LName = Application.Caller
BoxNumber = Mid(LName, 10)
However now I am looking for a way to also get the name of the other two corresponding checkboxes to enable me to change the "ActiveSheet.DrawingObjects("Check Box 1").Value = 0" to something like "ActiveSheet.DrawingObjects("Check Box " & BoxNumber1).Value = 0", where "BoxNumber1" would be the somehow aquired number of one of the corresponding boxes.
It is easy to find the associated cells, but I do not now how to determine the Name of the CheckBox therein and would hope for someone to be able to help me along.

-----
Thanks John

 

[grendel_42]

Well at least this problem I was able to solve myself after investing a lot of time into it, so I will simply share it for future reference here.

Sub Process_type()
	Dim LCol As Integer
	Dim BoxNumber As String
	
	LName = Application.Caller
	BoxNumber = Mid(LName, 10)
	LCol = ActiveSheet.DrawingObjects(LName).TopLeftCell.Column
Select Case LCol
Case 8
	If ActiveSheet.DrawingObjects(LName).Value > 0 Then
		ActiveSheet.DrawingObjects("Check Box " & BoxNumber + 1).Value = 0
		ActiveSheet.DrawingObjects("Check Box " & BoxNumber + 2).Value = 0
	End If
Case 9
	If ActiveSheet.DrawingObjects(LName).Value > 0 Then
		ActiveSheet.DrawingObjects("Check Box " & BoxNumber - 1).Value = 0
		ActiveSheet.DrawingObjects("Check Box " & BoxNumber + 1).Value = 0
	End If
Case 10
	If ActiveSheet.DrawingObjects(LName).Value > 0 Then
		ActiveSheet.DrawingObjects("Check Box " & BoxNumber - 2).Value = 0
		ActiveSheet.DrawingObjects("Check Box " & BoxNumber - 1).Value = 0
	End If
End Select
End Sub

The code has still to be matched on the columns that it will be executed on (Case-statement) but everything else works pretty dynamic, without caring what row it is in.

-----
Sincerely yours John