One macro for many controls?

  • Thread starter Thread starter Don Wiss
  • Start date Start date
D

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>.
 
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.
 
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
 
You don't need screenupdating or use of the address property

ActiveCell.Activate

should work.
 
Back
Top