Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CheckBoxes: How to reset
I'd like to know the VBA code to set all CheckBoxes on a worksheet to false.
I presume a loop would be required, but I can't get one to work. Rob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CheckBoxes: How to reset
There are two kinds of checkboxes that can be put on a worksheet. From the
Forms toolbar and from the Control Toolbox. I imagine you went with the Forms toolbar version so in that case: ActiveSheet.CheckBoxes.Value = False -- Jim "Rob" <none wrote in message ... | I'd like to know the VBA code to set all CheckBoxes on a worksheet to false. | I presume a loop would be required, but I can't get one to work. | | Rob | | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CheckBoxes: How to reset
And if they're from the Control Toolbox toolbar:
Option Explicit Sub auto_open() Dim wks As Worksheet Dim OLEObj As OLEObject Set wks = Worksheets("sheet1") For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj End Sub Rob wrote: I'd like to know the VBA code to set all CheckBoxes on a worksheet to false. I presume a loop would be required, but I can't get one to work. Rob -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
CheckBoxes: How to reset
Thanks Jim,
I should have mentioned they were from the Control Toolbox and Dave has answered to that. Thanks anyway for your input as I'm sure I'll use that down the track. Rob "Jim Rech" wrote in message ... There are two kinds of checkboxes that can be put on a worksheet. From the Forms toolbar and from the Control Toolbox. I imagine you went with the Forms toolbar version so in that case: ActiveSheet.CheckBoxes.Value = False -- Jim "Rob" <none wrote in message ... | I'd like to know the VBA code to set all CheckBoxes on a worksheet to false. | I presume a loop would be required, but I can't get one to work. | | Rob | | |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
CheckBoxes: How to reset
Thanks Dave, but I get a "Subscript out of range" message at the line Set
wks = Worksheets("sheet1"). I note that the procedure says MSForms.CheckBox. Is that right if the CheckBoxes are from the Control Toolbox? Also, does the Sub auto_open() mean that as soon as the sheet is opened this procedure will run? Rob "Dave Peterson" wrote in message ... And if they're from the Control Toolbox toolbar: Option Explicit Sub auto_open() Dim wks As Worksheet Dim OLEObj As OLEObject Set wks = Worksheets("sheet1") For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj End Sub Rob wrote: I'd like to know the VBA code to set all CheckBoxes on a worksheet to false. I presume a loop would be required, but I can't get one to work. Rob -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
CheckBoxes: How to reset
#1. Change Sheet1 to the name of your worksheet that has the checkboxes
or change Set wks = Worksheets("sheet1") to set wks = activesheet If you want to work against the currently activesheet. #2. Yep. MSForms.Checkbox is correct #3. Auto_Open() in a general module will open with the workbook opens (assuming that the user allows macros to run). Rob wrote: Thanks Dave, but I get a "Subscript out of range" message at the line Set wks = Worksheets("sheet1"). I note that the procedure says MSForms.CheckBox. Is that right if the CheckBoxes are from the Control Toolbox? Also, does the Sub auto_open() mean that as soon as the sheet is opened this procedure will run? Rob "Dave Peterson" wrote in message ... And if they're from the Control Toolbox toolbar: Option Explicit Sub auto_open() Dim wks As Worksheet Dim OLEObj As OLEObject Set wks = Worksheets("sheet1") For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj End Sub Rob wrote: I'd like to know the VBA code to set all CheckBoxes on a worksheet to false. I presume a loop would be required, but I can't get one to work. Rob -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
CheckBoxes: How to reset
#3. Auto_Open() in a general module will RUN with the workbook opens (assuming
that the user allows macros to run). Dave Peterson wrote: #1. Change Sheet1 to the name of your worksheet that has the checkboxes or change Set wks = Worksheets("sheet1") to set wks = activesheet If you want to work against the currently activesheet. #2. Yep. MSForms.Checkbox is correct #3. Auto_Open() in a general module will open with the workbook opens (assuming that the user allows macros to run). Rob wrote: Thanks Dave, but I get a "Subscript out of range" message at the line Set wks = Worksheets("sheet1"). I note that the procedure says MSForms.CheckBox. Is that right if the CheckBoxes are from the Control Toolbox? Also, does the Sub auto_open() mean that as soon as the sheet is opened this procedure will run? Rob "Dave Peterson" wrote in message ... And if they're from the Control Toolbox toolbar: Option Explicit Sub auto_open() Dim wks As Worksheet Dim OLEObj As OLEObject Set wks = Worksheets("sheet1") For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj End Sub Rob wrote: I'd like to know the VBA code to set all CheckBoxes on a worksheet to false. I presume a loop would be required, but I can't get one to work. Rob -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
CheckBoxes: How to reset
Thanks again Dave.
I changed "Worksheets("sheet1")" to just Sheet1 and it works fine. This is probably a bit presumptious to ask of you, but I also posted another problem headed Mail Merging: From Filtered data to Word. I don't know if this is in your area of expertise, but I would really value any help I can with this. I was also wondering if using pivot tables somehow would be a solution to that problem. Rob "Dave Peterson" wrote in message ... #1. Change Sheet1 to the name of your worksheet that has the checkboxes or change Set wks = Worksheets("sheet1") to set wks = activesheet If you want to work against the currently activesheet. #2. Yep. MSForms.Checkbox is correct #3. Auto_Open() in a general module will open with the workbook opens (assuming that the user allows macros to run). Rob wrote: Thanks Dave, but I get a "Subscript out of range" message at the line Set wks = Worksheets("sheet1"). I note that the procedure says MSForms.CheckBox. Is that right if the CheckBoxes are from the Control Toolbox? Also, does the Sub auto_open() mean that as soon as the sheet is opened this procedure will run? Rob "Dave Peterson" wrote in message ... And if they're from the Control Toolbox toolbar: Option Explicit Sub auto_open() Dim wks As Worksheet Dim OLEObj As OLEObject Set wks = Worksheets("sheet1") For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj End Sub Rob wrote: I'd like to know the VBA code to set all CheckBoxes on a worksheet to false. I presume a loop would be required, but I can't get one to work. Rob -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
CheckBoxes: How to reset
You used what's called the Codename of that worksheet.
You could have changed the stuff within the double quotes to the name of the worksheet that you see on that worksheet tab (when you're in excel). Rob wrote: Thanks again Dave. I changed "Worksheets("sheet1")" to just Sheet1 and it works fine. This is probably a bit presumptious to ask of you, but I also posted another problem headed Mail Merging: From Filtered data to Word. I don't know if this is in your area of expertise, but I would really value any help I can with this. I was also wondering if using pivot tables somehow would be a solution to that problem. Rob "Dave Peterson" wrote in message ... #1. Change Sheet1 to the name of your worksheet that has the checkboxes or change Set wks = Worksheets("sheet1") to set wks = activesheet If you want to work against the currently activesheet. #2. Yep. MSForms.Checkbox is correct #3. Auto_Open() in a general module will open with the workbook opens (assuming that the user allows macros to run). Rob wrote: Thanks Dave, but I get a "Subscript out of range" message at the line Set wks = Worksheets("sheet1"). I note that the procedure says MSForms.CheckBox. Is that right if the CheckBoxes are from the Control Toolbox? Also, does the Sub auto_open() mean that as soon as the sheet is opened this procedure will run? Rob "Dave Peterson" wrote in message ... And if they're from the Control Toolbox toolbar: Option Explicit Sub auto_open() Dim wks As Worksheet Dim OLEObj As OLEObject Set wks = Worksheets("sheet1") For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj End Sub Rob wrote: I'd like to know the VBA code to set all CheckBoxes on a worksheet to false. I presume a loop would be required, but I can't get one to work. Rob -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
CheckBoxes: How to reset
Yes, you're right! I simply changed ("sheet1") to ("sheet2").....dummy me!!
Rob "Dave Peterson" wrote in message ... You used what's called the Codename of that worksheet. You could have changed the stuff within the double quotes to the name of the worksheet that you see on that worksheet tab (when you're in excel). Rob wrote: Thanks again Dave. I changed "Worksheets("sheet1")" to just Sheet1 and it works fine. This is probably a bit presumptious to ask of you, but I also posted another problem headed Mail Merging: From Filtered data to Word. I don't know if this is in your area of expertise, but I would really value any help I can with this. I was also wondering if using pivot tables somehow would be a solution to that problem. Rob "Dave Peterson" wrote in message ... #1. Change Sheet1 to the name of your worksheet that has the checkboxes or change Set wks = Worksheets("sheet1") to set wks = activesheet If you want to work against the currently activesheet. #2. Yep. MSForms.Checkbox is correct #3. Auto_Open() in a general module will open with the workbook opens (assuming that the user allows macros to run). Rob wrote: Thanks Dave, but I get a "Subscript out of range" message at the line Set wks = Worksheets("sheet1"). I note that the procedure says MSForms.CheckBox. Is that right if the CheckBoxes are from the Control Toolbox? Also, does the Sub auto_open() mean that as soon as the sheet is opened this procedure will run? Rob "Dave Peterson" wrote in message ... And if they're from the Control Toolbox toolbar: Option Explicit Sub auto_open() Dim wks As Worksheet Dim OLEObj As OLEObject Set wks = Worksheets("sheet1") For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.CheckBox Then OLEObj.Object.Value = False End If Next OLEObj End Sub Rob wrote: I'd like to know the VBA code to set all CheckBoxes on a worksheet to false. I presume a loop would be required, but I can't get one to work. Rob -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Based on Checkboxes | Excel Discussion (Misc queries) | |||
Checkboxes vs. Option Buttons | Excel Discussion (Misc queries) | |||
How do I delete checkboxes from rows I deleted in a macro? | Excel Discussion (Misc queries) | |||
ability to put checkboxes into a cell (not just onto) | Excel Discussion (Misc queries) | |||
Autofilter reset button | Excel Discussion (Misc queries) |