Checkbox Criteria

[13lack13lade]

Hey Guys!

I need a little help with changing an excel filter, into a check box GUI the easiest way possible.

As you can see in the picture there is a list of criteria with a Y or N (checked or unchecked) in order to give me the correct code in column A.

What is the easiest way to do this so i have the check boxes for the Y/N Criteria and just a label i guess that changes depending on the result of the criteria.

(basically so users don't have to filter an excel document anymore)

 

Thanks guys!

Edited December 6, 2013 by 13lack13lade

[13lack13lade]

local $SheetName = "Sheet1"
local $total = $oExcel.Worksheets($SheetName).UsedRange.Columns("A:A").Cells.Count$oExcel.Columns("A:A").Select$oExcel.Selection.Autofilter("1",'=')$oExcel.Rows("2:" & $total).Delete  ; change 2: to 1: if you dont want to keep the first row (headers)

Stolen from another post - Best way would be to set up with the above code and the check box linked to the filter with an if statement, if checkbox checked autofilter = "Y" else autofilter = "N" and then have a button you click will will just return the only visable cell in the excel document after filter is applied?

 

Or am i looking at it from the wrong angle?

[13lack13lade]

is something like this along the right lines or is there an easier/better way of doing it? 

expandcollapse popup

#include <Excel.au3>
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 615, 438, 192, 124)
$Checkbox1 = GUICtrlCreateCheckbox("Checkbox1", 40, 16, 25, 17)
$Checkbox2 = GUICtrlCreateCheckbox("Checkbox2", 80, 16, 33, 17)
$Checkbox3 = GUICtrlCreateCheckbox("Checkbox3", 120, 16, 41, 17)
$Checkbox4 = GUICtrlCreateCheckbox("Checkbox4", 168, 16, 25, 17)
$Checkbox5 = GUICtrlCreateCheckbox("Checkbox5", 200, 16, 33, 17)
$Checkbox6 = GUICtrlCreateCheckbox("Checkbox6", 232, 16, 25, 17)
$Checkbox7 = GUICtrlCreateCheckbox("Checkbox7", 272, 16, 41, 17)
$Checkbox8 = GUICtrlCreateCheckbox("Checkbox8", 320, 16, 97, 17)
$Label1 = GUICtrlCreateLabel("Label1", 56, 56, 260, 17)
$Button1 = GUICtrlCreateButton("Button1", 344, 48, 65, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
 Case $GUI_EVENT_CLOSE
    
 Case $button1
    GUICtrlRead $checkbox1
if $checkbox1 = checked then 
$oExcel.Selection.Autofilter("1",'Y')
elseif $checkbox1 = unchecked then
$oExcel.Selection.Autofilter("1",'N')

    
            Exit

    EndSwitch
WEnd

[BrewManNH]

Here's how I'd do it.

#include <Excel.au3>
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 615, 438, 192, 124)
Global $Checkbox[8]
For $I = 0 To 7
    $Checkbox[$I] = GUICtrlCreateCheckbox("Checkbox" & $I + 1, 10 + (75 * $I), 16)
Next
$Label1 = GUICtrlCreateLabel("Label1", 56, 56, 260, 17)
$Button1 = GUICtrlCreateButton("Button1", 344, 48, 65, 25)
GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            For $I = 0 To 7
                If GUICtrlRead($Checkbox[$I]) = $GUI_CHECKED Then
                    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : CHECKED = ' & "CHECKED" & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
;~                  $oExcel.Selection.Autofilter("1", 'Y')
                Else
                    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : CHECKED = ' & "UNCHECKED" & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
;~                  $oExcel.Selection.Autofilter("1", 'N')
                EndIf
            Next
    EndSwitch
WEnd

Currently this only prints to the output pane in Scite whether the checkbox is checked or not, easily adapted to doing what you want with it.

[13lack13lade]

thanks brewman, alot simpler than i imagined.

greatly appreciated!

[13lack13lade]

wasnt sure how to include your loop into looping through the excel columns matching criteria, got it filtering now though just has alot of code rather than looping! lol

expandcollapse popup

