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 prompt response. I'm having a problem where Excel spits out an error box that says, simply, "400" in it, with an option to click OK or Help. Help brings up the Help tool but with no contents. This code has been simplified a bit for my testing purposes: 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) If chbox.Value = xlOn Then num = Right(sName, 3) Set chbox2 = ActiveSheet.CheckBoxes("Checkbox" & CLng(num) + 64) chbox2.Value = xlOn End If End Sub The specific line it doesn't like is the "Set chbox2" line - I can't seem to properly set the value of chbox2, even if I explicitly set it with a line like: Set chbox2 = ActiveSheet.CheckBoxes("Checkbox150") or Set chbox2 = ActiveSheet.CheckBoxes(sName) It still gives me the "400" error. The "chbox" variable sets correctly, and the "num" variable returns the correct number. 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating 150+ checkboxes with a macro in Excel 2000
If you are working with checkboxes in the first column, are they all
numbered greater then 99 if not, then num = Right(sName, 3) would not have a number in it and clng(num) would fail. -- Regards, Tom Ogilvy "S. Romano" wrote in message om... Hi Tom - Thanks for your prompt response. I'm having a problem where Excel spits out an error box that says, simply, "400" in it, with an option to click OK or Help. Help brings up the Help tool but with no contents. This code has been simplified a bit for my testing purposes: 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) If chbox.Value = xlOn Then num = Right(sName, 3) Set chbox2 = ActiveSheet.CheckBoxes("Checkbox" & CLng(num) + 64) chbox2.Value = xlOn End If End Sub The specific line it doesn't like is the "Set chbox2" line - I can't seem to properly set the value of chbox2, even if I explicitly set it with a line like: Set chbox2 = ActiveSheet.CheckBoxes("Checkbox150") or Set chbox2 = ActiveSheet.CheckBoxes(sName) It still gives me the "400" error. The "chbox" variable sets correctly, and the "num" variable returns the correct number. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating 150+ checkboxes with a macro in Excel 2000
Sorry, Tom, should have been more specific. My checkboxes are actually
numbered 300+ for some interesting reason. I dumped in a whole bunch of Msgbox commands to show me the results of the variables as they are assigned, and they are all normal. I'm fairly new to Excel macros but I've done some coding in VB before and I've done all the testing I can to ensure that my syntax and variables are all working correctly. Further developments... I popped the code into a module vs. putting it into the worksheet code. The "400" error quit coming up, but now the same line of code is rejected with: Run-time error '1004': Unable to get the CheckBoxes property of the Worksheet class Is there a way to call the checkboxes property of the worksheet from the module, instead of the worksheet itself? I tried Worksheets("SheetName").CheckBoxes in case the ActiveSheet didn't work for some reason, but that didn't work either. Thanks for your continuing help with this. Stephan "Tom Ogilvy" wrote in message ... If you are working with checkboxes in the first column, are they all numbered greater then 99 if not, then num = Right(sName, 3) would not have a number in it and clng(num) would fail. -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to clear checkboxes | 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) | |||
Manipulating 150+ checkboxes with a macro in Excel 2000 | Excel Programming | |||
Multiple checkboxes 1 macro | Excel Programming |