Schoolboy. I'd switched off enable events accidently, your code works
perfectly. Thanks again.
"Joseph Fletcher" wrote:
> 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
>
> "Bernie Deitrick" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:44C1518B-A8AF-4FB8-9CF1-(E-Mail Removed)...
> > > 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!
> >
> >
> >
|
Similar Threads
