Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating 150+ checkboxes with a macro in Excel 2000
Hi Tom -
Thanks for your helpful reply. For some reason, Excel doesn't like this statement: Set chbox2 = ActiveSheet.CheckBoxes("Checkbox" & CLng(num) + 64) ....though I can't find anything wrong with it myself. It gives me an error window with the number "400" on it, and an option to click OK or Help. Clicking Help does nothing. Any ideas? Thanks, Stephan "Tom Ogilvy" wrote in message ... application.Caller will give you the name of the checkbox Sub GlobalCheckbox_click() dim chbox as Checkbox dim chbox2 as Checkbox dim sName as String, num as String sName = application.Caller set chbox = activesheet.Checkboxes(sName) ' next if block not needed if you only assign to checkboxes ' where the activity should happen if chbox.TopLeftCell.Column = 1 then ' column A if chbox.Value = xlOn then ' get number in name if chbox.topLeftCell.row < 10 then num = right(sname,1) else num = left(sName,1) end if set chbox2 = activesheet.Checkboxes("Checkbox" _ & clng(num)+64 ) chbox2.Value = xlon end if end if End sub make adjustments for the actual locations of your checkboxes in terms of single digit values (less than 10) and column Only assign the macro to checkboxes which should exhibit this behavior. consider the code a pseudocode since it hasn't been tested and may contain typos. Note that application.caller will not be defined if you try to run the code manually using the run command or call from another routine. -- Regards, Tom Ogilvy "S. Romano" wrote in message om... I have a workbook with a few worksheets in it. One of the worksheets has 160 "Forms" style checkboxes, arranged in rows of 5. The numbering for the checkboxes is seqential in columns - that is, the first column has CheckBox1 through CheckBox32, the second one contains CheckBox33 through CheckBox64, etc. Here is what I'd like to do: The columns have some relationships - for instance, when the first column is checked, the 3rd column should also be checked. There doesn't need to be any cross-checking as it is okay for the user to override it by manually unchecking the 3rd column. I am very new at this, and have done a lot of searching of the groups here, but have not found an answer. I can visualize what needs to be done: upon checking of the first column's CheckBox, I can add 64 to the CheckBox number to get the corresponding checkbox in the third column, then set that value to True. What I am having trouble with is creating a global event for this, rather than manually coding each checkbox. Can someone point me in the right direction? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to clear checkboxes | Excel Worksheet Functions | |||
Macro to clear checkboxes and protected cells | Excel Worksheet Functions | |||
Have problem in Manipulating Workbook Object and make macro shortest as possible | Excel Worksheet Functions | |||
How do I delete checkboxes from rows I deleted in a macro? | Excel Discussion (Misc queries) | |||
Multiple checkboxes 1 macro | Excel Programming |