One macro for many controls?

[Don Wiss]

I really dislike the check boxes from the Control toolbar. The one on the
Forms toolbar is so much easier to implement. But there are times when I
want them to move and size with cells (like when I hide unused rows). So I
end up spending hours implementing dozens of identical check boxes.

Right now I have 26 that all call identical macros. It appears that I can't
have them all call the same macro, but that I must have 26 separate macros.
Is it possible for them all to call just one?

I am using Excel 97. One of my problems with using a check box from the
Control toolbar is the focus stays on the control. This is not good. So the
macro each calls runs:

Application.ScreenUpdating = False
Range(ActiveCell.Address).Select

Am I missing something here? Is there a simpler way to do this?

Don <donwiss at panix.com>.

 

[Kieran]

Don,

You could create a custom menu button that does the same action.The
checkboxes will not then be required.
If not a menu button, then if your worksheet has a static area due to
freeze panes, you could add a button to the worksheet that does the
same thing.

 

[pfsardella]

You should be able to adapt this spiel from John Walkenbach to deal
with this issue.

http://j-walk.com/ss/excel/tips/tip44.htm

HTH
Paul

 

[Rocky McKinley]

Another way is to have checkmarks in cells, paste this code in any sheet
module and double click on any cell in range("A1:A10) to toggle a checkmark
on or off. Change the range to suite your needs.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Cancel = True
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
'run my code here
Else
Target = vbNullString
'or run my code here
End If
End If
End Sub

 

[Tom Ogilvy]

You don't need screenupdating or use of the address property

ActiveCell.Activate

should work.