Edit cell based on position of option button clicked.

[Joseph Fletcher]

Basically I have a number of yes/no optionbuttons set up on an excel
worksheet. If no is selected on any of them I want the cell to the right of
the no option button to be activated. I can do everything i need to the cell
once it is activated but can't work out how to get it activated in the first
place. Ideally I want to produce a macro that will say:

when no option button clicked,
select cell 1 to right of optionbutton
edit cell

The problem being that there are lots of option buttons and I don't want to
have to create lots of macros with option button 1...etc.

I really hope someone can help as this is very frustrating, I am sure hat
there is an insanely simple answer!

 

[Bernie Deitrick]

Joseph,

It is clearly NOT an insanely simple answer. Here's what you need to do:

Insert a new Class module into your project, and note its name.

Put this code into the class module:

Public WithEvents OptionButtonGroup As MSForms.OptionButton

Private Sub OptionButtonGroup_Click()
Dim myOBCell As Range
Set myOBCell = Where(OptionButtonGroup.Left + _
OptionButtonGroup.Width, OptionButtonGroup.Top)
MsgBox "Hello from " & OptionButtonGroup.Name & _
" near cell " & myOBCell.Address
myOBCell.Select
End Sub


Then in a regular module, put this code:

Option Explicit
Dim OptionButtons() As New Class1 'use the name of the class here, if it is different

Sub SetupOBGroup()
Dim OptionButtonCount As Long
Dim OleObj As OLEObject
OptionButtonCount = 0
For Each OleObj In ActiveSheet.OLEObjects
If TypeOf OleObj.Object Is MSForms.OptionButton Then
OptionButtonCount = OptionButtonCount + 1
ReDim Preserve OptionButtons(1 To OptionButtonCount)
Set OptionButtons(OptionButtonCount).OptionButtonGroup = OleObj.Object
End If
Next OleObj
End Sub

Function Where(myLeft As Double, myTop As Double) As Range
Dim i As Integer
Dim myCol As Integer
Dim myRow As Long

'Find column
For i = 1 To 256
If Cells(1, i).Left > myLeft Then
myCol = i
GoTo FoundCol
End If
Next i

FoundCol:

'Find Row
For i = 1 To 1000
If Cells(i, 1).Top > myTop Then
myRow = i - 1
GoTo FoundRow
End If
Next i

FoundRow:

Set Where = Cells(myRow, myCol)
End Function

Then run the macro SetupOBGroup, and see what happens when you click the option buttons. You will
need to align your option button properly to get the desired effect.

HTH,
Bernie
MS Excel MVP

 

[Joseph Fletcher]

Thanks Bernie, I obviously need to learn how to use class modules!

A further question; this works perfectly when there is only one sheet with
buttons but not when there is more than 1. In my workbook I am going to have
a number of sheets (probably 6), all laid out in the same way. In my
worksheet_activate code I run setupOBGroup, this worked when there was the
single sheet but doesn't with more than one. If I run the code manually (by
F8-ing it) then it works, just not when VBA is shut. What part of the code
do I need to change, or how do I change my code to sort this out, either
resetting the class each time a different sheet is opened or by naming 6
different optionbutton classes.

Your help is invaluable,

Joe

 

[Joseph Fletcher]

Schoolboy. I'd switched off enable events accidently, your code works
perfectly. Thanks again.