#include <Excel.au3>
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 680, 438, 192, 124)
$Checkbox = GUICtrlCreateCheckbox("Checkbox", 1, 10, 16)
$Checkbox2 = GUICtrlCreateCheckbox("Checkbox", 30, 10, 16)
$Checkbox3 = GUICtrlCreateCheckbox("Checkbox", 60, 10, 16)
$Checkbox4 = GUICtrlCreateCheckbox("Checkbox", 90, 10, 16)
$Checkbox5 = GUICtrlCreateCheckbox("Checkbox", 120, 10, 16)
$Checkbox6 = GUICtrlCreateCheckbox("Checkbox", 150, 10, 16)
$Checkbox7 = GUICtrlCreateCheckbox("Checkbox", 180, 10, 16)
$Checkbox8 = GUICtrlCreateCheckbox("Checkbox", 210, 10, 16)
$Checkbox9 = GUICtrlCreateCheckbox("Checkbox", 240, 10, 16)
$Checkbox10 = GUICtrlCreateCheckbox("Checkbox", 270, 10, 16)
$Checkbox11 = GUICtrlCreateCheckbox("Checkbox", 300, 10, 16)
$Checkbox12 = GUICtrlCreateCheckbox("Checkbox", 330, 10, 16)
$Checkbox13 = GUICtrlCreateCheckbox("Checkbox", 360, 10, 16)
$Checkbox14 = GUICtrlCreateCheckbox("Checkbox", 390, 10, 16)
$Checkbox15 = GUICtrlCreateCheckbox("Checkbox", 420, 10, 16)
$Checkbox16 = GUICtrlCreateCheckbox("Checkbox", 450, 10, 16)
$Checkbox17 = GUICtrlCreateCheckbox("Checkbox", 480, 10, 16)
$Checkbox18 = GUICtrlCreateCheckbox("Checkbox", 510, 10, 16)
$Checkbox19 = GUICtrlCreateCheckbox("Checkbox", 540, 10, 16)
$Checkbox20 = GUICtrlCreateCheckbox("Checkbox", 570, 10, 16)
$Checkbox21 = GUICtrlCreateCheckbox("Checkbox", 600, 10, 16)
$Checkbox22 = GUICtrlCreateCheckbox("Checkbox", 630, 10, 16)
$Label1 = GUICtrlCreateLabel("Label1", 56, 56, 260, 17)
$Button1 = GUICtrlCreateButton("Button1", 344, 48, 65, 25)
GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

$oExcel = _ExcelBookOpen("\\fbnecl3\inzb\Documents\Load Support\Tom\Projects\Current\Market Schedule Code List.xlsx",1)
$oExcel.Sheets("ASIA" ).Select


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
 ;          For $I = 0 To 7
                If GUICtrlRead($Checkbox) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(2, "Y")                
                Else
                  $oExcel.Selection.Autofilter(2, "N")
               EndIf
               
               If GUICtrlRead($Checkbox2) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(3, "Y")                
                Else
                  $oExcel.Selection.Autofilter(3, "N")
               EndIf
               
               If GUICtrlRead($Checkbox3) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(4, "Y")                
                Else
                  $oExcel.Selection.Autofilter(4, "N")
               EndIf
               
               If GUICtrlRead($Checkbox4) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(5, "Y")                
                Else
                  $oExcel.Selection.Autofilter(5, "N")
               EndIf
               
               If GUICtrlRead($Checkbox5) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(6, "Y")                
                Else
                  $oExcel.Selection.Autofilter(6, "N")
               EndIf
               
               If GUICtrlRead($Checkbox6) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(7, "Y")                
                Else
                  $oExcel.Selection.Autofilter(7, "N")
               EndIf
               
               If GUICtrlRead($Checkbox7) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(8, "Y")                
                Else
                  $oExcel.Selection.Autofilter(8, "N")
               EndIf
               
               If GUICtrlRead($Checkbox8) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(9, "Y")                
                Else
                  $oExcel.Selection.Autofilter(9, "N")
               EndIf
               
               If GUICtrlRead($Checkbox9) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(10, "Y")               
                Else
                  $oExcel.Selection.Autofilter(10, "N")
               EndIf
               
               If GUICtrlRead($Checkbox10) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(11, "Y")               
                Else
                  $oExcel.Selection.Autofilter(11, "N")
               EndIf
               
               If GUICtrlRead($Checkbox11) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(12, "Y")               
                Else
                  $oExcel.Selection.Autofilter(12, "N")
               EndIf
               
               If GUICtrlRead($Checkbox12) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(13, "Y")               
                Else
                  $oExcel.Selection.Autofilter(13, "N")
               EndIf
               
               If GUICtrlRead($Checkbox13) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(14, "Y")               
                Else
                  $oExcel.Selection.Autofilter(14, "N")
               EndIf
               
               If GUICtrlRead($Checkbox14) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(15, "Y")               
                Else
                  $oExcel.Selection.Autofilter(15, "N")
               EndIf
               
               If GUICtrlRead($Checkbox15) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(16, "Y")               
                Else
                  $oExcel.Selection.Autofilter(16, "N")
               EndIf
               
               If GUICtrlRead($Checkbox16) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(17, "Y")               
                Else
                  $oExcel.Selection.Autofilter(17, "N")
               EndIf
               
               If GUICtrlRead($Checkbox17) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(18, "Y")               
                Else
                  $oExcel.Selection.Autofilter(18, "N")
               EndIf
               
               If GUICtrlRead($Checkbox18) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(19, "Y")               
                Else
                  $oExcel.Selection.Autofilter(19, "N")
               EndIf
               
               If GUICtrlRead($Checkbox19) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(20, "Y")               
                Else
                  $oExcel.Selection.Autofilter(20, "N")
               EndIf
               
               If GUICtrlRead($Checkbox20) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(21, "Y")               
                Else
                  $oExcel.Selection.Autofilter(21, "N")
               EndIf
               
               If GUICtrlRead($Checkbox21) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(22, "Y")               
                Else
                  $oExcel.Selection.Autofilter(22, "N")
               EndIf
               
               If GUICtrlRead($Checkbox22) = $GUI_CHECKED Then
                  $oExcel.Selection.Autofilter(23, "Y")               
                Else
                  $oExcel.Selection.Autofilter(23, "N")
               EndIf
               

               
 ;           Next
    EndSwitch
