Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using gui controls | Excel Discussion (Misc queries) | |||
HTML Controls | Excel Discussion (Misc queries) | |||
Volatile Macro for Adding Controls When Opening Workbook | Excel Discussion (Misc queries) | |||
ActiveX Controls vs Form Controls | Excel Discussion (Misc queries) | |||
macro that copies controls | Excel Programming |