One macro for many controls?
 

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.

One macro for many controls?
 

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.


---
Message posted from http://www.ExcelForum.com/

One macro for many controls?
 

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
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

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.

One macro for many controls?
 

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


--
Regards,
Rocky McKinley


"Don Wiss" wrote in message
...
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.

One macro for many controls?
 

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

ActiveCell.Activate

should work.

--
Regards,
Tom Ogilvy

"Don Wiss" wrote in message
...
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.