WEnd

Now i need to determine what the specific code is (this comes from column A in the autofilter result) or in other words i need to return the last visable cell value in column A, then i can change the text of my label to the result.

anyone able to provide me with code on _excelreadcell( last visable cell in column a? )

Edited December 12, 2013 by 13lack13lade

[BrewManNH]

My script example will easily deal with removing 70% of the code you wrote in post 6. Instead of using the consolewrites in my example, replace it with this and get rid of that long redundant, and unnecessary, code.

If GUICtrlRead($Checkbox[$I]) = $GUI_CHECKED Then
    $oExcel.Selection.Autofilter($I, "Y")               
Else
    $oExcel.Selection.Autofilter($I, "N")
EndIf

This uses the same looping method, and replaces a LOT of the comparison code.

Edited December 12, 2013 by BrewManNH

[13lack13lade]

Ahh so i did have it coded right when i did that, i dont know what else i did that made it not work, will change it back.

Thanks brewman

How can i find the last visable cell in column a?

[BrewManNH]

I'd look at the Excel functions, or water's rewrite of it, I don't automate Excel so I have no idea.

[13lack13lade]

Okay i have done some more research and i think ive found all the bits of code i need however im having some trouble trying join them all together correctly to get what i want. Can anyone help me join these bits of code together to ultimate get what im trying to do?

This code controls the autofilter - 

#include <Excel.au3>
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 615, 438, 192, 124)
Global $Checkbox[8]
For $I = 0 To 7
    $Checkbox[$I] = GUICtrlCreateCheckbox("Checkbox" & $I + 1, 10 + (75 * $I), 16)
Next
$Label1 = GUICtrlCreateLabel("Label1", 56, 56, 260, 17)
$Button1 = GUICtrlCreateButton("Button1", 344, 48, 65, 25)
GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

$oExcel = _ExcelBookOpen("\\fbnecl3\inzb\Documents\Load Support\Tom\Projects\Current\Market Schedule Code List.xlsx",1)
$oExcel.Sheets("ASIA" ).Select
$oExcel.Range("B4:X4").Select

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            For $I = 0 To 7
If GUICtrlRead($Checkbox[$I]) = $GUI_CHECKED Then
    $oExcel.Selection.Autofilter($I, "Y")               
Else
    $oExcel.Selection.Autofilter($I, "N")
EndIf
            Next
    EndSwitch
WEnd

This is the code ive found to find the visible cell in column A or the filtered data i am pretty sure what im needing is in this bit of code but im not sure how to change it to get it to do what i want:(

Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
; Extract integer last row and col
$sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
Local $iLastRow = $sLastCell[0]
Local $iLastColumn = $sLastCell[1]

ConsoleWrite($iLastRow & ' ' & $iLastColumn & @LF)

for $1 = 2 to $iLastRow
    if $oExcel.Sheets("Todays Data").Rows($1).Hidden = True then
    Else
        ConsoleWrite($oExcel.Activesheet.Cells($1,1).Value & @LF)
    EndIf

_ExcelBookClose($oExcel, 0, 0)

So instead of the last line - ConsoleWrite($oExcel.Activesheet.Cells($1,1).Value & @LF) that would need to be GUISetControl ( $label1, $slastcell?, left, top, width, height)

[13lack13lade]

still after some help for this if anyone can assist?

[13lack13lade]

still need help if theres any autoit to excel gurus out there!

[BrewManNH]

You should probably split the Excel part of your thread from the checkbox question you started with as the title doesn't match your problem now. Start a new thread with the relevant information and what you need instead of bumping a non-related thread (non-related in that it doesn't have anything to do about checkboxes now)