Sign in to follow this  
Followers 0
13lack13lade

Checkbox Criteria

13 posts in this topic

#1 ·  Posted (edited)

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!

post-81238-0-96364400-1386288733_thumb.j

Edited by 13lack13lade

Share this post


Link to post
Share on other sites



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?

Share this post


Link to post
Share on other sites

is something like this along the right lines or is there an easier/better way of doing 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)
$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

Share this post


Link to post
Share on other sites

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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

thanks brewman, alot simpler than i imagined.

greatly appreciated!

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

#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 by 13lack13lade

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by BrewManNH

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

still after some help for this if anyone can assist? :)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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) :)


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0