Cannot run macro but code is in the same VBA file

[hugh111111]

Hi, this ismy first time working with VBA. I’m using Excel 2010 and I have ‘enabled allmacros’ in the security option.<o></o>
I’ve writtencode to place a number of option buttons in cells. When the user clicks on anoption the colour of that cell should change. Here is the code…

[FONT="Arial"]Private Sub Workbook_Open()[/FONT][/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT="Arial"]            WithSheets(1)[/FONT]
[FONT="Arial"]                         createOB"B1", "   "
                         [/FONT][FONT="Arial"]createOB"B3", "   "
                         [/FONT][FONT="Arial"]createOB"B4", "   "
[/FONT][FONT="Arial"]                         createOB"B5", "   "
             [/FONT][FONT="Arial"]End With
[/FONT][FONT="Arial"]End Sub<o:p></o:p>[/FONT]
[FONT=Times New Roman][/FONT]
[FONT="Arial"]Private Sub createOB(cCellAdd, sText)<o:p></o:p>[/FONT]
[FONT=Times New Roman]     [/FONT][FONT="Arial"]    WithRange(cCellAdd)
               [/FONT][FONT="Arial"]        
                        Set ob =ActiveSheet.OptionButtons.Add(.Left + .Width / 2, .Top, .Width, .Height)
                        [/FONT][FONT="Arial"]ob.Characters.Text = sText
                        [/FONT][FONT="Arial"]ob.OnAction= "ChangeOB"
           [/FONT][FONT="Arial"]End With
[/FONT][FONT="Arial"]End Sub<o:p></o:p>[/FONT]
[FONT=Times New Roman]
[/FONT][FONT="Arial"]Private Sub ChangeOB()<o:p></o:p>[/FONT]
[FONT=Times New Roman]       [/FONT][FONT="Arial"] ActiveCell.Select

          [/FONT][FONT="Arial"]WithSelection.Interior<o:p></o:p>[/FONT]
[FONT=Times New Roman][/FONT][FONT="Arial"][FONT=Times New Roman][SIZE=3]                [/SIZE][/FONT].Pattern =xlSolid
              [/FONT][FONT="Arial"]       .PatternColorIndex = xlAutomatic
                      [/FONT][FONT="Arial"].Color =65535
                      [/FONT][FONT="Arial"].TintAndShade = 0
                      [/FONT][FONT="Arial"].PatternTintAndShade = 0
          [/FONT][FONT="Arial"]End With<o:p></o:p>[/FONT]
[FONT=Times New Roman][/FONT][FONT="Arial"]End Sub[/FONT]

Now when theuser opens the spread sheet they are presented with a column of option buttonsbut if they click one it produces the following error…<o></o>
Cannot run the macro ". The macro may not be available inthis workbook or all macros may be disabled.


I know I havemacros enabled and this code should be available to this workbook because the OnActioncode in the routine ‘ChangeOB’ is in the same VBA file. I’ve tried changing eachroutine from private to public but still the same problem. Any help is muchappreciated.<o></o>

 

[Andrew Poulsom]

Welcome to MrExcel.

Try moving the ChangeOB procedure to a General module like Module1 and removing the word Private from its declaration.

 

[hugh111111]

Thank you Andrew. That works now.