Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trending checkboxes?
Hi, I have two sheets, one's a check sheet with 11 check boxes in it an the other is for trending the results each time the checksheet is used I want to copy over the results of the check sheet (i.e if the checkbo is checked or not) over to the other page by adding true or false fo each question on the trending sheet. I was trying to loop through the checkboxes but I couldn't get that t work. Can someone please help me? Thanks in advance Trevo -- Tre_coo ----------------------------------------------------------------------- Tre_cool's Profile: http://www.excelforum.com/member.php...fo&userid=2641 View this thread: http://www.excelforum.com/showthread.php?threadid=50785 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trending checkboxes?
if the are from the forms toolbar
for each c in worksheets("sheet2").Checkboxes if from the control toolbox toolbar Dim cBox as MSForms.CheckBox Dim obj as OleObject for each obj in Worksheets("Sheet2").OleObjects if Typeof Obj.Object is MSForms.Checkbox then set cBox = Obj.Object msgbox cBox.Name & " - " & cBox.Value end if Next If you have use names like cb1, cb2 Dim cb as MsForms.Checkbox for i = 1 to 11 set cb = Activesheet.OleObjects("cb" & i).Object msgbox cb.Name & " - " & cb.Value Next -- Regards, Tom Ogilvy "Tre_cool" wrote in message ... Hi, I have two sheets, one's a check sheet with 11 check boxes in it and the other is for trending the results each time the checksheet is used. I want to copy over the results of the check sheet (i.e if the checkbox is checked or not) over to the other page by adding true or false for each question on the trending sheet. I was trying to loop through the checkboxes but I couldn't get that to work. Can someone please help me? Thanks in advance Trevor -- Tre_cool ------------------------------------------------------------------------ Tre_cool's Profile: http://www.excelforum.com/member.php...o&userid=26416 View this thread: http://www.excelforum.com/showthread...hreadid=507852 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trending checkboxes?
Hello Trevor, Here is macro for the Forms type Check Box. You can modify this easily to fit your code. There are three things you need to change. The name of the Trend worksheet, in this example it is "Sheet2". You will need to change the cell addresses where the Check Box state will be stored. Here it is set for "C1", "C2", "C3". Lastly, you will need to add mose Case statements to match the captions of the Check Boxes. After you have made the changes and saved the code into a VBA module, you can assign the macro to your Check Boxes. When they change so will the result in the answer cell to either TRUE or FALSE. Code: -------------------- Public Sub GetCheckBoxState() Dim Check_Box As Object Dim Check_Box_Name As String Dim CurrentState As Boolean Dim TrendWks As Worksheet Set Check_Box = ActiveSheet.Shapes(Application.Caller) Set TrendWks = Worksheets("Sheet2") If Check_Box.Type < msoFormControl Then Exit Sub If Check_Box.FormControlType = xlCheckBox Then Check_Box_Name = Check_Box.TextFrame.Characters.Text Select Case Check_Box_Name Case Is = "Answer 1" GoSub ReturnState: TrendWks.Range("C1").Value = CurrentState Case Is = "Answer 2" GoSub ReturnState: TrendWks.Range("C2").Value = CurrentState Case Is = "Answer 3" GoSub ReturnState: TrendWks.Range("C3").Value = CurrentState End Select End If Exit Sub ReturnState: CurrentState = False If Check_Box.ControlFormat.Value = 1 Then CurrentState = True Return End Sub -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=507852 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trending checkboxes?
Thanks guys...that worked great! -- Tre_cool ------------------------------------------------------------------------ Tre_cool's Profile: http://www.excelforum.com/member.php...o&userid=26416 View this thread: http://www.excelforum.com/showthread...hreadid=507852 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trending Function? | Excel Worksheet Functions | |||
Trending Methodology | Excel Discussion (Misc queries) | |||
polynomial trending | Charts and Charting in Excel | |||
Trending data | Excel Discussion (Misc queries) | |||
Series Trending | Excel Programming |