Extract input to an Excel file

KickStarter15 · January 4, 2018

Hi Experts,

A while ago, I have this problem on how to create multiple checkboxes within TabControl function. Gladly, I made it happened (of course with the help of our forum friends) but not that good coding, it's kind of messy something (nee to work on this).

Now, my problem is how can I extract these inputs to an existing Excel file (which is the Sample.xlsx) when clicking the button "Submit Report" or should we say to a new Excel file which is more easier I think o:) . I have attached my sample Excel and the code is:

#include <Excel.au3>
#include <GUIConstants.au3>
#include <TabConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include "GUIScrollbars_Ex.au3"
#include <ColorConstants.au3>
#include <WindowsConstants.au3>
#Include <Array.au3>
#include <File.au3>

#Region ### START Koda GUI section ### Form=
Opt("GUIOnEventMode", 1)
HotKeySet("{ESC}", "_Exit")
Global $Form1 = GUICreate("Test Form", 820, 740, 265, 50)
GUISetBkColor(0x38A7D2)
GUISetOnEvent($GUI_EVENT_CLOSE, "_Exit")
$Pic1 = GUICtrlCreatePic(@ScriptDir & "\Image.jpg", 690, 15, 120, 120)
$Label10 = GUICtrlCreateLabel("Welcome: "&GetFullName(@UserName), 17, 130, 500, 16)
;~ GUICtrlSetFont(-1, 10, 400, 0, "Arial Rounded MT Bold")
GUICtrlSetColor($Label10, 0xFFFFFF2)
GUICtrlSetFont($Label10, 9, 700)
$File = GUICtrlCreateMenu("File")
GUICtrlCreateMenuItem("Close", $File)
GUICtrlCreateMenuItem("Export", $File)
$Edit = GUICtrlCreateMenu("Edit")
GUICtrlSetState($Edit, $GUI_Disable)
$Option = GUICtrlCreateMenu("Option")
GUICtrlSetState($Option, $GUI_Disable)
$Menu = GUICtrlCreateMenu("View")
GUICtrlCreateMenuItem("Exit", $Menu)
GUICtrlSetState($Menu, $GUI_Disable)
$Help = GUICtrlCreateMenu("Help")
GUICtrlCreateMenuItem("About", $Help)
$dEmployeInfo = GUICtrlCreateGroup("Employee Information", 16, 8, 281, 121)
$dEmpNbr = GUICtrlCreateLabel("Emp#:", 33, 35, 35, 17)
$dEmpNme = GUICtrlCreateLabel("Name:", 33, 59, 35, 17)
$dEmpPrs = GUICtrlCreateLabel("Process:", 24, 83, 45, 17)
$EmpNbrInput = GUICtrlCreateInput("1234567", 72, 32, 217, 21)
GUICtrlSetState($EmpNbrInput, $GUI_Disable)
$EmpNmeInput = GUICtrlCreateInput(GetFullName(@UserName), 72, 56, 217, 21)
GUICtrlSetState($EmpNmeInput, $GUI_Disable)
$EmpPrsInput = GUICtrlCreateInput("First Process", 72, 80, 217, 21)
GUICtrlSetState($EmpPrsInput, $GUI_Disable)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$AID = GUICtrlCreateLabel("AID:", 10, 170, 25, 17)
GUICtrlSetFont($AID, 10, 700)
$AIDLabel = GUICtrlCreateLabel("ABZ123456", 40, 170, 90, 17)
GUICtrlSetColor($AIDLabel, 0xFFFFFF)
GUICtrlSetFont($AIDLabel, 10, 700)
$Submit = GUICtrlCreateButton("Submit Report",  635, 672, 177, 33)
GUICtrlSetOnEvent(-1, "Submit")
$Checkbox1 = GUICtrlCreateCheckbox("I agree that I have checked the entry/s above.", 16, 659, 265, 17)
$Checkbox2 = GUICtrlCreateCheckbox("Accept", 16, 677, 50, 17)
$Checkbox3 = GUICtrlCreateCheckbox("Reject", 16, 695, 50, 17)
$LabelASTR = GUICtrlCreateLabel("*", 72, 680, 40, 17)
GUICtrlSetColor($LabelASTR, 0xFF0000)
GUICtrlSetFont($LabelASTR, 10, 700)
$LabelASTR1 = GUICtrlCreateLabel("*", 70, 695, 40, 17)
GUICtrlSetColor($LabelASTR1, 0xFF0000)
GUICtrlSetFont($LabelASTR1, 10, 700)
$tab = GUICtrlCreateTab(10, 190, 780, 455, $WS_EX_MDICHILD)
GUICtrlSetOnEvent(-1, "_Tab")
GUICtrlCreateTabItem("Front Matter")
GUICtrlSetState(-1, $GUI_SHOW)
GUICtrlCreateTabItem("Body Matter")
GUICtrlCreateTabItem("Back Matter")
Local $Number = 60, $iRows = 60, $iSpacing = 60
Local $CboxCheck[$Number]
Local $CboxNA[$Number]
Local $CboxNerror[$Number]
Local $CboxWerror[$Number]
Local $Edit[$Number]
Local $eElement[$Number]
Global $hTab_Win0, $hTab_Win1, $hTab_Win2

GUICtrlCreateTabItem("")
$hTab_Win0 = GUICreate("", 780, 435, 10, 210, $WS_POPUP, $WS_EX_MDICHILD, $Form1)
GUISetBkColor(0xFFFFFF)
$GroupElement = GUICtrlCreateGroup("", 40, 10, 73, 30)
$LabelElement = GUICtrlCreateLabel("ENTRY/s", 50, 20, 62, 17)
$GroupNA = GUICtrlCreateGroup("", 200, 10, 73, 30)
$LabelNA = GUICtrlCreateLabel("N/A", 225, 20, 40, 17)
$GroupNerror = GUICtrlCreateGroup("", 292, 10, 70, 30)
$LabelNerror = GUICtrlCreateLabel("NO ERROR", 299, 20, 60, 17)
$GroupWError = GUICtrlCreateGroup("", 380, 10, 80, 30)
$LabelWError = GUICtrlCreateLabel("WITH ERROR", 385, 20, 73, 15)
$GroupRemark = GUICtrlCreateGroup("", 580, 10, 95, 30)
$LabelRemark = GUICtrlCreateLabel("REMARKS", 600, 20, 65, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)

; Reading excel file column A for tab 1 "Part I"
$sPath = @ScriptDir & "\Sample.xlsx"
$oExcel = ObjCreate("Excel.Application")
$oWorkbook = _Excel_BookOpen($oExcel, $sPath)
If @error Then Exit
$aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)
_Excel_Close($oExcel, False, True)
   For $i = 0 To $Number - 1
        $eElement[$i] = GUICtrlCreateInput("", $iSpacing + -50 + (Int($i / $iRows)), ((30.7 * Mod($i, $iRows)) + $iSpacing), 155, 20, BitOR($GUI_SS_DEFAULT_INPUT,$ES_READONLY))
        GUICtrlSetState($eElement[$i], $GUI_Enable)
        GUICtrlSetData($eElement[$i], $aResult[$i])
        _Excel_Close($oExcel, False, True)
   Next
   For $i = 0 To $Number - 1
        $CboxNA[$i] = GUICtrlCreateCheckbox("", $iSpacing + 165 + (Int($i / $iRows)), ((30.7 * Mod($i, $iRows)) + $iSpacing), 15, 20)
    Next
    For $j = 0 To $Number - 1
        $CboxNerror[$j] = GUICtrlCreateCheckbox("", $iSpacing + 260 + (Int($j / $iRows)), ((30.7 * Mod($j, $iRows)) + $iSpacing), 15, 20)
    Next
    For $x = 0 To $Number - 1
        $CboxWerror[$x] = GUICtrlCreateCheckbox("", $iSpacing + 355 + (Int($x / $iRows)), ((30.7 * Mod($x, $iRows)) + $iSpacing), 15, 20)
    Next
    For $i = 0 To $Number - 1
        $Edit[$i] = GUICtrlCreateEdit("", $iSpacing + 440 + (Int($i / $iRows)), ((30.7 * Mod($i, $iRows)) + $iSpacing), 240, 24, BitOR($ES_AUTOVSCROLL, $ES_WANTRETURN)) ;
        GUICtrlSetState($Edit[$i], $GUI_Enable)
    Next
_GUIScrollbars_Generate($hTab_Win0, 0, 2000)

GUISetState(@SW_SHOW, $Form1)
GUISetState(@SW_SHOW, $hTab_Win0)
_Excel_Close($oExcel, False, True)
; Close Tab definiton
#EndRegion ### END Koda GUI section ###

Sleep(20)
While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
      Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd

Func _Tab()
    Switch GUICtrlRead($tab)
        Case 0
            GUISetState(@SW_SHOW, $hTab_Win0)
            GUISetState(@SW_HIDE, $hTab_Win1)
            GUISetState(@SW_HIDE, $hTab_Win2)
        Case 1
            GUISetState(@SW_HIDE, $hTab_Win0)
            GUISetState(@SW_SHOW, $hTab_Win1)
            _TabA()
            GUISetState(@SW_HIDE, $hTab_Win2)
        Case 2
            GUISetState(@SW_HIDE, $hTab_Win0)
            GUISetState(@SW_HIDE, $hTab_Win1)
            GUISetState(@SW_SHOW, $hTab_Win2)
            _TabB()
    EndSwitch
EndFunc   ;==>_Tab
Func _TabA()
GUICtrlCreateTabItem("")
$hTab_Win1 = GUICreate("", 780, 435, 10, 210, $WS_POPUP, $WS_EX_MDICHILD, $Form1)
GUISetBkColor(0xFFFFFF)
$GroupElement = GUICtrlCreateGroup("", 40, 10, 73, 30)
$LabelElement = GUICtrlCreateLabel("ENTRY/s", 50, 20, 62, 17)
$GroupNA = GUICtrlCreateGroup("", 200, 10, 73, 30)
$LabelNA = GUICtrlCreateLabel("N/A", 225, 20, 40, 17)
$GroupNerror = GUICtrlCreateGroup("", 292, 10, 70, 30)
$LabelNerror = GUICtrlCreateLabel("NO ERROR", 299, 20, 60, 17)
$GroupWError = GUICtrlCreateGroup("", 380, 10, 80, 30)
$LabelWError = GUICtrlCreateLabel("WITH ERROR", 385, 20, 73, 15)
$GroupRemark = GUICtrlCreateGroup("", 580, 10, 95, 30)
$LabelRemark = GUICtrlCreateLabel("REMARKS", 600, 20, 65, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)

$sPath = @ScriptDir & "\Sample.xlsx"
$oExcel = ObjCreate("Excel.Application")
$oWorkbook = _Excel_BookOpen($oExcel, $sPath)
If @error Then Exit
$aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("B:B"), 2)
_Excel_Close($oExcel, False, True)
   For $i = 0 To $Number - 1
        $eElement[$i] = GUICtrlCreateInput("", $iSpacing + -50 + (Int($i / $iRows)), ((30.7 * Mod($i, $iRows)) + $iSpacing), 155, 20, BitOR($GUI_SS_DEFAULT_INPUT,$ES_READONLY))
        GUICtrlSetState($eElement[$i], $GUI_Enable)
        GUICtrlSetData($eElement[$i], $aResult[$i])
        _Excel_Close($oExcel, False, True)
   Next
   For $i = 0 To $Number - 1
        $CboxNA[$i] = GUICtrlCreateCheckbox("", $iSpacing + 165 + (Int($i / $iRows)), ((30.7 * Mod($i, $iRows)) + $iSpacing), 15, 20)
    Next
    For $j = 0 To $Number - 1
        $CboxNerror[$j] = GUICtrlCreateCheckbox("", $iSpacing + 260 + (Int($j / $iRows)), ((30.7 * Mod($j, $iRows)) + $iSpacing), 15, 20)
    Next
    For $x = 0 To $Number - 1
        $CboxWerror[$x] = GUICtrlCreateCheckbox("", $iSpacing + 355 + (Int($x / $iRows)), ((30.7 * Mod($x, $iRows)) + $iSpacing), 15, 20)
    Next
    For $i = 0 To $Number - 1
        $Edit[$i] = GUICtrlCreateEdit("", $iSpacing + 440 + (Int($i / $iRows)), ((30.7 * Mod($i, $iRows)) + $iSpacing), 240, 24, BitOR($ES_AUTOVSCROLL, $ES_WANTRETURN)) ;
        GUICtrlSetState($Edit[$i], $GUI_Enable)
    Next
_GUIScrollbars_Generate($hTab_Win1, 0, 2000)

GUISetState(@SW_SHOW, $Form1)
GUISetState(@SW_SHOW, $hTab_Win1)
_Excel_Close($oExcel, False, True)
EndFunc
Func _TabB()
GUICtrlCreateTabItem("")
$hTab_Win2 = GUICreate("", 780, 435, 10, 210, $WS_POPUP, $WS_EX_MDICHILD, $Form1)
GUISetBkColor(0xFFFFFF)
$GroupElement = GUICtrlCreateGroup("", 40, 10, 73, 30)
$LabelElement = GUICtrlCreateLabel("ENTRY/s", 50, 20, 62, 17)
$GroupNA = GUICtrlCreateGroup("", 200, 10, 73, 30)
$LabelNA = GUICtrlCreateLabel("N/A", 225, 20, 40, 17)
$GroupNerror = GUICtrlCreateGroup("", 292, 10, 70, 30)
$LabelNerror = GUICtrlCreateLabel("NO ERROR", 299, 20, 60, 17)
$GroupWError = GUICtrlCreateGroup("", 380, 10, 80, 30)
$LabelWError = GUICtrlCreateLabel("WITH ERROR", 385, 20, 73, 15)
$GroupRemark = GUICtrlCreateGroup("", 580, 10, 95, 30)
$LabelRemark = GUICtrlCreateLabel("REMARKS", 600, 20, 65, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)

$sPath = @ScriptDir & "\Sample.xlsx"
$oExcel = ObjCreate("Excel.Application")
$oWorkbook = _Excel_BookOpen($oExcel, $sPath)
If @error Then Exit
$aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("C:C"), 2)
_Excel_Close($oExcel, False, True)
   For $i = 0 To $Number - 1
        $eElement[$i] = GUICtrlCreateInput("", $iSpacing + -50 + (Int($i / $iRows)), ((30.7 * Mod($i, $iRows)) + $iSpacing), 155, 20, BitOR($GUI_SS_DEFAULT_INPUT,$ES_READONLY))
        GUICtrlSetState($eElement[$i], $GUI_Enable)
        GUICtrlSetData($eElement[$i], $aResult[$i])
        _Excel_Close($oExcel, False, True)
   Next
   For $i = 0 To $Number - 1
        $CboxNA[$i] = GUICtrlCreateCheckbox("", $iSpacing + 165 + (Int($i / $iRows)), ((30.7 * Mod($i, $iRows)) + $iSpacing), 15, 20)
    Next
    For $j = 0 To $Number - 1
        $CboxNerror[$j] = GUICtrlCreateCheckbox("", $iSpacing + 260 + (Int($j / $iRows)), ((30.7 * Mod($j, $iRows)) + $iSpacing), 15, 20)
    Next
    For $x = 0 To $Number - 1
        $CboxWerror[$x] = GUICtrlCreateCheckbox("", $iSpacing + 355 + (Int($x / $iRows)), ((30.7 * Mod($x, $iRows)) + $iSpacing), 15, 20)
    Next
    For $i = 0 To $Number - 1
        $Edit[$i] = GUICtrlCreateEdit("", $iSpacing + 440 + (Int($i / $iRows)), ((30.7 * Mod($i, $iRows)) + $iSpacing), 240, 24, BitOR($ES_AUTOVSCROLL, $ES_WANTRETURN)) ;
        GUICtrlSetState($Edit[$i], $GUI_Enable)
    Next
_GUIScrollbars_Generate($hTab_Win2, 0, 2000)

GUISetState(@SW_SHOW, $Form1)
GUISetState(@SW_SHOW, $hTab_Win2)
_Excel_Close($oExcel, False, True)
EndFunc
Func _Exit()
    Exit
EndFunc   ;==>_Exit
Func Submit()
    MsgBox(16, "Warning!", "This is still on-going")
EndFunc
Func GetFullName($sUserName)
    $colItems = ""
    $strComputer = "localhost"

    $objWMIService = ObjGet("winmgmts:\\" & $strComputer & "\root\CIMV2")
    $colItems = $objWMIService.ExecQuery("SELECT * FROM Win32_UserAccount WHERE Name = '" & $sUserName &  "'", "WQL", 0x10 + 0x20)

    If IsObj($colItems) then
       For $objItem In $colItems
          Return $objItem.FullName
       Next
    Else
       Return SetError(1,0,"")
    Endif
EndFunc

Hope someone here can help me with this. Kind of hard thing to do in my status right now.

Thank you in advance Experts!

KS15

Sample.xlsx

Edited January 4, 2018 by KickStarter15

benners · January 4, 2018

If you swap this function with the one in your script it will write the input values to cells D1 to D3

Func Submit()
    $oExcel = _Excel_Open(False)
    $oWorkbook = _Excel_BookOpen($oExcel, $sPath)
    If @error Then return SetError(1)

    Local $ai_Labels[4] = [3, $EmpNbrInput, $EmpNmeInput, $EmpPrsInput]

    For $i = 1 to $ai_Labels[0]
        _Excel_RangeWrite($oWorkbook, Default, GUICtrlRead($ai_Labels[$i]), "D" & $i)
    Next

    _Excel_Close($oExcel)

    MsgBox(16, "Warning!", "This is still on-going")
    shellexecute($sPath)
EndFunc   ;==>Submit

Edited January 4, 2018 by benners
water's advice below

water · January 4, 2018

Please replace

$oExcel = ObjCreate("Excel.Application")

with

$oExcel = _Excel_Open()

This makes sure that _Excel_Close works properly.

KickStarter15 · January 5, 2018

@benners,

Thanks, it's working indeed for the 3 inputs "$EmpNbrInput, $EmpNmeInput, $EmpPrsInput" but please can you guide me on how should I add these items from my GUICtrlCreateTabItem() to an excel cell? It's like my GUICtrlCreateCheckbox() are in Array and no idea how to handle this case so that I can write these items in my Excel.

For example TabItem() "Front Matter" there are items that were checked and remarked and I need that input to be extracted to my Excel cell.

@water,

Thanks as well and very noted.

benners · January 5, 2018

You create the create the checkboxes in an array but the array is overwritten each time you build the tab so there are no IDs for the controls. If there were you could loop through them and get the values as I did in the submit button. There's no need to rebuild the controls when you change tabs, as far as I can see, so you could just have a 2D array with enough rows for the controls and enough columns for the number of tabs.

The other thing is if the report is going to be read by humans or is just for saving. If it's going to be read by humans then you will have to change the values you get from reading the checkboxes to values that everyone knows, such as with checkboxes. They return 1, 2 or 4. If you saw that in the report you wouldn't know what it meant Does it need to be recorded as yes/no , for instance with the I agree\accept or reject boxes?.

Before going any further I would look at streamlining the code to improve it and increase the speed and make it easier to add your values to the excel sheet. There are a lot of repeated actions that could be made into functions and variables created (when controls are added) that don't need to be. Normally, if I am not going to reference or change the controls value, such as a labels text, I don't assign it a variable. I would also use a variable naming convention, such as the one listed here

I'll have a play with the code if you want and give you some examples and people cleverer than I can improve it

benners · January 6, 2018

Well. been playing all day and have come up with this. It just draws the gui and sets up the control arrays for later. One annoying problem to sort out when drawing the controls,. It will only create the same amount of controls as there are cells with text in the excel file. This way redundant controls aren't added

#include <Array.au3>
#include <ColorConstants.au3>
#include <EditConstants.au3>
#include <Excel.au3>
#include <FontConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIScrollbars_Ex.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>

Opt("GUIOnEventMode", 1)
HotKeySet("{ESC}", "CloseProgram")

; create the arrays to hold the controlIDs for the tab guis. These can be referenced
; later when writing their values to the excel sheet
Global _
        $aid_FrontMatterGUI = '', _
        $aid_BodyMatterGUI = '', _
        $aid_BackMatterGUI = ''

; theee are integers that relate to where the controliDs are located in $g_aidControls.
; The control ID can be retrieved like so, $g_aidControls[$EMPPROCESS_INP]
Global Enum _
        $FRONTMATTERGUI, _
        $BODYMATTERGUI, _
        $BACKMATTERGUI, _
        $EMPNUMBER_INP, _
        $EMPNAME_INP, _
        $EMPPROCESS_INP, _
        $MYSTERY_LBL, _
        $AGREE_CHK, _
        $ACCEPT_CHK, _
        $REJECT_CHK, _
        $SUBMIT_BTN, _
        $MAIN_TAB, _
        $ARRAYMAX

; create the array to hold the remaining controlIDs. These can be referenced
; later when writing their values to the excel sheet
Global $g_aidControls[$ARRAYMAX]

; set the path to the file that has the values for the inputs
Global $g_sExcelFile = @ScriptDir & "\Sample.xlsx"

Draw_GUI()

;~ _ArrayDisplay($g_aidControls)

While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch

    Sleep(50)
WEnd

Func CloseProgram()
    Exit
EndFunc   ;==>CloseProgram

Func CreateTabGUIs(ByRef $as_RangeRead, $i_ArrayRow, $h_MainGUI)
    ; create the gui for the controls and add it to the global controls array
    $g_aidControls[$i_ArrayRow] = GUICreate("", 770, 420, 12, 220, $WS_POPUP, $WS_EX_MDICHILD, $h_MainGUI)
    GUISetBkColor($COLOR_WHITE)

    Local $i_Columns = 5
    ; create the temp array
    Local $aid_Temp[61][$i_Columns] = [[60]]

    ; Create the controls
    GUICtrlCreateLabel("ENTRY(S)", 50, 20, 80, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel("N/A", 212, 20, 40, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel("NO ERROR", 291, 20, 70, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel("WITH ERROR", 383, 20, 85, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel("REMARKS", 570, 20, 90, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))

    Local $s_Value = ''
    Local _
            $i_ArrayRows = 0, _
            $i_Left = 0, _
            $i_Rows = 60, _
            $i_Spacing = 60, _
            $i_Top = 0

    For $i = 1 To $aid_Temp[0][0] ; 1 to 60 rows of controls
        ; get the value to add to the control
        $s_Value = $as_RangeRead[$i - 1][$i_ArrayRow]

        ; don't create anymore controls if the value is blanks
        ; ### comment this line and it will create empty controls on the gui ###
        ; ### and for some reason a blank one at the top which I can't figure out why ###
        ; ### if you use $i as the value it is always the last one such as 60 of 60 but it is the first input GRRRRRRR ####
        If Not $s_Value Then ExitLoop

        ; calculate additional spacing
        $i_Left = (Int($i / $i_Rows))
        $i_Top = (30.7 * Mod($i, $i_Rows))

        ; draw the inputs and add the controlID to the correct gui array
        $aid_Temp[$i][0] = GUICtrlCreateInput("", ($i_Spacing - 50) + $i_Left, $i_Top + $i_Spacing, 155, 20, BitOR($GUI_SS_DEFAULT_INPUT, $ES_READONLY))
        GUICtrlSetData(-1, $s_Value)

        ; draw the N/A check boces
        $aid_Temp[$i][1] = GUICtrlCreateCheckbox("", ($i_Spacing + 165) + $i_Left, $i_Top + $i_Spacing, 15, 20)

        ; draw the no error
        $aid_Temp[$i][2] = GUICtrlCreateCheckbox("", ($i_Spacing + 260) + $i_Left, $i_Top + $i_Spacing, 15, 20)

        ; draw the with error check boxes
        $aid_Temp[$i][3] = GUICtrlCreateCheckbox("", ($i_Spacing + 355) + $i_Left, $i_Top + $i_Spacing, 15, 20)

        ; draw the edit boxes
        $aid_Temp[$i][4] = GUICtrlCreateEdit("", ($i_Spacing + 440) + $i_Left, $i_Top + $i_Spacing, 240, 24, BitOR($ES_AUTOVSCROLL, $ES_WANTRETURN))

        $i_ArrayRows += 1
    Next

    ; add scroll bars to the current gui
    _GUIScrollbars_Generate($g_aidControls[$i_ArrayRow], 0, 2000)

    $aid_Temp[0][0] = $i_ArrayRows ; update the total number of controls
;~  Redim $aid_Temp[$i_ArrayRows + 1][$i_Columns] ; redim the array. not really needed but nice to do

    Return $aid_Temp
EndFunc   ;==>CreateTabGUIs

Func Draw_GUI()
    Local $h_MainGUI = GUICreate("Test Form", 820, 740, -1, -1)
    GUISetBkColor(0x38A7D2)
    GUISetOnEvent($GUI_EVENT_CLOSE, "CloseProgram")

    GUICtrlCreatePic(@ScriptDir & "\Image.jpg", 690, 15, 120, 120)

    GUICtrlCreateLabel("Welcome: " & GetFullName(@UserName), 17, 130, 500, 16)
    GUICtrlSetColor(-1, $COLOR_WHITE)
    GUICtrlSetFont(-1, 9, $FW_BOLD)

    #Region #### Main Menu ###########################
    Local $id_Menu = GUICtrlCreateMenu("File")
    GUICtrlCreateMenuItem("Close", $id_Menu)
    GUICtrlSetOnEvent(-1, 'CloseProgram')
    GUICtrlCreateMenuItem("Export", $id_Menu)
    GUICtrlSetOnEvent(-1, 'MainMenu_FileExport')

    $id_Menu = GUICtrlCreateMenu("Edit")
    GUICtrlSetState(-1, $GUI_Disable)

    $id_Menu = GUICtrlCreateMenu("Option")
    GUICtrlSetState(-1, $GUI_Disable)

    $id_Menu = GUICtrlCreateMenu("View")
    GUICtrlCreateMenuItem("Exit", $id_Menu)
    GUICtrlSetState(-1, $GUI_Disable)
    GUICtrlSetOnEvent(-1, 'MainMenu_ViewExit')

    $id_Menu = GUICtrlCreateMenu("Help")
    GUICtrlCreateMenuItem("About", $id_Menu)
    GUICtrlSetOnEvent(-1, 'MainMenu_HelpAbout')
    #EndRegion #### Main Menu ###########################

    #Region #### Employee Info #######################
    GUICtrlCreateGroup("Employee Information", 16, 8, 281, 121)

    GUICtrlCreateLabel("Emp#:", 33, 35, 35, 17)
    $g_aidControls[$EMPNUMBER_INP] = GUICtrlCreateInput("1234567", 72, 32, 217, 21)
    GUICtrlSetState(-1, $GUI_Disable)

    GUICtrlCreateLabel("Name:", 33, 59, 35, 17)
    $g_aidControls[$EMPNAME_INP] = GUICtrlCreateInput(GetFullName(@UserName), 72, 56, 217, 21)
    GUICtrlSetState(-1, $GUI_Disable)

    GUICtrlCreateLabel("Process:", 24, 83, 45, 17)
    $g_aidControls[$EMPPROCESS_INP] = GUICtrlCreateInput("First Process", 72, 80, 217, 21)
    GUICtrlSetState(-1, $GUI_Disable)

    GUICtrlCreateGroup("", -99, -99, 1, 1)
    #EndRegion #### Employee Info #######################

    #Region #### whatever this is ####################
    GUICtrlCreateLabel("AID:", 10, 170, 25, 17)
    GUICtrlSetFont(-1, 10, $FW_BOLD)

    $g_aidControls[$MYSTERY_LBL] = GUICtrlCreateLabel("ABZ123456", 40, 170, 90, 17) ; remove this if you don't alter the text
    GUICtrlSetColor(-1, $COLOR_WHITE)
    GUICtrlSetFont(-1, 10, $FW_BOLD)
    #EndRegion #### whatever this is ####################

    #Region #### confirmation checkboxes #############
    $g_aidControls[$AGREE_CHK] = GUICtrlCreateCheckbox("I agree that I have checked the entry(s) above.", 16, 659, 265, 17)

    $g_aidControls[$ACCEPT_CHK] = GUICtrlCreateCheckbox("Accept", 16, 677, 50, 17)
    GUICtrlCreateLabel("*", 72, 680, 40, 17)
    GUICtrlSetColor(-1, $COLOR_RED)
    GUICtrlSetFont(-1, 10, $FW_BOLD)

    $g_aidControls[$REJECT_CHK] = GUICtrlCreateCheckbox("Reject", 16, 695, 50, 17)
    GUICtrlCreateLabel("*", 70, 695, 40, 17)
    GUICtrlSetColor(-1, $COLOR_RED)
    GUICtrlSetFont(-1, 10, $FW_BOLD)
    #EndRegion #### confirmation checkboxes #############

    #Region #### submit button ###########################
    $g_aidControls[$SUBMIT_BTN] = GUICtrlCreateButton("Submit Report", 635, 672, 177, 33)
    GUICtrlSetOnEvent(-1, "Submit")
    #EndRegion #### submit button ###########################

    #Region #### Create Tabs #########################
    Draw_Tabs($h_MainGUI)
    #EndRegion #### Create Tabs #########################

    GUISetState(@SW_SHOW, $h_MainGUI) ; show the main gui
    Sleep(80) ; add a sleep to try and get them to show at the same time
    GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI]) ; show the first tab gui
EndFunc   ;==>Draw_GUI

Func Draw_Tabs($h_MainGUI)
    ; create the main tab control
    $g_aidControls[$MAIN_TAB] = GUICtrlCreateTab(10, 190, 780, 455, $WS_EX_MDICHILD)
    GUICtrlSetOnEvent(-1, "TabChange") ; do summat on tab change

    ; create the child tab item
    GUICtrlCreateTabItem("Front Matter")
    GUICtrlCreateTabItem("Body Matter")
    GUICtrlCreateTabItem("Back Matter")
    GUICtrlCreateTabItem("")

    ; get the strings that will populate the gui inputs from the excel sheet
    ; ### add some error checking for this ####
    Local $as_RangeRead = Excel_GetValuesFromRange()

    ; loop though the array of arrays
    For $i = 1 To 3 ; create the 3 tabs, gui and their controls
        Switch $i
            Case 1
                $aid_FrontMatterGUI = CreateTabGUIs($as_RangeRead, $i - 1, $h_MainGUI)
            Case 2
                $aid_BodyMatterGUI = CreateTabGUIs($as_RangeRead, $i - 1, $h_MainGUI)
            Case 3
                $aid_BackMatterGUI = CreateTabGUIs($as_RangeRead, $i - 1, $h_MainGUI)
        EndSwitch
    Next

;~  _ArrayDisplay($aid_FrontMatterGUI)
;~  _ArrayDisplay($aid_BodyMatterGUI)
;~  _ArrayDisplay($aid_BackMatterGUI)
EndFunc   ;==>Draw_Tabs

Func Excel_GetValuesFromRange()
    ; ### add some error checking to this function
    Local $o_Excel = _Excel_Open(False)
    Local $o_Workbook = _Excel_BookOpen($o_Excel, $g_sExcelFile)

    Local $as_RangeRead = _Excel_RangeRead($o_Workbook, Default, $o_Workbook.ActiveSheet.Usedrange.Columns("A:C"), 2)
    _Excel_Close($o_Excel)
    Return $as_RangeRead
EndFunc   ;==>Excel_GetValuesFromRange

Func GetFullName($sUserName) ; I can't test this
;~  $strComputer = "localhost" ; do you need to specify this????

;~  Local $objWMIService = ObjGet("winmgmts:\\" & $strComputer & "\root\CIMV2")
    Local $objWMIService = ObjGet("winmgmts:\\localhost\root\CIMV2")
    Local $colItems = $objWMIService.ExecQuery("SELECT * FROM Win32_UserAccount WHERE Name = '" & $sUserName & "'", "WQL", 0x10 + 0x20)
    If Not IsObj($colItems) Then Return SetError(1, 0, "")

    For $objItem In $colItems
        Return $objItem.FullName
    Next
EndFunc   ;==>GetFullName

Func MainMenu_FileExport()
    ; add commands later
EndFunc   ;==>MainMenu_FileExport

Func MainMenu_HelpAbout()
    ; add commands later
EndFunc   ;==>MainMenu_HelpAbout

Func MainMenu_ViewExit()
    ; add commands later
EndFunc   ;==>MainMenu_ViewExit

Func TabChange()
    Switch GUICtrlRead($g_aidControls[$MAIN_TAB])
        Case 0 ; Front Matter tab
            GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI])
        Case 1 ; body Matter tab
            GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_SHOW, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI])
        Case 2 ; back matter tab
            GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_SHOW, $g_aidControls[$BACKMATTERGUI])
    EndSwitch
EndFunc   ;==>TabChange

Func Submit()
    ; ##### left this as I don't know whether you right all the 3 guis control values or just the current one ####
;~  Local $o_Excel = _Excel_Open(False)
;~  Local $o_Workbook = _Excel_BookOpen($o_Excel, $g_sExcelFile)
;~  If @error Then Return SetError(1)

;~  Local $ai_Labels[4] = [3, $id_EmpNbr_inp, $id_EmpNme_inp, $id_EmpPrs_inp]

;~  For $i = 1 To $ai_Labels[0]
;~      _Excel_RangeWrite($o_Workbook, Default, GUICtrlRead($ai_Labels[$i]), "D" & $i)
;~  Next

;~  _Excel_Close($o_Excel)
;~  ShellExecute($g_sExcelFile)
EndFunc   ;==>Submit

I have attached the sample file as the one in the first post had a blank cell before GTOC.

Sample.xlsx

KickStarter15 · January 6, 2018

Awsome, Benners.

Where were you by the time I need help... Thumbs up men. It's so beautiful compared to what I coded. Now I need to learn and read things in you code, so exciting. However, I still need to learn how to handle these checkboxes input and extract them in Excel cell.

Much appreciated benners!!!!!

One question:

- Is there a chances that I can extract these inputs to an excel file?

Thank you so much!

benners · January 6, 2018

Quote

Where were you by the time I need help

I was in my own coding nightmare

Quote

It's so beautiful compared to what I coded

In a years time when you know better, you'll look back and laugh at it

You can get the values by using guictrlread. The number in the arrays will be the controlID of the control. just uncomment the code below to show the array and the IDs. If you read all these values and make an array. I think RangeWrite can accept an array.

;~  _ArrayDisplay($aid_FrontMatterGUI)

I will do a bit more later as I need to know what layout you want to write in the spreadsheet and it's 03:45 here and i'm up again @ 07:00. With more info I can make it easier for you to add the code that writes the excel sheet.

Do you need the values in upper case like CATEGORY?
Do the upper case values need the checkboxes?
Do you need the check box values like 1 or 4 or the values like checked/ unchecked

If you make another sample excel sheet with what you expect to see in the final sheet when you click the submit button, I'll make it easier.

KickStarter15 · January 6, 2018

2 hours ago, benners said:

In a years time when you know better, you'll look back and laugh at it

Hahaha funny, well good thing we have you in this forum...

2 hours ago, benners said:

I was in my own coding nightmare

o:) .... your nightmare is an angel falling....

2 hours ago, benners said:

Do you need the values in upper case like CATEGORY?

Do the upper case values need the checkboxes?

Do you need the check box values like 1 or 4 or the values like checked/ unchecked

Item 1: Not necessary, it's just a heading for identification.

Item 2: Honestly, No , I just don't know how to disable this checkboxes from the heading line...

Item 3: I have attached two samples layout that I want it to be. Maybe it's easier if we use checked/unchecked.

ExcelLayouts.zip

benners · January 6, 2018

I have done most of the code. It creates the sheet similar to the one in layout1. I'll let you have a go and sort layout2. Couldn't get the bullet points to show up correctly like they do in the original sheet.

Only the values on the tabs are logged in the sheet, the other employee inputs and checkboxes should be easy enough for you to add where you want.

There are a couple of niggles with the gui. Would be nice for the scroll bars to stop when the last control is visible, rather than scrolling pages of white. Also, if you minimise the gui, the tab gui's show up before the main gui when you restore the window. Not a job stopper but just aesthetics :construction:

It uses the sample file in post # 6. Enjoy

#Region #### Includes ################################
#include <ButtonConstants.au3>
#include <ColorConstants.au3>
#include <EditConstants.au3>
#include <Excel.au3>
#include <FontConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIScrollbars_Ex.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Array.au3>
#EndRegion #### Includes ################################

Opt('GUIOnEventMode', 1)
HotKeySet('{ESC}', 'CloseProgram')

#Region #### Globals #################################
; these are integers that relate to where the controliDs are located in $g_aidControls.
; The control ID can be retrieved like so, $g_aidControls[$EMPPROCESS_INP]
Global Enum _ ; values are 0 to 13
        $FRONTMATTERGUI, _ ;_   gui 1
        $BODYMATTERGUI, _ ;_    gui 2
        $BACKMATTERGUI, _ ;_    gui 3
        $TABCOUNT, _ ; #### IMPORTANT #### this must be after the last gui. it signifies how many tabs there will be and is used in other functions
        $EMPNUMBER_INP, _ ;_    employee number input id
        $EMPNAME_INP, _ ;_      employee name input id
        $EMPPROCESS_INP, _ ;_   employee process input id
        $MYSTERY_LBL, _ ;_      some label I don't know if you use id
        $AGREE_CHK, _ ;_        I agree checkbox id
        $ACCEPT_CHK, _ ;_       accept checkbox id
        $REJECT_CHK, _ ;_       reject checkbox id
        $SUBMIT_BTN, _ ;_       submit button id
        $MAIN_TAB, _ ;_         main tab id
        $ARRAYMAXROWS ;_        number of rows to give the array its 0 based

; create the array to hold the remaining controlIDs. These can be referenced
; later when writing their values to the excel sheet
Global $g_aidControls[$ARRAYMAXROWS]

; create the arrays to hold the all controlIDs that will be created on the tab guis.
; These can be referenced later when writing their values to the excel sheet
Global _
        $aid_FrontMatterGUI = '', _
        $aid_BodyMatterGUI = '', _
        $aid_BackMatterGUI = ''

; these are the tab names and will be used as the spreadsheet names when the report is submitted
; these have the same value as the Global Enum $FRONTMATTERGUI to $BACKMATTERGUI and are used in varios functions
Global $g_asTabNames[$TABCOUNT] = [ _ ; values are 0 to 2
        'Front Matter', _
        'Body Matter', _
        'Back Matter']

; these are integers that relate to where the rows are for each set of controls in the $aid_FrontMatterGUI array etc
; they are also the same values as their matching strings index in the $g_asTabHeaderText array
Global Enum _ ; values are 0 to 6
        $ENTRYS_COL, _
        $NA_COL, _
        $NOERROR_COL, _
        $WITHERROR_COL, _
        $REMARKS_COL, _
        $ARRAYMAX_COLS

; this is the array that holds the tab header text strings. These are written to the first row of the array that matches the tab and gui
; I.E -
;   Tab = 'Front Matter'
;   Array with the controlIDs = $aid_FrontMatterGUI
Global $g_asTabHeaderText[$ARRAYMAX_COLS] = [ _
        'Entry(s)', _
        'N/A', _
        'No Error', _
        'With Error', _
        'Remarks']

#EndRegion #### Globals #################################

Draw_GUI()

While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch

    Sleep(50)
WEnd

Func CloseProgram()
    Exit
EndFunc   ;==>CloseProgram

Func WriteArrayValuesToSpreadsheet($o_WorkBook)
    Local _ ; this is an array of arrays. The arrays have the controlIDs for the relevant tab gui controls
            $aa_ControlArrays[4] = [3, $aid_FrontMatterGUI, $aid_BodyMatterGUI, $aid_BackMatterGUI], _
            $as_Temp = ''

    ; these determine which cells on the active sheet get fettled later :)
    Local $s_AutoFitRange = _Excel_ColumnToLetter($ENTRYS_COL + 1) & ':' & _Excel_ColumnToLetter($ARRAYMAX_COLS)
    Local $s_HeaderCellRange = _Excel_ColumnToLetter($ENTRYS_COL + 1) & '1:' & _Excel_ColumnToLetter($ARRAYMAX_COLS) & '1'
    Local $s_BoldCellRange = ''

    ; loop through the arrays
    For $i = 1 To $aa_ControlArrays[0]
        $as_Temp = $aa_ControlArrays[$i] ; copy the nested array to a temp one. this will be filled with the values of the controls

        ; loop through the temp array
        For $j = 1 To UBound($as_Temp) - 1
            $s_BoldCells = CreateArrayOfControlValues($as_Temp, $j) ; update the array row with the control values
            If $s_BoldCells Then $s_BoldCellRange &= $s_BoldCells & ',' ; if any cells are returned assume they need to be bold in the spreadsheet
        Next

        $s_BoldCellRange = StringTrimRight($s_BoldCellRange, 1) ; remove the trailing ',' from the string

        $o_WorkBook.Sheets($i).Activate ;_              activate the sheet
        _Excel_RangeWrite($o_WorkBook, $i, $as_Temp) ;_ write the array to an excel spreadsheet
        If @error Then Return SetError(@error, @extended, 'Error Writing ' & $g_asTabNames[$i - 1] & ' to sheet')

        ; adjust the sheet properties whilst it's active
        With $o_WorkBook.ActiveSheet
            .Columns($s_AutoFitRange).AutoFit ;_                            resize the columns to fit the text
            .Name = $g_asTabNames[$i - 1] ;_                                set the sheet name
            .Range($s_HeaderCellRange).Font.FontStyle = 'Bold' ;_           set the font weight as bold
            .Range($s_BoldCellRange).Font.FontStyle = 'Bold' ;_             set the font weight as bold
            .Range($s_HeaderCellRange).HorizontalAlignment = $xlCenter ;_   centre align the cells
            .Range($s_HeaderCellRange).Interior.ColorIndex = 15 ;_          set the header cells back colour
        EndWith

        $s_BoldCellRange = '' ; reset the string for next loop
        Sleep(20)
    Next

    $o_WorkBook.Sheets(1).Activate ; activate the first sheet
EndFunc   ;==>WriteArrayValuesToSpreadsheet

Func CreateTabGUIs(ByRef $as_RangeRead, $i_ArrayRow, $h_MainGUI)
    ; create the gui for the controls and add it to the global controls array
    $g_aidControls[$i_ArrayRow] = GUICreate('', 770, 370, 12, 270, $WS_POPUP, $WS_EX_MDICHILD, $h_MainGUI)
    GUISetBkColor($COLOR_WHITE)

    ; create the temp array. This will hold all the controlIDs
    Local $aid_Temp[71][$ARRAYMAX_COLS] = [[70]]

    ; add the header text to the first row of the array
    $aid_Temp[0][$ENTRYS_COL] = 'Entry(s)'
    $aid_Temp[0][$NA_COL] = 'N/A'
    $aid_Temp[0][$NOERROR_COL] = 'No Error'
    $aid_Temp[0][$WITHERROR_COL] = 'With Error'
    $aid_Temp[0][$REMARKS_COL] = 'Remarks'

    GUICtrlCreateGroup('', -99, -99, 1, 1)

    Local $s_Value = '' ; value to give the control
    Local _
            $i_ArrayRowCount = 0, _ ;_  keeps track of number of array rows (controls rows added)
            $i_Left = 0, _ ;_           starting left position
            $i_Rows = 60, _ ;_          initial number of rows
            $i_Spacing = 60, _ ;_       control padding
            $i_Top = 18 ;_              initial top position

    For $i = 1 To UBound($aid_Temp) - 1 ; 1 to 70 rows of controls
        ; get the value to add to the control from the array returned by Excel_GetValuesFromRange()
        $s_Value = $as_RangeRead[$i - 1][$i_ArrayRow]

        ; don't create anymore controls if the value is blanks
        If Not $s_Value Then ExitLoop

        ; calculate additional spacing
        $i_Left = (Int($i / $i_Rows)) + $i_Spacing
        $i_Top = (30.7 * Mod($i, $i_Rows)) - 20

        ; check if the string is upper case. Strip any none letter characters out for testing
        ; as digits/punctuation/whitespace will cause StringIsUpper() to return 0.
        If StringIsUpper(StringRegExpReplace($s_Value, '[^A-Za-z]', '')) Then ; assume its a heading
            ; draw the input and add the controlID to the correct gui array
            $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 730, 20, $WS_EX_CLIENTEDGE)
            GUICtrlSetFont(-1, 8, 800)
            GUICtrlSetColor(-1, $COLOR_BLACK)
            GUICtrlSetBkColor(-1, $COLOR_SILVER)

            ; set the other columns to 0 as there are no other controls being added
            $aid_Temp[$i][$NA_COL] = 0
            $aid_Temp[$i][$NOERROR_COL] = 0
            $aid_Temp[$i][$WITHERROR_COL] = 0
            $aid_Temp[$i][$REMARKS_COL] = 0
        Else ; assume it's a sub
            ; draw the input
            $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateInput($s_Value, $i_Left - 50, $i_Top, 155, 20, BitOR($GUI_SS_DEFAULT_INPUT, $ES_READONLY), $WS_EX_TRANSPARENT) ; use an input
            GUICtrlSetBkColor(-1, $COLOR_WHITE)
;~          $aid_Temp[$i][0] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 155, 40) ; use a label if you want

            ; draw the N/A check box
            $aid_Temp[$i][$NA_COL] = GUICtrlCreateCheckbox('', $i_Left + 165, $i_Top, 15, 20)

            ; draw the no error check box
            $aid_Temp[$i][$NOERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 260, $i_Top, 15, 20)

            ; draw the with error check box
            $aid_Temp[$i][$WITHERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 355, $i_Top, 15, 20)

            ; draw the edit control
            $aid_Temp[$i][$REMARKS_COL] = GUICtrlCreateEdit('', $i_Left + 440, $i_Top, 240, 24, BitOR($ES_AUTOVSCROLL, $ES_WANTRETURN))
        EndIf

        $i_ArrayRowCount += 1 ; increase the number of rows added to the array
    Next

    ; add scroll bars to the current gui
    _GUIScrollbars_Generate($g_aidControls[$i_ArrayRow], 0, 2000)

    ReDim $aid_Temp[$i_ArrayRowCount + 1][$ARRAYMAX_COLS] ; redim the array to remove any blanks
;~ _ArrayDisplay($aid_Temp)
    Return $aid_Temp
EndFunc   ;==>CreateTabGUIs

Func Draw_GUI()
    Local $h_MainGUI = GUICreate('Test Form', 820, 740, -1, -1)
    GUISetBkColor(0x38A7D2)
    GUISetOnEvent($GUI_EVENT_CLOSE, 'CloseProgram')

    GUICtrlCreatePic(@ScriptDir & '\Image.jpg', 690, 15, 120, 120)

    GUICtrlCreateLabel('Welcome: ' & GetFullName(@UserName), 17, 130, 500, 16)
    GUICtrlSetColor(-1, $COLOR_WHITE)
    GUICtrlSetFont(-1, 9, $FW_BOLD)

    #Region #### Main Menu ###########################
    Local $id_Menu = GUICtrlCreateMenu('File')
    GUICtrlCreateMenuItem('Close', $id_Menu)
    GUICtrlSetOnEvent(-1, 'CloseProgram')
    GUICtrlCreateMenuItem('Export', $id_Menu)
    GUICtrlSetOnEvent(-1, 'MainMenu_FileExport')

    $id_Menu = GUICtrlCreateMenu('Edit')
    GUICtrlSetState(-1, $GUI_Disable)

    $id_Menu = GUICtrlCreateMenu('Option')
    GUICtrlSetState(-1, $GUI_Disable)

    $id_Menu = GUICtrlCreateMenu('View')
    GUICtrlCreateMenuItem('Exit', $id_Menu)
    GUICtrlSetState(-1, $GUI_Disable)
    GUICtrlSetOnEvent(-1, 'MainMenu_ViewExit')

    $id_Menu = GUICtrlCreateMenu('Help')
    GUICtrlCreateMenuItem('About', $id_Menu)
    GUICtrlSetOnEvent(-1, 'MainMenu_HelpAbout')
    #EndRegion #### Main Menu ###########################

    #Region #### Employee Info #######################
    GUICtrlCreateGroup('Employee Information', 16, 8, 281, 121)

    GUICtrlCreateLabel('Emp#:', 33, 35, 35, 17)
    $g_aidControls[$EMPNUMBER_INP] = GUICtrlCreateInput('1234567', 72, 32, 217, 21)
    GUICtrlSetState(-1, $GUI_Disable)

    GUICtrlCreateLabel('Name:', 33, 59, 35, 17)
    $g_aidControls[$EMPNAME_INP] = GUICtrlCreateInput(GetFullName(@UserName), 72, 56, 217, 21)
    GUICtrlSetState(-1, $GUI_Disable)

    GUICtrlCreateLabel('Process:', 24, 83, 45, 17)
    $g_aidControls[$EMPPROCESS_INP] = GUICtrlCreateInput('First Process', 72, 80, 217, 21)
    GUICtrlSetState(-1, $GUI_Disable)

    GUICtrlCreateGroup('', -99, -99, 1, 1)
    #EndRegion #### Employee Info #######################

    #Region #### whatever this is ####################
    GUICtrlCreateLabel('AID:', 10, 170, 25, 17)
    GUICtrlSetFont(-1, 10, $FW_BOLD)

    $g_aidControls[$MYSTERY_LBL] = GUICtrlCreateLabel('ABZ123456', 40, 170, 90, 17) ; remove this if you don't alter the text
    GUICtrlSetColor(-1, $COLOR_WHITE)
    GUICtrlSetFont(-1, 10, $FW_BOLD)
    #EndRegion #### whatever this is ####################

    #Region #### confirmation checkboxes #############
    $g_aidControls[$AGREE_CHK] = GUICtrlCreateCheckbox('I agree that I have checked the entry(s) above.', 16, 659, 265, 17)

    $g_aidControls[$ACCEPT_CHK] = GUICtrlCreateCheckbox('Accept', 16, 677, 50, 17)
    GUICtrlCreateLabel('*', 72, 680, 40, 17)
    GUICtrlSetColor(-1, $COLOR_RED)
    GUICtrlSetFont(-1, 10, $FW_BOLD)

    $g_aidControls[$REJECT_CHK] = GUICtrlCreateCheckbox('Reject', 16, 695, 50, 17)
    GUICtrlCreateLabel('*', 70, 695, 40, 17)
    GUICtrlSetColor(-1, $COLOR_RED)
    GUICtrlSetFont(-1, 10, $FW_BOLD)
    #EndRegion #### confirmation checkboxes #############

    #Region #### submit button #######################
    $g_aidControls[$SUBMIT_BTN] = GUICtrlCreateButton('Submit Report', 615, 665, 177, 33)
    GUICtrlSetOnEvent(-1, 'Submit')
    #EndRegion #### submit button #######################

    #Region #### Create Tabs #########################
    Draw_Tabs($h_MainGUI)
    #EndRegion #### Create Tabs #########################

    GUISetState(@SW_SHOW, $h_MainGUI) ; show the main gui
    Sleep(80) ; add a sleep to try and get them to show at the same time
    GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI]) ; show the first tab gui
EndFunc   ;==>Draw_GUI

Func Draw_TabHeaders()
    ; this draws the headers over the columns on the tab gui. The values are pulled from the $g_asTabHeaderText array
    GUICtrlCreateGroup('', 30, 215, 730, 50)
    GUICtrlCreateLabel($g_asTabHeaderText[$ENTRYS_COL], 50, 232, 80, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$NA_COL], 222, 232, 40, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$NOERROR_COL], 301, 232, 70, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$WITHERROR_COL], 393, 232, 85, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$REMARKS_COL], 570, 232, 90, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
EndFunc   ;==>Draw_TabHeaders

Func Draw_Tabs($h_MainGUI)
    ; create the main tab control
    $g_aidControls[$MAIN_TAB] = GUICtrlCreateTab(10, 190, 780, 455, $WS_EX_MDICHILD)
    GUICtrlSetOnEvent(-1, 'TabChange') ; do summat on tab change

    ; get the strings that will populate the gui inputs from the excel sheet
    ; ### add some error checking for _Excel_ColumnToLetter ####
    Local $as_RangeRead = Excel_GetValuesFromRange(_Excel_ColumnToLetter($FRONTMATTERGUI + 1) & ':' & _Excel_ColumnToLetter($TABCOUNT))

    ; loop to create the required number of tabs, guis and their controls
    For $i = $FRONTMATTERGUI To $TABCOUNT
        Switch $i
            Case $FRONTMATTERGUI ; gui 1
                GUICtrlCreateTabItem($g_asTabNames[$i])
                Draw_TabHeaders()
                $aid_FrontMatterGUI = CreateTabGUIs($as_RangeRead, $i, $h_MainGUI)
            Case $BODYMATTERGUI ; gui 2
                GUICtrlCreateTabItem($g_asTabNames[$i])
                Draw_TabHeaders()
                $aid_BodyMatterGUI = CreateTabGUIs($as_RangeRead, $i, $h_MainGUI)
            Case $BACKMATTERGUI ; gui 3
                GUICtrlCreateTabItem($g_asTabNames[$i])
                Draw_TabHeaders()
                $aid_BackMatterGUI = CreateTabGUIs($as_RangeRead, $i, $h_MainGUI)
        EndSwitch

        ; switch to the main gui and tab after creating the new guis or else the control creation gets bollocksed Up
        ; and the tabs or controls are not visible
        GUISwitch($h_MainGUI, $g_aidControls[$MAIN_TAB])
    Next

    GUICtrlCreateTabItem('')

;~  _ArrayDisplay($aid_FrontMatterGUI)
;~  _ArrayDisplay($aid_BodyMatterGUI)
;~  _ArrayDisplay($aid_BackMatterGUI)
EndFunc   ;==>Draw_Tabs

Func Excel_GetValuesFromRange($s_Range = 'A')
    ; set the path to the file that has the values for the inputs
    Local $s_ExcelFile = @ScriptDir & '\Sample.xlsx'

    ; #### add some error checking for these excel functions ####
    Local $o_Excel = _Excel_Open(False)
    Local $o_WorkBook = _Excel_BookOpen($o_Excel, $s_ExcelFile)
    Local $as_RangeRead = _Excel_RangeRead($o_WorkBook, Default, $o_WorkBook.ActiveSheet.Usedrange.Columns($s_Range), 2)
    _Excel_Close($o_Excel)

;~  _ArrayDisplay($as_RangeRead)
    Return $as_RangeRead
EndFunc   ;==>Excel_GetValuesFromRange

Func CreateArrayOfControlValues(ByRef $aid_ControlID, $i_Row)
    Local $s_Value = ''
    Local $s_BoldCells = ''

    ; loop through the array columns
    For $i = $ENTRYS_COL To $REMARKS_COL
        ; get the value from the specified control in the array
        $s_Value = GUICtrlRead($aid_ControlID[$i_Row][$i])

        ; if its 0 then set a blank string. this will be if no controlID was added. Assume it's a header
        If Not $s_Value Then $s_Value = ''

        ; do different things based on the specified control
        If $i = $ENTRYS_COL Or $i = $REMARKS_COL Then ; these are input controls
            ; write the value to the array
            $aid_ControlID[$i_Row][$i] = $s_Value
        Else ; these are checkbox controls
            ; get the checked state of the control
            $aid_ControlID[$i_Row][$i] = GetCheckBoxValue($aid_ControlID[$i_Row][$i])

            ; here we attempt to get the array row that contains a heading for identification. This row will be used
            ; later to try and set these items in bold in the finished excel sheet. We add plus one because $aid_ControlID is 0 based
            ; and when we write the spreadsheet we start a cell 1
            If $aid_ControlID[$i_Row][$i] = '' And $s_BoldCells = '' Then $s_BoldCells = 'A' & $i_Row + 1
        EndIf
    Next

    Return $s_BoldCells
EndFunc   ;==>CreateArrayOfControlValues

Func GetCheckBoxValue($id_ControlID)
    If Not $id_ControlID Then Return '' ; no checkbox control
    If GUICtrlRead($id_ControlID) = $GUI_CHECKED Then Return 'Checked'
    Return 'Unchecked'
EndFunc   ;==>GetCheckBoxValue

Func GetFullName($sUserName) ; I can't test this
;~  $strComputer = 'localhost' ; do you need to specify this????

;~  Local $objWMIService = ObjGet('winmgmts:\\' & $strComputer & '\root\CIMV2')
    Local $objWMIService = ObjGet('winmgmts:\\localhost\root\CIMV2')
    Local $colItems = $objWMIService.ExecQuery('SELECT * FROM Win32_UserAccount WHERE Name = '' & $sUserName & ''', 'WQL', 0x10 + 0x20)
    If Not IsObj($colItems) Then Return SetError(1, 0, '')

    For $objItem In $colItems
        Return $objItem.FullName
    Next
EndFunc   ;==>GetFullName

Func MainMenu_FileExport()
    ; add commands later
EndFunc   ;==>MainMenu_FileExport

Func MainMenu_HelpAbout()
    ; add commands later
EndFunc   ;==>MainMenu_HelpAbout

Func MainMenu_ViewExit()
    ; add commands later
EndFunc   ;==>MainMenu_ViewExit

Func TabChange()
    Switch GUICtrlRead($g_aidControls[$MAIN_TAB])
        Case $FRONTMATTERGUI ; Front Matter tab
            GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI])
        Case $BODYMATTERGUI ; body Matter tab
            GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_SHOW, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI])
        Case $BACKMATTERGUI ; back matter tab
            GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_SHOW, $g_aidControls[$BACKMATTERGUI])
    EndSwitch
EndFunc   ;==>TabChange

Func Submit()
    SplashTextOn("submit", "Submitting report...", 300, 50, -1, -1, $DLG_NOTITLE + $DLG_TEXTVCENTER, '', '', $FW_BOLD)

    ; open an excel instance
    Local $o_Excel = _Excel_Open(False)
    If @error Then Return SetError(1, _
            MsgBox($MB_SYSTEMMODAL, _
            '_Excel_Open', _
            'Error creating the Excel application object' & @CRLF & '@error = ' & @error & ', @extended = ' & @extended), 0)

    ; open a new excel book
    Local $o_WorkBook = _Excel_BookNew($o_Excel, $TABCOUNT) ; $TABCOUNT is the number of tabs in your program and so the number of sheets we create for submitting
    If @error Then Return SetError(2, _
            MsgBox($MB_SYSTEMMODAL, _
            '_Excel_BookNew', _
            'Error creating new workbook' & @CRLF & '@error = ' & @error & ', @extended = ' & @extended), _Excel_Close($o_Excel))

    ; this will read the control values to an array and write the array to
    ; an excel file
    WriteArrayValuesToSpreadsheet($o_WorkBook)

    If Not @error Then
        Local $s_FilePath = @ScriptDir & '\Report_' & @YEAR & '-' & @MON & '-' & @MDAY & '_' & @HOUR & @MIN & @SEC & '.xlsx'

        ; save the created report
        _Excel_BookSaveAs($o_WorkBook, $s_FilePath, $xlWorkbookDefault)
        If @error Then MsgBox($MB_SYSTEMMODAL, _
                "_Excel_BookSaveAs", _
                "Error saving workbook to '" & $s_FilePath & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    EndIf

    _Excel_Close($o_Excel)
    ControlSetText("submit", "", "Static1", 'Opening report...')
    ShellExecute($s_FilePath)
    SplashOff()
EndFunc   ;==>Submit

benners · January 7, 2018

Getting quite addictive this. Added the option of choosing from the two layouts you added in post #9. Was a nightmare but interesting learning

#Region #### Includes ################################
#include <ButtonConstants.au3>
#include <ColorConstants.au3>
#include <EditConstants.au3>
#include <Excel.au3>
#include <FontConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIScrollbars_Ex.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Array.au3>
#EndRegion #### Includes ################################

Opt('GUIOnEventMode', 1)
HotKeySet('{ESC}', 'CloseProgram')

#Region #### Globals #################################
; these are integers that relate to where the controliDs are located in $g_aidControls.
; The control ID can be retrieved like so, $g_aidControls[$EMPPROCESS_INP]
Global Enum _ ; values are 0 to 13
        $FRONTMATTERGUI, _ ;_   gui 1
        $BODYMATTERGUI, _ ;_    gui 2
        $BACKMATTERGUI, _ ;_    gui 3
        $TABCOUNT, _ ; #### IMPORTANT #### this must be after the last gui. it signifies how many tabs there will be and is used in other functions
        $EMPNUMBER_INP, _ ;_    employee number input id
        $EMPNAME_INP, _ ;_      employee name input id
        $EMPPROCESS_INP, _ ;_   employee process input id
        $MYSTERY_LBL, _ ;_      some label I don't know if you use id
        $AGREE_CHK, _ ;_        I agree checkbox id
        $ACCEPT_CHK, _ ;_       accept checkbox id
        $REJECT_CHK, _ ;_       reject checkbox id
        $LAYOUTONE_RDO, _ ;_        radio button for layout 1 selection
        $SUBMIT_BTN, _ ;_       submit button id
        $MAIN_TAB, _ ;_         main tab id
        $ARRAYMAXROWS ;_        number of rows to give the array its 0 based

; create the array to hold the remaining controlIDs. These can be referenced
; later when writing their values to the excel sheet
Global $g_aidControls[$ARRAYMAXROWS]

; create the arrays to hold the all controlIDs that will be created on the tab guis by CreateTabGUIs() called by Draw_Tabs() function
; These can be referenced later when writing their values to the excel sheet
Global _
        $aid_FrontMatterGUI = '', _
        $aid_BodyMatterGUI = '', _
        $aid_BackMatterGUI = ''

; these are the tab names and will be used as the spreadsheet names when the report is submitted
; these have the same value as the Global Enum $FRONTMATTERGUI to $BACKMATTERGUI and are used in varios functions
Global $g_asTabNames[$TABCOUNT] = [ _ ; values are 0 to 2
        'Front Matter', _
        'Body Matter', _
        'Back Matter']

; these are integers that relate to where the rows are for each set of controls in the $aid_FrontMatterGUI array etc
; they are also the same values as their matching strings index in the $g_asTabHeaderText array
Global Enum _ ; values are 0 to 6
        $ENTRYS_COL, _
        $NA_COL, _
        $NOERROR_COL, _
        $WITHERROR_COL, _
        $REMARKS_COL, _
        $ARRAYMAX_COLS

; this is the array that holds the tab header text strings. These are written to the first row of the array that matches the tab and gui
; I.E -
;   Tab = 'Front Matter'
;   Array with the controlIDs = $aid_FrontMatterGUI
Global $g_asTabHeaderText[$ARRAYMAX_COLS] = [ _ ; 0 based array
        'Entry(s)', _
        'N/A', _
        'No Error', _
        'With Error', _
        'Remarks']

Global $g_iMultipleSheets = 1

Global _
        $g_sHeaderCells = '', _
        $g_sSectionCells = '', _
        $g_sTabNameCells = ''

#EndRegion #### Globals #################################

Draw_GUI()

While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch

    Sleep(50)
WEnd

; create an excel range from 2 numbers
Func CellRange_CreateFromInteger($i_Start, $i_End)
    ; convert any numbers to excel column letters
    Return _Excel_ColumnToLetter($i_Start) & ':' & _Excel_ColumnToLetter($i_End)
EndFunc   ;==>CellRange_CreateFromInteger

Func CellRange_CreateFromString($s_Range, $v_AddRange)
    ; if $v_AddRange is a number then convert the number to a letter for the range
    If IsNumber($v_AddRange) Then $v_AddRange = _Excel_ColumnToLetter($v_AddRange)

    ; trim any leading\trailing comma symbols before splitting
    $s_Range = StringRegExpReplace($s_Range, "\A[,]+|[,]+\Z", "")

    ; split the comma delimited range
    Local $as_Split = StringSplit($s_Range, ',')

    ; loop through the array
    For $i = 1 To $as_Split[0]
        ; update the array element
        $as_Split[$i] = $as_Split[$i] & ':' & StringRegExpReplace($as_Split[$i], '[[:alpha:]]+', $v_AddRange)
    Next

    ; return a string that will be used as a cell range
    Return _ArrayToString($as_Split, ',', 1)
EndFunc   ;==>CellRange_CreateFromString

Func CellRange_GetValues($s_Range = 'A')
    ; set the path to the file that has the values for the inputs
    Local $s_ExcelFile = @ScriptDir & '\Sample.xlsx'

    ; #### add some error checking for these excel functions ####
    Local $o_Excel = _Excel_Open(False)
    Local $o_WorkBook = _Excel_BookOpen($o_Excel, $s_ExcelFile)
    Local $as_RangeRead = _Excel_RangeRead($o_WorkBook, Default, $o_WorkBook.ActiveSheet.Usedrange.Columns($s_Range), 2)

    _Excel_Close($o_Excel)

;~ _ArrayDisplay($as_RangeRead, 'CellRange_GetValues') ; for debugging

    Return $as_RangeRead
EndFunc   ;==>CellRange_GetValues

Func CellRange_ResetGlobalValues()
    $g_sHeaderCells = ''
    $g_sSectionCells = ''
    $g_sTabNameCells = ''
EndFunc   ;==>CellRange_ResetGlobalValues

Func CloseProgram()
    Exit
EndFunc   ;==>CloseProgram

; create an array of values that are read from the controls on the tabs GUI
Func CreateArrayOfControlValues(ByRef $aid_ControlID, $i_Row)
    Local $v_Value = ''
    Local $s_BoldCells = ''

    ; loop through the array columns
    For $i = $ENTRYS_COL To $REMARKS_COL
        ; get the value from the supplied array
        $v_Value = $aid_ControlID[$i_Row][$i]

        ; check if the value is a controlID
        If IsNumber($v_Value) Then
            If Not $v_Value Then ; it's 0 so no control to read
                $v_Value = '' ; set a blank string
            Else
                $v_Value = GUICtrlRead($aid_ControlID[$i_Row][$i])

                ; assume it's a header or heading indentifier string
                If StringIsUpper(StringRegExpReplace($v_Value, '[^A-Za-z]', '')) Then $g_sSectionCells &= 'A' & $i_Row + 1 & ','
                If StringInStr($v_Value, $g_asTabHeaderText[0]) Then $g_sHeaderCells &= 'A' & $i_Row + 1 & ','
            EndIf
        Else ; assume it's a tab name
            $aid_ControlID[$i_Row][$i] = $v_Value

            If StringInStr($v_Value, $g_asTabHeaderText[0]) Then $g_sHeaderCells &= 'A' & $i_Row + 1 & ','
            If StringInStr(_ArrayToString($g_asTabNames, '|'), $v_Value) Then $g_sTabNameCells &= 'A' & $i_Row + 1 & ','

            ExitLoop
        EndIf

        ; do different things based on the specified control
        If $i = $ENTRYS_COL Or $i = $REMARKS_COL Then ; these are input controls
            ; write the value to the array
            $aid_ControlID[$i_Row][$i] = $v_Value
        Else ; these are checkbox controls
            ; get the checked state of the control
            $aid_ControlID[$i_Row][$i] = GetCheckBoxValue($aid_ControlID[$i_Row][$i])
        EndIf
    Next
EndFunc   ;==>CreateArrayOfControlValues

; create the scrolling guis and their controls that go on each of the tabs
Func CreateTabGUIs(ByRef $as_RangeRead, $i_ArrayRow, $h_MainGUI)
    ; create the gui for the controls and add it to the global controls array
    $g_aidControls[$i_ArrayRow] = GUICreate('', 770, 370, 23, 270, $WS_POPUP, $WS_EX_MDICHILD, $h_MainGUI)
    GUISetBkColor($COLOR_WHITE)

    ; create the temp array. This will hold all the controlIDs
    Local _
            $aid_Temp[70][$ARRAYMAX_COLS] = [[0]] ; this is a 0 based array
    Local _
            $s_Value = '' ; value to give the control
    Local _
            $i_ArrayRowCount = 0 ; keeps track of number of array rows (controls rows added)
    Local _ ; variables for the control spacing
            $i_Left = 0, _ ;_           starting left position
            $i_Rows = 60, _ ;_          initial number of rows
            $i_Spacing = 60, _ ;_       control padding
            $i_Top = 18 ;_              initial top position

    For $i = 0 To UBound($aid_Temp) - 1 ; 0 to 70 rows of controls . if you add more this number will need to change
        ; get the value to add to the control from the array returned by CellRange_GetValues()
        $s_Value = $as_RangeRead[$i][$i_ArrayRow] ; array is 0 based and has 53 elements filled

        ; don't create anymore controls if the value is blanks
        If Not $s_Value Then ExitLoop

        ; calculate additional spacing
        $i_Left = (Int($i / $i_Rows)) + $i_Spacing
        $i_Top = (30.7 * Mod($i, $i_Rows)) ; - 30

        ; check if the string is upper case. Strip any none letter characters out for testing
        ; as digits/punctuation/whitespace will cause StringIsUpper() to return 0.
        If StringIsUpper(StringRegExpReplace($s_Value, '[^A-Za-z]', '')) Then ; assume its a heading
            ; draw the input and add the controlID to the correct gui array
            $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 730, 20, $WS_EX_CLIENTEDGE)
            GUICtrlSetFont(-1, 8, 800)
            GUICtrlSetColor(-1, $COLOR_BLACK)
            GUICtrlSetBkColor(-1, $COLOR_SILVER)

            ; set the other columns to 0 as there are no other controls being added
            $aid_Temp[$i][$NA_COL] = 0
            $aid_Temp[$i][$NOERROR_COL] = 0
            $aid_Temp[$i][$WITHERROR_COL] = 0
            $aid_Temp[$i][$REMARKS_COL] = 0
        Else ; assume it's a sub
            ; draw the input
            $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateInput($s_Value, $i_Left - 50, $i_Top, 155, 20, BitOR($GUI_SS_DEFAULT_INPUT, $ES_READONLY), $WS_EX_TRANSPARENT) ; use an input
            GUICtrlSetBkColor(-1, $COLOR_WHITE)
;~          $aid_Temp[$i][0] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 155, 40) ; use a label if you want

            ; draw the N/A check box
            $aid_Temp[$i][$NA_COL] = GUICtrlCreateCheckbox('', $i_Left + 165, $i_Top, 15, 20)

            ; draw the no error check box
            $aid_Temp[$i][$NOERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 260, $i_Top, 15, 20)

            ; draw the with error check box
            $aid_Temp[$i][$WITHERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 355, $i_Top, 15, 20)

            ; draw the edit control
            $aid_Temp[$i][$REMARKS_COL] = GUICtrlCreateEdit('', $i_Left + 440, $i_Top, 240, 24, BitOR($ES_AUTOVSCROLL, $ES_WANTRETURN))
        EndIf

        $i_ArrayRowCount += 1 ; increase the number of rows added to the array
    Next

    ; add scroll bars to the current gui
    _GUIScrollbars_Generate($g_aidControls[$i_ArrayRow], 0, 2000)

    ; redim the array to remove any blanks
    ReDim $aid_Temp[$i_ArrayRowCount][$ARRAYMAX_COLS]

;~  _ArrayDisplay($aid_Temp, 'CreateTabGUIs ($aid_Temp)') ; for debugging

    ; returns a 0 based array of controlIDs
    Return $aid_Temp
EndFunc   ;==>CreateTabGUIs

Func Draw_GUI()
    Local $h_MainGUI = GUICreate('Test Form', 820, 740, -1, -1)
    GUISetBkColor(0x38A7D2)
    GUISetOnEvent($GUI_EVENT_CLOSE, 'CloseProgram')

    GUICtrlCreatePic(@ScriptDir & '\Image.jpg', 690, 15, 120, 120)

    GUICtrlCreateLabel('Welcome: ' & GetFullName(@UserName), 17, 135, 500, 16)
    GUICtrlSetColor(-1, $COLOR_WHITE)
    GUICtrlSetFont(-1, 9, $FW_BOLD)

    #Region #### Main Menu ###########################
    Local $id_Menu = GUICtrlCreateMenu('File')
    GUICtrlCreateMenuItem('Close', $id_Menu)
    GUICtrlSetOnEvent(-1, 'CloseProgram')
    GUICtrlCreateMenuItem('Export', $id_Menu)
    GUICtrlSetOnEvent(-1, 'MainMenu_FileExport')

    $id_Menu = GUICtrlCreateMenu('Edit')
    GUICtrlSetState(-1, $GUI_Disable)

    $id_Menu = GUICtrlCreateMenu('Option')
    GUICtrlSetState(-1, $GUI_Disable)

    $id_Menu = GUICtrlCreateMenu('View')
    GUICtrlCreateMenuItem('Exit', $id_Menu)
    GUICtrlSetState(-1, $GUI_Disable)
    GUICtrlSetOnEvent(-1, 'MainMenu_ViewExit')

    $id_Menu = GUICtrlCreateMenu('Help')
    GUICtrlCreateMenuItem('About', $id_Menu)
    GUICtrlSetOnEvent(-1, 'MainMenu_HelpAbout')
    #EndRegion #### Main Menu ###########################

    #Region #### Employee Info #######################
    GUICtrlCreateGroup('Employee Information', 16, 8, 281, 121)

    GUICtrlCreateLabel('Emp#:', 33, 35, 35, 17)
    $g_aidControls[$EMPNUMBER_INP] = GUICtrlCreateInput('1234567', 72, 32, 217, 21)
    GUICtrlSetState(-1, $GUI_Disable)

    GUICtrlCreateLabel('Name:', 33, 59, 35, 17)
    $g_aidControls[$EMPNAME_INP] = GUICtrlCreateInput(GetFullName(@UserName), 72, 56, 217, 21)
    GUICtrlSetState(-1, $GUI_Disable)

    GUICtrlCreateLabel('Process:', 24, 83, 45, 17)
    $g_aidControls[$EMPPROCESS_INP] = GUICtrlCreateInput('First Process', 72, 80, 217, 21)
    GUICtrlSetState(-1, $GUI_Disable)

    GUICtrlCreateGroup('', -99, -99, 1, 1)
    #EndRegion #### Employee Info #######################

    #Region #### whatever this is ####################
    GUICtrlCreateLabel('AID:', 25, 170, 25, 17)
    GUICtrlSetFont(-1, 10, $FW_BOLD)

    $g_aidControls[$MYSTERY_LBL] = GUICtrlCreateLabel('ABZ123456', 60, 170, 90, 17) ; remove this if you don't alter the text
    GUICtrlSetColor(-1, $COLOR_WHITE)
    GUICtrlSetFont(-1, 10, $FW_BOLD)
    #EndRegion #### whatever this is ####################

    #Region #### Confirmation Checkboxes #############
    $g_aidControls[$AGREE_CHK] = GUICtrlCreateCheckbox('  I agree that I have checked the entry(s) above.', 25, 655, 265, 17)

    $g_aidControls[$ACCEPT_CHK] = GUICtrlCreateCheckbox('  Accept', 25, 675, 60, 17)
    GUICtrlCreateLabel('*', 85, 680, 40, 17)
    GUICtrlSetColor(-1, $COLOR_RED)
    GUICtrlSetFont(-1, 10, $FW_BOLD)

    $g_aidControls[$REJECT_CHK] = GUICtrlCreateCheckbox('  Reject', 25, 695, 60, 17)
    GUICtrlCreateLabel('*', 85, 695, 40, 17)
    GUICtrlSetColor(-1, $COLOR_RED)
    GUICtrlSetFont(-1, 10, $FW_BOLD)
    #EndRegion #### Confirmation Checkboxes #############

    #Region #### Submit Report Group #################
    $Group1 = GUICtrlCreateGroup("Submit Report", 600, 647, 200, 65)

    $g_aidControls[$LAYOUTONE_RDO] = GUICtrlCreateRadio("Layout 1", 615, 665, 70, 17)
    GUICtrlSetState(-1, $GUI_CHECKED)
    GUICtrlSetOnEvent(-1, 'Layout1_Selected')

    GUICtrlCreateRadio("Layout 2", 615, 685, 70, 17)
    GUICtrlSetOnEvent(-1, 'Layout2_Selected')

    $g_aidControls[$SUBMIT_BTN] = GUICtrlCreateButton('Submit Report', 690, 667, 100, 33)
    GUICtrlSetOnEvent(-1, 'Report_Submit')

    GUICtrlCreateGroup("", -99, -99, 1, 1)
    #EndRegion #### Submit Report Group #################

    #Region #### Create Tabs #########################
    Draw_Tabs($h_MainGUI)
    #EndRegion #### Create Tabs #########################

    GUISetState(@SW_SHOW, $h_MainGUI) ; show the main gui
    Sleep(80) ; add a sleep to try and get them to show at the same time
    GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI]) ; show the first tab gui
EndFunc   ;==>Draw_GUI

; draw the group on controls that sit above the tab guis
Func Draw_TabHeaders()
    ; this draws the headers over the columns on the tab gui. The values are pulled from the $g_asTabHeaderText array
    GUICtrlCreateGroup('', 30, 215, 755, 50)
    GUICtrlCreateLabel($g_asTabHeaderText[$ENTRYS_COL], 50, 232, 80, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$NA_COL], 238, 232, 40, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$NOERROR_COL], 315, 232, 70, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$WITHERROR_COL], 405, 232, 85, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$REMARKS_COL], 590, 232, 90, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
EndFunc   ;==>Draw_TabHeaders

Func Draw_Tabs($h_MainGUI)
    ; create the main tab control and add it's ID to the control array
    $g_aidControls[$MAIN_TAB] = GUICtrlCreateTab(20, 190, 780, 455, $WS_EX_MDICHILD)
    GUICtrlSetOnEvent(-1, 'TabChange') ; do summat on tab change

    ; get the strings that will populate the gui controls from the excel sheet. These will be the "Entry{s}"
    ; set the range as the first column 'A', to the last column that contains the layout for the guis (currently 'C')
    ; ### add some error checking for CellRange_GetValues ####
    Local $as_RangeRead = CellRange_GetValues(CellRange_CreateFromInteger($FRONTMATTERGUI + 1, $TABCOUNT))

    ; loop to create the required number of tabs, guis and their controls
    For $i = $FRONTMATTERGUI To $TABCOUNT - 1 ; - 1 because $g_asTabNames is a 0 based array
        GUICtrlCreateTabItem($g_asTabNames[$i])

        ; create the headers for each tab. These are listed in $g_asTabHeaderText
        Draw_TabHeaders()

        Switch $i
            Case $FRONTMATTERGUI ; gui 1
                ; create the array containing the controlIDs. returns a 0 based array of controlIDs
                $aid_FrontMatterGUI = CreateTabGUIs($as_RangeRead, $i, $h_MainGUI)

            Case $BODYMATTERGUI ; gui 2
                ; create the array containing the controlIDs
                $aid_BodyMatterGUI = CreateTabGUIs($as_RangeRead, $i, $h_MainGUI)

            Case $BACKMATTERGUI ; gui 3
                ; create the array containing the controlIDs
                $aid_BackMatterGUI = CreateTabGUIs($as_RangeRead, $i, $h_MainGUI)
        EndSwitch

        ; switch to the main gui and tab after creating the new guis or else the control creation gets bollocksed Up
        ; and the tabs or controls are not visible
        GUISwitch($h_MainGUI, $g_aidControls[$MAIN_TAB])
    Next

    GUICtrlCreateTabItem('')
EndFunc   ;==>Draw_Tabs

; get the state of the checkbox
Func GetCheckBoxValue($id_ControlID)
    If GUICtrlRead($id_ControlID) = $GUI_CHECKED Then Return 'Checked'
    Return ''
EndFunc   ;==>GetCheckBoxValue

Func GetFullName($sUserName) ; I can't test this
;~  $strComputer = 'localhost' ; do you need to specify this????

;~  Local $objWMIService = ObjGet('winmgmts:\\' & $strComputer & '\root\CIMV2')
    Local $objWMIService = ObjGet('winmgmts:\\localhost\root\CIMV2')
    Local $colItems = $objWMIService.ExecQuery('SELECT * FROM Win32_UserAccount WHERE Name = '' & $sUserName & ''', 'WQL', 0x10 + 0x20)
    If Not IsObj($colItems) Then Return SetError(1, 0, '')

    For $objItem In $colItems
        Return $objItem.FullName
    Next
EndFunc   ;==>GetFullName

Func Layout1_Selected()
    $g_iMultipleSheets = 1
EndFunc   ;==>Layout1_Selected

Func Layout2_Selected()
    $g_iMultipleSheets = 0
EndFunc   ;==>Layout2_Selected

Func MainMenu_FileExport()
    ; add commands later
EndFunc   ;==>MainMenu_FileExport

Func MainMenu_HelpAbout()
    ; add commands later
EndFunc   ;==>MainMenu_HelpAbout

Func MainMenu_ViewExit()
    ; add commands later
EndFunc   ;==>MainMenu_ViewExit

Func TabChange()
    Switch GUICtrlRead($g_aidControls[$MAIN_TAB])
        Case $FRONTMATTERGUI ; Front Matter tab
            GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI])
        Case $BODYMATTERGUI ; body Matter tab
            GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_SHOW, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI])
        Case $BACKMATTERGUI ; back matter tab
            GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_SHOW, $g_aidControls[$BACKMATTERGUI])
    EndSwitch
EndFunc   ;==>TabChange

; gets the currently selected layout type for the report
Func Report_GetLayout()
    ; return the state of the layout 1 radio button
    If GUICtrlRead($g_aidControls[$LAYOUTONE_RDO]) = $GUI_CHECKED Then Return 1
    Return 2
EndFunc   ;==>Report_GetLayout

; submit your report
Func Report_Submit()
    SplashTextOn("submit", "Submitting report...", 300, 50, -1, -1, $DLG_NOTITLE + $DLG_TEXTVCENTER, '', '', $FW_BOLD)
;~  Local $o_WorkBook = ''

    ; open an excel instance
    Local $o_Excel = _Excel_Open(False)
    If @error Then Return SetError(1, _
            MsgBox($MB_SYSTEMMODAL, _
            '_Excel_Open', _
            'Error creating the Excel application object' & @CRLF & '@error = ' & @error & ', @extended = ' & @extended), 0)

    ; set the number of sheets to add when opening a new workbook
    Local $iSheets = $TABCOUNT
    If Not $g_iMultipleSheets Then $iSheets = 1

    ; open a new excel book
    Local $o_WorkBook = _Excel_BookNew($o_Excel, $iSheets) ; $TABCOUNT is the number of tabs in your program and so the number of sheets we create for submitting
    If @error Then Return SetError(2, _
            MsgBox($MB_SYSTEMMODAL, _
            '_Excel_BookNew', _
            'Error creating new workbook' & @CRLF & '@error = ' & @error & ', @extended = ' & @extended), _Excel_Close($o_Excel))

;~  ; this will read the control values to an array and write the array to an excel file
    WriteArrayValuesToSpreadsheet($o_WorkBook)

    If Not @error Then
        ; create a file path based on time. If you want to keep overwriting the files the create a definite file name
        ; and use the commented line
        Local $s_FilePath = @ScriptDir & '\Report_' & @YEAR & '-' & @MON & '-' & @MDAY & '_' & @HOUR & @MIN & @SEC & '.xlsx'

        ; ; save the created report and overwrite the previous
;~      _Excel_BookSaveAs($o_WorkBook, $s_FilePath, $xlWorkbookDefault, True)

        ; save the created report to a new file
        _Excel_BookSaveAs($o_WorkBook, $s_FilePath, $xlWorkbookDefault)

        If @error Then MsgBox($MB_SYSTEMMODAL, _
                "_Excel_BookSaveAs", _
                "Error saving workbook to '" & $s_FilePath & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    EndIf

    _Excel_Close($o_Excel)

    ; update the splash text
    ControlSetText("submit", "", "Static1", 'Opening report...')

    ; open the report, remove if you don't need to
    ShellExecute($s_FilePath)

    SplashOff()
EndFunc   ;==>Report_Submit

; go through the tabs and controls and write the values to a spreadsheet
Func WriteArrayValuesToSpreadsheet($o_WorkBook, $i_Layout = Report_GetLayout())
    Local _ ; $aa_ControlArrays is an array of arrays. The arrays have the controlIDs for the relevant tab gui controls
            $aa_ControlArrays[$TABCOUNT + 1] = [$TABCOUNT, $aid_FrontMatterGUI, $aid_BodyMatterGUI, $aid_BackMatterGUI], _
            $as_Values = 0, _ ; the array that will hold the values of the gui controls
            $as_Temp = 0 ;_     a temp array for modding

    ; set the number of loop to perform when writing.
    ; If layout 1 is used, there will be a seperate spreadsheet for each tab so 3 loops
    ; if layout 2 is used all the tabs are written to one spreadsheet so only 1 loop is needed
    Local _
            $i_Total = $aa_ControlArrays[0], _ ;_   set as default layout 1 and loop through the all the arrays
            $i_Concatenated = 0 ;_                  Set the state of the array being processed 0 = multiple 1 = 1 concatenated array

    ; Change if the layout is 2 and just loop through the concatenated array
    If $i_Layout > 1 Then $i_Total = 1 ; layout 2 selected

    #Region #### only needed for cell outside borders if not interested the code can be deleted ###
    Local _ ; Excel constants not in ExcelConstants.au3
            $i_xlAutomatic = -4105, _
            $i_xlContinuous = 1, _
            $i_xlThin = 2, _
            $i_xlEdgeLeft = 7, _
            $i_xlEdgeTop = 8, _
            $i_xlEdgeBottom = 9, _
            $i_xlEdgeRight = 10
    #EndRegion #### only needed for cell outside borders if not interested the code can be deleted ###

    ; loop through the arrays
    For $i = 1 To $i_Total
        ; on each loop copy the nested array to a temp one, this will be filled with the values of the controls
        $as_Values = $aa_ControlArrays[$i] ; set a starting array
        _ArrayInsert($as_Values, 0, _ArrayToString($g_asTabHeaderText, '|')) ; add the headers to each array

        #Region #### This determines which array(s) to use for the write ####
        If $i_Layout = 2 Then
            _ArrayInsert($as_Values, 0, $g_asTabNames[0]) ; add the first tab name to the array

            ; loop through the remaining arrays
            For $j = 2 To $TABCOUNT ; $TABCOUNT = 3
                $as_Temp = $aa_ControlArrays[$j] ; copy to a temp array so we can add the tab names and headet text
                _ArrayInsert($as_Temp, 0, $g_asTabNames[$j - 1]) ; add the next tab name to the array
                _ArrayInsert($as_Temp, 1, _ArrayToString($g_asTabHeaderText, '|')) ; add the next header text

                ; joining them $as_Values
                _ArrayConcatenate($as_Values, $as_Temp)
            Next
        EndIf
        #EndRegion #### This determines which array(s) to use for the write ####

        ; loop through the temp array
        For $j = 0 To UBound($as_Values) - 1
            ; update the array row with the control values
            CreateArrayOfControlValues($as_Values, $j)
        Next

        ; activate the sheet
        $o_WorkBook.Sheets($i).Activate

        ; write the array to an excel spreadsheet
        _Excel_RangeWrite($o_WorkBook, $i, $as_Values)
        If @error Then Return SetError(@error, @extended, 'Error Writing ' & $g_asTabNames[$i - 1] & ' to sheet')

        ; adjust the sheet properties whilst it's active
        With $o_WorkBook.ActiveSheet
            If $g_iMultipleSheets Then ; we are writing multiple sheets
                ;   set the sheet name
                .Name = $g_asTabNames[$i - 1]
            EndIf

            ; set the column letters that will auto size to fit the text
            .Columns(CellRange_CreateFromInteger($ENTRYS_COL + 1, $ARRAYMAX_COLS)).AutoFit

            ; this is where the cells that have the tab names in get nodified
            #Region #### Tab Name Cells ####
            If $g_sTabNameCells Then
                $g_sTabNameCells = CellRange_CreateFromString($g_sTabNameCells, $ARRAYMAX_COLS)

                ; set the font weight as bold
                .Range($g_sTabNameCells).Font.FontStyle = 'Bold'

                ; set the tab name cells back colour
                .Range($g_sTabNameCells).Interior.Color = 0xBFBFBF

                ; merge the cells
                .Range($g_sTabNameCells).Merge

                ; centre align the cells
                .Range($g_sTabNameCells).HorizontalAlignment = $xlCenter
            EndIf
            #EndRegion #### Tab Name Cells ####

            ; this is where the cells that have Entry(s), N/A, No Error etc get modified
            #Region #### Header Cells ####
            If $g_sHeaderCells Then
                $g_sHeaderCells = CellRange_CreateFromString($g_sHeaderCells, $ARRAYMAX_COLS)

                ; set the font weight as bold
                .Range($g_sHeaderCells).Font.FontStyle = 'Bold'

                ; centre align the cells
                .Range($g_sHeaderCells).HorizontalAlignment = $xlCenter

                ; set the header cells back colour
                .Range($g_sHeaderCells).Interior.Color = 0xFFE6CC
            EndIf
            #EndRegion #### Header Cells ####

            ; this is where the cells that have the section text like CATEGORY, HISTORY DATES etc get modified
            #Region #### Section Cells ####
            If $g_sSectionCells Then
                $g_sSectionCells = CellRange_CreateFromString($g_sSectionCells, $ARRAYMAX_COLS)

                ; set the font weight as bold
                .Range($g_sSectionCells).Font.FontStyle = 'Bold'

                ; set the header cells back colour
                .Range($g_sSectionCells).Interior.Color = 0xDAEFE2

                ; add outside borders
                .Range($g_sSectionCells).Borders($i_xlEdgeTop).LineStyle = $i_xlContinuous
                .Range($g_sSectionCells).Borders($i_xlEdgeLeft).LineStyle = $i_xlContinuous
                .Range($g_sSectionCells).Borders($i_xlEdgeRight).LineStyle = $i_xlContinuous
                .Range($g_sSectionCells).Borders($i_xlEdgeBottom).LineStyle = $i_xlContinuous
            EndIf
            #EndRegion #### Section Cells ####
        EndWith

        CellRange_ResetGlobalValues()
        Sleep(20)
    Next

    $o_WorkBook.Sheets(1).Activate ; activate the first sheet
    CellRange_ResetGlobalValues()
EndFunc   ;==>WriteArrayValuesToSpreadsheet

Edited January 7, 2018 by benners

KickStarter15 · January 8, 2018

@benners,

This is a work of genius with 1000% above level of coding o:) .... I never though you could make it more interesting. REALLY , higher with thumbs up is what you are benners.

This is the first time I gave 3 likes in one person in one thread. Worth to have it!!!! Thank you very very very much benners, you've made my day complete with a smile.

6 hours ago, benners said:

Was a nightmare but interesting learning

Indeed a nightmare, but an angel. I really need to learn a lot, I mean a lotsssssss!!! hahaha. When can I have this kind of nightmare.

Func GetFullName($sUserName) ; I can't test this
;~  $strComputer = 'localhost' ; do you need to specify this????

;~  Local $objWMIService = ObjGet('winmgmts:\\' & $strComputer & '\root\CIMV2')
    Local $objWMIService = ObjGet('winmgmts:\\localhost\root\CIMV2')
    Local $colItems = $objWMIService.ExecQuery('SELECT * FROM Win32_UserAccount WHERE Name = '' & $sUserName & ''', 'WQL', 0x10 + 0x20)
    If Not IsObj($colItems) Then Return SetError(1, 0, '')

    For $objItem In $colItems
        Return $objItem.FullName
    Next
EndFunc   ;==>GetFullName

Full name is not showing, but I think I can manage this.

benners · January 8, 2018

I found it interesting doing it and I learned somethings as well so I am sure it can be improved. I'll probably keep looking at it. Just post back if you need me to further explain what stuff does. I tried to make it easy to add tabs and controls by using the arrays but it also makes it look more complicated and didn't alter your GUI design too much. There are still the other inputs and checkboxes to deal with but I don't know what you have planned for those with regards to storing those values.

There are other ideas such as disabling the submit button unless the I agree checkbox is checked etc, but without knowing what you have planned it would just be going over the top. As I said, it has been quite addictive doing it and certainly passed the time.

If the GetFullName function worked before then I have broke it. Just restore it to how you had it before in your first post. I only changed the one line and commented two others.

KickStarter15 · January 8, 2018

Yes, Benners. Actually I'm currently wondering how can I disable some checkboxes using my below code (this is my old code before it was greatly modified by you). This is to disable inline checkboxes once one of them was checked already or selected.

If GUICtrlRead($Error[$iIndex]) = $GUI_CHECKED Then
    GUICtrlSetState($nError[$iIndex], $GUI_Disable)
    GUICtrlSetState($Error[$iIndex], $GUI_Enable)
    GUICtrlSetState($NA[$iIndex], $GUI_Disable)
Else
    GUICtrlSetState($NA[$iIndex], $GUI_Enable)
    GUICtrlSetState($nError[$iIndex], $GUI_Enable)
EndIf

45 minutes ago, benners said:

There are other ideas such as disabling the submit button unless the I agree checkbox is checked etc, but without knowing what you have planned it would just be going over the top.

Yes, please. That would be awesome. I agree that these "I agree" and "I accept/I reject" checkboxes must be checked first before clicking submit button.

Honestly, you have more ideas than I am. No wonder I'm stuck in my level and your more than everyone.

50 minutes ago, benners said:

If the GetFullName function worked before then I have broke it. Just restore it to how you had it before in your first post. I only changed the one line and commented two others.

Can it be possible to add this in first sheet of the excel? Like my attached sample excel output.

Report_2018-01-08_135236.xlsx

farouk12 · January 8, 2018

Maybe

Func _Switch($Id,$FirstId) ;$FirstId : First checkbox ID
$N=int(($Id-$FirstId)/3) ;Get the raw of check boxes
for $i = $N*3 to $N*3 + 2 
if not (($i + $FirstId) = $Id) then
GUICtrlSetState($i+$FirstId,4)
else
GUICtrlSetState($i+$FirstId,1)
endif
next
endfunc

Edited January 8, 2018 by farouk12

benners · January 8, 2018

3 hours ago, KickStarter15 said:

No wonder I'm stuck in my level and your more than everyone.

Well I wouldn't go that far . Calm down :hyper:

I'll have a look at the other stuff but the checkboxes work as discussed. I'm busy for the next few day so won't be around as much. Feel free to have a try with the inputs

3 hours ago, KickStarter15 said:

Yes, Benners. Actually I'm currently wondering how can I disable some checkboxes using my below code (this is my old code before it was greatly modified by you). This is to disable inline checkboxes once one of them was checked already or selected.

With this you will have to modify the CreateTabGUIs() function where the checkboxes are created and use GUICtrlSetOnEvent to set a function for the click. The function will be the same for all the checkboxes and if you use @GUI_CtrlId to get the control id you can change the state from there

benners · January 8, 2018

Well that was easier than I thought. How's this?. You will still need to go and add error checking though. I'll leave that bit up to you

#Region #### Includes ################################
#include <ButtonConstants.au3>
#include <ColorConstants.au3>
#include <EditConstants.au3>
#include <Excel.au3>
#include <FontConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIScrollbars_Ex.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Array.au3>
#EndRegion #### Includes ################################

Opt('GUIOnEventMode', 1)
HotKeySet('{ESC}', 'CloseProgram')

#Region #### Globals #################################
; these are integers that relate to where the controliDs are located in $g_aidControls.
; The control ID can be retrieved like so, $g_aidControls[$EMPPROCESS_INP]
Global Enum _ ; values are 0 to 13
        $FRONTMATTERGUI, _ ;_   gui 1
        $BODYMATTERGUI, _ ;_    gui 2
        $BACKMATTERGUI, _ ;_    gui 3
        $TABCOUNT, _ ; #### IMPORTANT #### this must be after the last gui. it signifies how many tabs there will be and is used in other functions
        $EMPNUMBER_INP, _ ;_    employee number input id
        $EMPNAME_INP, _ ;_      employee name input id
        $EMPPROCESS_INP, _ ;_   employee process input id
        $AID_LBL, _ ;_      some label I don't know if you use id
        $AGREE_CHK, _ ;_        I agree checkbox id
        $ACCEPT_CHK, _ ;_       accept checkbox id
        $REJECT_CHK, _ ;_       reject checkbox id
        $LAYOUTONE_RDO, _ ;_        radio button for layout 1 selection
        $SUBMIT_BTN, _ ;_       submit button id
        $MAIN_TAB, _ ;_         main tab id
        $ARRAYMAX_ROWS ;_       number of rows to give the array its 0 based

; create the array to hold the remaining controlIDs. These can be referenced
; later when writing their values to the excel sheet
Global $g_aidControls[$ARRAYMAX_ROWS]

; create the arrays to hold the all controlIDs that will be created on the tab guis by CreateTabGUIs() called by Draw_Tabs() function
; These can be referenced later when writing their values to the excel sheet
Global _
        $aid_FrontMatterGUI = '', _
        $aid_BodyMatterGUI = '', _
        $aid_BackMatterGUI = ''

; these are the tab names and will be used as the spreadsheet names when the report is submitted
; these have the same value as the Global Enum $FRONTMATTERGUI to $BACKMATTERGUI and are used in varios functions
Global $g_asTabNames[$TABCOUNT] = [ _ ; values are 0 to 2
        'Front Matter', _
        'Body Matter', _
        'Back Matter']

; these are integers that relate to where the rows are for each set of controls in the $aid_FrontMatterGUI array etc
; they are also the same values as their matching strings index in the $g_asTabHeaderText array
Global Enum _ ; values are 0 to 5
        $ENTRYS_COL, _
        $NA_COL, _
        $NOERROR_COL, _
        $WITHERROR_COL, _
        $REMARKS_COL, _
        $ARRAYMAX_COLS

; this is the array that holds the tab header text strings. These are written to the first row of the array that matches the tab and gui
; I.E -
;   Tab = 'Front Matter'
;   Array with the controlIDs = $aid_FrontMatterGUI
Global $g_asTabHeaderText[$ARRAYMAX_COLS] = [ _ ; 0 based array
        'Entry(s)', _
        'N/A', _
        'No Error', _
        'With Error', _
        'Remarks']

; this keeps track of if the report layout is one or two
Global $g_iMultipleSheets = 1

; these keep track of the cells that need tweaking in the report
Global _
        $g_sHeaderCells = '', _ ;_  These are the strings above the inline controls such as Entry(s) N/A etc
        $g_sSectionCells = '', _ ;_ Sections, these are upper case strings like CATEGORY, HISTORY DATES
        $g_sTabNameCells = '' ;_    tab names
#EndRegion #### Globals #################################

Draw_GUI()

While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch

    Sleep(50)
WEnd

; #### you need to fix this ####
; i have guessed the line as you are trying to use $sUserName but in the uncommented line it is used as a string
; still returns blank for me
Func GetFullName($sUserName) ; I can't test this
    Local $strComputer = 'localhost'

    Local $objWMIService = ObjGet('winmgmts:\\' & $strComputer & '\root\CIMV2')
;~  Local $colItems = $objWMIService.ExecQuery('SELECT * FROM Win32_UserAccount WHERE Name = '' & $sUserName & ''', 'WQL', 0x10 + 0x20)
    Local $colItems = $objWMIService.ExecQuery("SELECT * FROM Win32_UserAccount WHERE Name = '" & $sUserName &  "'", "WQL", 0x10 + 0x20)
    If Not IsObj($colItems) Then Return SetError(1, 0, '')

    For $objItem In $colItems
        Return $objItem.FullName
    Next
EndFunc   ;==>GetFullName

#Region #### CheckBox actions ########################
; get the state of the checkbox
Func CheckBox_ReturnStringState($id_ControlID)
    If GUICtrlRead($id_ControlID) = $GUI_CHECKED Then Return 'Checked'
    Return ''
;~  Return 'Unchecked' ; use this if you want it to return unchecked instead of blank
EndFunc   ;==>CheckBox_ReturnStringState

; inline check boxes
Func NaChk_SetState()
    ; get the controlID of the checkbox that was clicked
    Local $i_ControlID = @GUI_CtrlId

    GUICtrlSetState(@GUI_CtrlId + 1, $GUI_UNCHECKED) ; uncheck the No Error checkbox
    GUICtrlSetState(@GUI_CtrlId + 2, $GUI_UNCHECKED) ; uncheck the With Error checkbox
EndFunc   ;==>NaChk_SetState

Func NoErrorChk_SetState()
    ; get the controlID of the checkbox that was clicked
    Local $i_ControlID = @GUI_CtrlId

    GUICtrlSetState(@GUI_CtrlId - 1, $GUI_UNCHECKED) ; uncheck the NA checkbox
    GUICtrlSetState(@GUI_CtrlId + 1, $GUI_UNCHECKED) ; uncheck the With Error checkbox
EndFunc   ;==>NoErrorChk_SetState

Func WithErrorChk_SetState()
    ; get the controlID of the checkbox that was clicked
    Local $i_ControlID = @GUI_CtrlId

    GUICtrlSetState(@GUI_CtrlId - 1, $GUI_UNCHECKED) ; uncheck the No Error checkbox
    GUICtrlSetState(@GUI_CtrlId - 2, $GUI_UNCHECKED) ; uncheck the NA checkbox
EndFunc   ;==>WithErrorChk_SetState

; accept, reject
Func AcceptChk_Clicked()
    ; uncheck the reject control
    GUICtrlSetState($g_aidControls[$REJECT_CHK], $GUI_UNCHECKED)

    ; check if the submit button is to be enabled
    SubmitBtn_SetState()
EndFunc   ;==>AcceptChk_Clicked

Func RejectChk_Clicked()
    ; uncheck the accept control
    GUICtrlSetState($g_aidControls[$ACCEPT_CHK], $GUI_UNCHECKED)
    SubmitBtn_SetState()
EndFunc   ;==>RejectChk_Clicked
#EndRegion #### CheckBox actions ########################

#Region #### GUI Actions #############################
Func CloseProgram()
    Exit
EndFunc   ;==>CloseProgram

; create the scrolling guis and their controls that go on each of the tabs
Func CreateTabGUIs(ByRef $as_RangeRead, $i_ArrayRow, $h_MainGUI)
    ; create the gui for the controls and add it to the global controls array
    $g_aidControls[$i_ArrayRow] = GUICreate('', 770, 370, 23, 270, $WS_POPUP, $WS_EX_MDICHILD, $h_MainGUI)
    GUISetBkColor($COLOR_WHITE)

    ; create the temp array. This will hold all the controlIDs
    Local _
            $aid_Temp[70][$ARRAYMAX_COLS] = [[0]] ; this is a 0 based array
    Local _
            $s_Value = '' ; value to give the control
    Local _
            $i_ArrayRowCount = 0 ; keeps track of number of array rows (controls rows added)
    Local _ ; variables for the control spacing
            $i_Left = 0, _ ;_           starting left position
            $i_Rows = 60, _ ;_          initial number of rows
            $i_Spacing = 60, _ ;_       control padding
            $i_Top = 18 ;_              initial top position

    For $i = 0 To UBound($aid_Temp) - 1 ; 0 to 70 rows of controls . if you add more this number will need to change
        ; get the value to add to the control from the array returned by CellRange_GetValues()
        $s_Value = $as_RangeRead[$i][$i_ArrayRow] ; array is 0 based and has 53 elements filled

        ; don't create anymore controls if the value is blanks
        If Not $s_Value Then ExitLoop

        ; calculate additional spacing
        $i_Left = (Int($i / $i_Rows)) + $i_Spacing
        $i_Top = (30.7 * Mod($i, $i_Rows)) ; - 30

        ; check if the string is upper case. Strip any none letter characters out for testing
        ; as digits/punctuation/whitespace will cause StringIsUpper() to return 0.
        If StringIsUpper(StringRegExpReplace($s_Value, '[^A-Za-z]', '')) Then ; assume its a section
            ; draw the input and add the controlID to the correct gui array
            $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 730, 20, $WS_EX_CLIENTEDGE)
            GUICtrlSetFont(-1, 8, 800)
            GUICtrlSetColor(-1, $COLOR_BLACK)
            GUICtrlSetBkColor(-1, $COLOR_SILVER)

            ; set the other columns to 0 as there are no other controls being added
            $aid_Temp[$i][$NA_COL] = 0
            $aid_Temp[$i][$NOERROR_COL] = 0
            $aid_Temp[$i][$WITHERROR_COL] = 0
            $aid_Temp[$i][$REMARKS_COL] = 0
        Else ; assume it's a sub
            ; draw the input
            $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateInput($s_Value, $i_Left - 50, $i_Top, 155, 20, BitOR($GUI_SS_DEFAULT_INPUT, $ES_READONLY), $WS_EX_TRANSPARENT) ; use an input
            GUICtrlSetBkColor(-1, $COLOR_WHITE)
;~          $aid_Temp[$i][0] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 155, 40) ; use a label if you want

            ; draw the N/A check box
            $aid_Temp[$i][$NA_COL] = GUICtrlCreateCheckbox('', $i_Left + 165, $i_Top, 15, 20)
            GUICtrlSetOnEvent(-1, 'NaChk_SetState')

            ; draw the no error check box
            $aid_Temp[$i][$NOERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 260, $i_Top, 15, 20)
            GUICtrlSetOnEvent(-1, 'NoErrorChk_SetState')

            ; draw the with error check box
            $aid_Temp[$i][$WITHERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 355, $i_Top, 15, 20)
            GUICtrlSetOnEvent(-1, 'WithErrorChk_SetState')

            ; draw the edit control
            $aid_Temp[$i][$REMARKS_COL] = GUICtrlCreateEdit('', $i_Left + 440, $i_Top, 240, 24, BitOR($ES_AUTOVSCROLL, $ES_WANTRETURN))
        EndIf

        $i_ArrayRowCount += 1 ; increase the number of rows added to the array
    Next

    ; add scroll bars to the current gui
    _GUIScrollbars_Generate($g_aidControls[$i_ArrayRow], 0, 2000)

    ; redim the array to remove any blanks
    ReDim $aid_Temp[$i_ArrayRowCount][$ARRAYMAX_COLS]

;~  _ArrayDisplay($aid_Temp, 'CreateTabGUIs ($aid_Temp)') ; for debugging

    ; returns a 0 based array of controlIDs
    Return $aid_Temp
EndFunc   ;==>CreateTabGUIs

Func Draw_GUI()
    Local $h_MainGUI = GUICreate('Test Form', 820, 740, -1, -1)
    GUISetBkColor(0x38A7D2)
    GUISetOnEvent($GUI_EVENT_CLOSE, 'CloseProgram')

    GUICtrlCreatePic(@ScriptDir & '\Image.jpg', 690, 15, 120, 120)

    GUICtrlCreateLabel('Welcome: ' & GetFullName(@UserName), 17, 135, 500, 16)
    GUICtrlSetColor(-1, $COLOR_WHITE)
    GUICtrlSetFont(-1, 9, $FW_BOLD)

    #Region #### Main Menu ###########################
    Local $id_Menu = GUICtrlCreateMenu('File')
    GUICtrlCreateMenuItem('Close', $id_Menu)
    GUICtrlSetOnEvent(-1, 'CloseProgram')
    GUICtrlCreateMenuItem('Export', $id_Menu)
    GUICtrlSetOnEvent(-1, 'MainMenu_FileExport')

    $id_Menu = GUICtrlCreateMenu('Edit')
    GUICtrlSetState(-1, $GUI_DISABLE)

    $id_Menu = GUICtrlCreateMenu('Option')
    GUICtrlSetState(-1, $GUI_DISABLE)

    $id_Menu = GUICtrlCreateMenu('View')
    GUICtrlCreateMenuItem('Exit', $id_Menu)
    GUICtrlSetState(-1, $GUI_DISABLE)
    GUICtrlSetOnEvent(-1, 'MainMenu_ViewExit')

    $id_Menu = GUICtrlCreateMenu('Help')
    GUICtrlCreateMenuItem('About', $id_Menu)
    GUICtrlSetOnEvent(-1, 'MainMenu_HelpAbout')
    #EndRegion #### Main Menu ###########################

    #Region #### Employee Info #######################
    GUICtrlCreateGroup('Employee Information', 16, 8, 281, 121)

    GUICtrlCreateLabel('Emp#:', 33, 35, 35, 17)
    $g_aidControls[$EMPNUMBER_INP] = GUICtrlCreateInput('1234567', 72, 32, 217, 21)
    GUICtrlSetState(-1, $GUI_DISABLE)

    GUICtrlCreateLabel('Name:', 33, 59, 35, 17)
    $g_aidControls[$EMPNAME_INP] = GUICtrlCreateInput(GetFullName(@UserName), 72, 56, 217, 21)
    GUICtrlSetState(-1, $GUI_DISABLE)

    GUICtrlCreateLabel('Process:', 24, 83, 45, 17)
    $g_aidControls[$EMPPROCESS_INP] = GUICtrlCreateInput('First Process', 72, 80, 217, 21)
    GUICtrlSetState(-1, $GUI_DISABLE)

    GUICtrlCreateGroup('', -99, -99, 1, 1)
    #EndRegion #### Employee Info #######################

    #Region #### whatever this is ####################
    GUICtrlCreateLabel('AID:', 25, 170, 25, 17)
    GUICtrlSetFont(-1, 10, $FW_BOLD)

    $g_aidControls[$AID_LBL] = GUICtrlCreateLabel('ABZ123456', 60, 170, 90, 17) ; remove this if you don't alter the text
    GUICtrlSetColor(-1, $COLOR_WHITE)
    GUICtrlSetFont(-1, 10, $FW_BOLD)
    #EndRegion #### whatever this is ####################

    #Region #### Confirmation Checkboxes #############
    $g_aidControls[$AGREE_CHK] = GUICtrlCreateCheckbox('  I agree that I have checked the entry(s) above.', 25, 655, 265, 17)
    GUICtrlSetOnEvent(-1, 'SubmitBtn_SetState')

    $g_aidControls[$ACCEPT_CHK] = GUICtrlCreateCheckbox('  Accept', 25, 675, 60, 17)
    GUICtrlSetOnEvent(-1, 'AcceptChk_Clicked')

    GUICtrlCreateLabel('*', 85, 680, 40, 17)
    GUICtrlSetColor(-1, $COLOR_RED)
    GUICtrlSetFont(-1, 10, $FW_BOLD)


    $g_aidControls[$REJECT_CHK] = GUICtrlCreateCheckbox('  Reject', 25, 695, 60, 17)
    GUICtrlSetOnEvent(-1, 'RejectChk_Clicked')

    GUICtrlCreateLabel('*', 85, 695, 40, 17)
    GUICtrlSetColor(-1, $COLOR_RED)
    GUICtrlSetFont(-1, 10, $FW_BOLD)
    #EndRegion #### Confirmation Checkboxes #############

    #Region #### Submit Report Group #################
    $Group1 = GUICtrlCreateGroup("Submit Report", 600, 647, 200, 65)

    $g_aidControls[$LAYOUTONE_RDO] = GUICtrlCreateRadio("Layout 1", 615, 665, 70, 17)
    GUICtrlSetState(-1, $GUI_CHECKED)
    GUICtrlSetOnEvent(-1, 'Layout1_Selected')

    GUICtrlCreateRadio("Layout 2", 615, 685, 70, 17)
    GUICtrlSetOnEvent(-1, 'Layout2_Selected')

    $g_aidControls[$SUBMIT_BTN] = GUICtrlCreateButton('Submit Report', 690, 667, 100, 33)
    GUICtrlSetState(-1, $GUI_DISABLE)
    GUICtrlSetOnEvent(-1, 'Report_Submit')

    GUICtrlCreateGroup("", -99, -99, 1, 1)
    #EndRegion #### Submit Report Group #################

    #Region #### Create Tabs #########################
    Draw_Tabs($h_MainGUI)
    #EndRegion #### Create Tabs #########################

    GUISetState(@SW_SHOW, $h_MainGUI) ; show the main gui
    Sleep(80) ; add a sleep to try and get them to show at the same time
    GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI]) ; show the first tab gui
EndFunc   ;==>Draw_GUI

; draw the group on controls that sit above the tab guis
Func Draw_TabHeaders()
    ; this draws the headers over the columns on the tab gui. The values are pulled from the $g_asTabHeaderText array
    GUICtrlCreateGroup('', 30, 215, 755, 50)
    GUICtrlCreateLabel($g_asTabHeaderText[$ENTRYS_COL], 50, 232, 80, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$NA_COL], 238, 232, 40, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$NOERROR_COL], 315, 232, 70, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$WITHERROR_COL], 405, 232, 85, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
    GUICtrlCreateLabel($g_asTabHeaderText[$REMARKS_COL], 590, 232, 90, 20, BitOR($SS_CENTER, $SS_CENTERIMAGE, $WS_BORDER))
EndFunc   ;==>Draw_TabHeaders

Func Draw_Tabs($h_MainGUI)
    ; create the main tab control and add it's ID to the control array
    $g_aidControls[$MAIN_TAB] = GUICtrlCreateTab(20, 190, 780, 455, $WS_EX_MDICHILD)
    GUICtrlSetOnEvent(-1, 'TabChange') ; do summat on tab change

    ; get the strings that will populate the gui controls from the excel sheet. These will be the "Entry{s}"
    ; set the range as the first column 'A', to the last column that contains the layout for the guis (currently 'C')
    ; ### add some error checking for CellRange_GetValues ####
    Local $as_RangeRead = CellRange_GetValues(CellRange_CreateFromInteger($FRONTMATTERGUI + 1, $TABCOUNT))

    ; loop to create the required number of tabs, guis and their controls
    For $i = $FRONTMATTERGUI To $TABCOUNT - 1 ; - 1 because $g_asTabNames is a 0 based array
        GUICtrlCreateTabItem($g_asTabNames[$i])

        ; create the headers for each tab. These are listed in $g_asTabHeaderText
        Draw_TabHeaders()

        Switch $i
            Case $FRONTMATTERGUI ; gui 1
                ; create the array containing the controlIDs. returns a 0 based array of controlIDs
                $aid_FrontMatterGUI = CreateTabGUIs($as_RangeRead, $i, $h_MainGUI)

            Case $BODYMATTERGUI ; gui 2
                ; create the array containing the controlIDs
                $aid_BodyMatterGUI = CreateTabGUIs($as_RangeRead, $i, $h_MainGUI)

            Case $BACKMATTERGUI ; gui 3
                ; create the array containing the controlIDs
                $aid_BackMatterGUI = CreateTabGUIs($as_RangeRead, $i, $h_MainGUI)
        EndSwitch

        ; switch to the main gui and tab after creating the new guis or else the control creation gets bollocksed Up
        ; and the tabs or controls are not visible
        GUISwitch($h_MainGUI, $g_aidControls[$MAIN_TAB])
    Next

    GUICtrlCreateTabItem('')
EndFunc   ;==>Draw_Tabs

Func TabChange()
    Switch GUICtrlRead($g_aidControls[$MAIN_TAB])
        Case $FRONTMATTERGUI ; Front Matter tab
            GUISetState(@SW_SHOW, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI])
        Case $BODYMATTERGUI ; body Matter tab
            GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_SHOW, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BACKMATTERGUI])
        Case $BACKMATTERGUI ; back matter tab
            GUISetState(@SW_HIDE, $g_aidControls[$FRONTMATTERGUI])
            GUISetState(@SW_HIDE, $g_aidControls[$BODYMATTERGUI])
            GUISetState(@SW_SHOW, $g_aidControls[$BACKMATTERGUI])
    EndSwitch
EndFunc   ;==>TabChange
#EndRegion #### GUI Actions #############################

#Region #### Main Menu Action ########################
Func MainMenu_FileExport()
    ; add commands later
EndFunc   ;==>MainMenu_FileExport

Func MainMenu_HelpAbout()
    ; add commands later
EndFunc   ;==>MainMenu_HelpAbout

Func MainMenu_ViewExit()
    ; add commands later
EndFunc   ;==>MainMenu_ViewExit
#EndRegion #### Main Menu Action ########################

#Region #### Spreadsheet Actions #####################
; create an array of values that are read from the controls on the tabs GUI
; these will be written to the spreadsheet later
Func ArrayRow_SetValues(ByRef $aid_ControlID, $i_Row)
    Local $v_Value = ''
    Local $s_BoldCells = ''

    ; loop through the array columns
    For $i = $ENTRYS_COL To $REMARKS_COL
        ; get the value from the supplied array
        $v_Value = $aid_ControlID[$i_Row][$i]

        ; check if the value is a controlID
        If IsNumber($v_Value) Then
            If Not $v_Value Then ; it's 0 so no control to read
                $v_Value = '' ; set a blank string
            Else
                $v_Value = GUICtrlRead($aid_ControlID[$i_Row][$i])

                ; assume it's a header or heading indentifier string
                If StringIsUpper(StringRegExpReplace($v_Value, '[^A-Za-z]', '')) Then $g_sSectionCells &= 'A' & $i_Row + 1 & ','
                If StringInStr($v_Value, $g_asTabHeaderText[0]) Then $g_sHeaderCells &= 'A' & $i_Row + 1 & ','
            EndIf
        Else ; assume it's a tab name
            $aid_ControlID[$i_Row][$i] = $v_Value

            If StringInStr($v_Value, $g_asTabHeaderText[0]) Then $g_sHeaderCells &= 'A' & $i_Row + 1 & ','
            If StringInStr(_ArrayToString($g_asTabNames, '|'), $v_Value) Then $g_sTabNameCells &= 'A' & $i_Row + 1 & ','

            ExitLoop
        EndIf

        ; do different things based on the specified control
        If $i = $ENTRYS_COL Or $i = $REMARKS_COL Then ; these are input controls
            ; write the value to the array
            $aid_ControlID[$i_Row][$i] = $v_Value
        Else ; these are checkbox controls
            ; get the checked state of the control
            $aid_ControlID[$i_Row][$i] = CheckBox_ReturnStringState($aid_ControlID[$i_Row][$i])
        EndIf
    Next
EndFunc   ;==>ArrayRow_SetValues

; create an excel range from 2 numbers
Func CellRange_CreateFromInteger($i_Start, $i_End = 0)
    ; assumes there is only one number to convert to a range
    if not $i_End Then Return  _Excel_ColumnToLetter($i_Start)

    ; convert any numbers to excel column letters
    Return _Excel_ColumnToLetter($i_Start) & ':' & _Excel_ColumnToLetter($i_End)
EndFunc   ;==>CellRange_CreateFromInteger

Func CellRange_CreateFromString($s_Range, $v_AddRange)
    ; if $v_AddRange is a number then convert the number to a letter for the range
    If IsNumber($v_AddRange) Then $v_AddRange = _Excel_ColumnToLetter($v_AddRange)

    ; trim any leading\trailing comma symbols before splitting
    $s_Range = StringRegExpReplace($s_Range, "\A[,]+|[,]+\Z", "")

    ; split the comma delimited range
    Local $as_Split = StringSplit($s_Range, ',')

    ; loop through the array
    For $i = 1 To $as_Split[0]
        ; update the array element
        $as_Split[$i] = $as_Split[$i] & ':' & StringRegExpReplace($as_Split[$i], '[[:alpha:]]+', $v_AddRange)
    Next

    ; return a string that will be used as a cell range
    Return _ArrayToString($as_Split, ',', 1)
EndFunc   ;==>CellRange_CreateFromString

Func CellRange_GetValues($s_Range = 'A')
    ; set the path to the file that has the values for the inputs
    Local $s_ExcelFile = @ScriptDir & '\Sample.xlsx'

    ; #### add some error checking for these excel functions ####
    Local $o_Excel = _Excel_Open(False)
    Local $o_WorkBook = _Excel_BookOpen($o_Excel, $s_ExcelFile)
    Local $as_RangeRead = _Excel_RangeRead($o_WorkBook, Default, $o_WorkBook.ActiveSheet.Usedrange.Columns($s_Range), 2)

    _Excel_Close($o_Excel)

;~ _ArrayDisplay($as_RangeRead, 'CellRange_GetValues') ; for debugging

    Return $as_RangeRead
EndFunc   ;==>CellRange_GetValues

Func CellRange_ResetGlobalValues()
    $g_sHeaderCells = ''
    $g_sSectionCells = ''
    $g_sTabNameCells = ''
EndFunc   ;==>CellRange_ResetGlobalValues

Func GetEmployeeInfo()
    Local $as_Employee[6] = [ _ ; 0 based array
    'Emp#   : ' & GUICtrlRead($g_aidControls[$EMPNUMBER_INP]), _
    'Name   : ' & GUICtrlRead($g_aidControls[$EMPNAME_INP]), _
    'Process: ' & GUICtrlRead($g_aidControls[$EMPPROCESS_INP]), _
    'AID    : ' & GUICtrlRead($g_aidControls[$AID_LBL]), _
    'I agree that I have checked the entry(s) above.', _
    'Accepted']

    ; update the last array element if the reject checkbox is checked
    if GUICtrlRead($g_aidControls[$REJECT_CHK]) = $GUI_CHECKED Then $as_Employee[5] = 'Rejected'

    ; return thte array for writing to the spreadsheet
    Return $as_Employee
EndFunc

; go through the tabs and controls and write the values to a spreadsheet
Func WriteArrayValuesToSpreadsheet($o_WorkBook, $i_Layout = Report_GetLayout())
    Local _ ; $aa_ControlArrays is an array of arrays. The arrays have the controlIDs for the relevant tab gui controls
            $aa_ControlArrays[$TABCOUNT + 1] = [$TABCOUNT, $aid_FrontMatterGUI, $aid_BodyMatterGUI, $aid_BackMatterGUI], _
            $as_Values = 0, _ ; the array that will hold the values of the gui controls
            $as_Temp = 0 ;_     a temp array for modding

    ; set the number of loop to perform when writing.
    ; If layout 1 is used, there will be a seperate spreadsheet for each tab so 3 loops
    ; if layout 2 is used all the tabs are written to one spreadsheet so only 1 loop is needed
    Local _
            $i_Total = $aa_ControlArrays[0], _ ;_   set as default layout 1 and loop through the all the arrays
            $i_Concatenated = 0 ;_                  Set the state of the array being processed 0 = multiple 1 = 1 concatenated array

    ; Change if the layout is 2 and just loop through the concatenated array
    If $i_Layout > 1 Then $i_Total = 1 ; layout 2 selected

    #Region #### only needed for cell outside borders if not interested the code can be deleted ###
    Local _ ; Excel constants not in ExcelConstants.au3
            $i_xlAutomatic = -4105, _
            $i_xlContinuous = 1, _
            $i_xlThin = 2, _
            $i_xlEdgeLeft = 7, _
            $i_xlEdgeTop = 8, _
            $i_xlEdgeBottom = 9, _
            $i_xlEdgeRight = 10
    #EndRegion #### only needed for cell outside borders if not interested the code can be deleted ###

    ; loop through the arrays
    For $i = 1 To $i_Total
        ; on each loop copy the nested array to a temp one, this will be filled with the values of the controls
        $as_Values = $aa_ControlArrays[$i] ; set a starting array
        _ArrayInsert($as_Values, 0, _ArrayToString($g_asTabHeaderText, '|')) ; add the headers to each array

        #Region #### This determines which array(s) to use for the write ####
        If $i_Layout = 2 Then
            _ArrayInsert($as_Values, 0, $g_asTabNames[0]) ; add the first tab name to the array

            ; loop through the remaining arrays
            For $j = 2 To $TABCOUNT ; $TABCOUNT = 3
                $as_Temp = $aa_ControlArrays[$j] ; copy to a temp array so we can add the tab names and headet text
                _ArrayInsert($as_Temp, 0, $g_asTabNames[$j - 1]) ; add the next tab name to the array
                _ArrayInsert($as_Temp, 1, _ArrayToString($g_asTabHeaderText, '|')) ; add the next header text

                ; joining them $as_Values
                _ArrayConcatenate($as_Values, $as_Temp)
            Next
        EndIf
        #EndRegion #### This determines which array(s) to use for the write ####

        ; loop through the temp array
        For $j = 0 To UBound($as_Values) - 1
            ; update the array row with the control values
            ArrayRow_SetValues($as_Values, $j)
        Next

        ; activate the sheet
        $o_WorkBook.Sheets($i).Activate

        ; write the array to an excel spreadsheet
        _Excel_RangeWrite($o_WorkBook, $i, $as_Values)
        If @error Then Return SetError(@error, @extended, 'Error Writing ' & $g_asTabNames[$i - 1] & ' to sheet')

        ; write the employee info
        If $i = 1 then  _Excel_RangeWrite($o_WorkBook, $i, GetEmployeeInfo(), CellRange_CreateFromInteger($ARRAYMAX_COLS + 2) & '1')
        If @error Then Return SetError(@error, @extended, 'Error Writing Employee info to sheet')

        ; adjust the sheet properties whilst it's active
        With $o_WorkBook.ActiveSheet
            If $g_iMultipleSheets Then ; we are writing multiple sheets
                ;   set the sheet name
                .Name = $g_asTabNames[$i - 1]
            EndIf

            ; set the column letters that will auto size to fit the text
            .Columns(CellRange_CreateFromInteger($ENTRYS_COL + 1, $ARRAYMAX_COLS + 2)).AutoFit

            ; this is where the cells that have the tab names in get nodified
            #Region #### Tab Name Cells ####
            If $g_sTabNameCells Then
                $g_sTabNameCells = CellRange_CreateFromString($g_sTabNameCells, $ARRAYMAX_COLS)

                ; set the font weight as bold
                .Range($g_sTabNameCells).Font.FontStyle = 'Bold'

                ; set the tab name cells back colour
                .Range($g_sTabNameCells).Interior.Color = 0xBFBFBF

                ; merge the cells
                .Range($g_sTabNameCells).Merge

                ; centre align the cells
                .Range($g_sTabNameCells).HorizontalAlignment = $xlCenter
            EndIf
            #EndRegion #### Tab Name Cells ####

            ; this is where the cells that have Entry(s), N/A, No Error etc get modified
            #Region #### Header Cells ####
            If $g_sHeaderCells Then
                $g_sHeaderCells = CellRange_CreateFromString($g_sHeaderCells, $ARRAYMAX_COLS)

                ; set the font weight as bold
                .Range($g_sHeaderCells).Font.FontStyle = 'Bold'

                ; centre align the cells
                .Range($g_sHeaderCells).HorizontalAlignment = $xlCenter

                ; set the header cells back colour
                .Range($g_sHeaderCells).Interior.Color = 0xFFE6CC
            EndIf
            #EndRegion #### Header Cells ####

            ; this is where the cells that have the section text like CATEGORY, HISTORY DATES etc get modified
            #Region #### Section Cells ####
            If $g_sSectionCells Then
                $g_sSectionCells = CellRange_CreateFromString($g_sSectionCells, $ARRAYMAX_COLS)

                ; set the font weight as bold
                .Range($g_sSectionCells).Font.FontStyle = 'Bold'

                ; set the header cells back colour
                .Range($g_sSectionCells).Interior.Color = 0xDAEFE2

                ; add outside borders
                .Range($g_sSectionCells).Borders($i_xlEdgeTop).LineStyle = $i_xlContinuous
                .Range($g_sSectionCells).Borders($i_xlEdgeLeft).LineStyle = $i_xlContinuous
                .Range($g_sSectionCells).Borders($i_xlEdgeRight).LineStyle = $i_xlContinuous
                .Range($g_sSectionCells).Borders($i_xlEdgeBottom).LineStyle = $i_xlContinuous
            EndIf
            #EndRegion #### Section Cells ####
        EndWith

        CellRange_ResetGlobalValues()
        Sleep(20)
    Next

    $o_WorkBook.Sheets(1).Activate ; activate the first sheet
    CellRange_ResetGlobalValues()
EndFunc   ;==>WriteArrayValuesToSpreadsheet
#EndRegion #### Spreadsheet Actions #####################

#Region #### Submit Report Actions ###################
Func Layout1_Selected()
    $g_iMultipleSheets = 1
EndFunc   ;==>Layout1_Selected

Func Layout2_Selected()
    $g_iMultipleSheets = 0
EndFunc   ;==>Layout2_Selected

; gets the currently selected layout type for the report
Func Report_GetLayout()
    ; return the state of the layout 1 radio button
    If GUICtrlRead($g_aidControls[$LAYOUTONE_RDO]) = $GUI_CHECKED Then Return 1
    Return 2
EndFunc   ;==>Report_GetLayout

; submit your report
Func Report_Submit()
    SplashTextOn("submit", "Submitting report...", 300, 50, -1, -1, $DLG_NOTITLE + $DLG_TEXTVCENTER, '', '', $FW_BOLD)
;~  Local $o_WorkBook = ''

    ; open an excel instance
    Local $o_Excel = _Excel_Open(False)
    If @error Then Return SetError(1, _
            MsgBox($MB_SYSTEMMODAL, _
            '_Excel_Open', _
            'Error creating the Excel application object' & @CRLF & '@error = ' & @error & ', @extended = ' & @extended), 0)

    ; set the number of sheets to add when opening a new workbook
    Local $iSheets = $TABCOUNT
    If Not $g_iMultipleSheets Then $iSheets = 1

    ; open a new excel book
    Local $o_WorkBook = _Excel_BookNew($o_Excel, $iSheets) ; $TABCOUNT is the number of tabs in your program and so the number of sheets we create for submitting
    If @error Then Return SetError(2, _
            MsgBox($MB_SYSTEMMODAL, _
            '_Excel_BookNew', _
            'Error creating new workbook' & @CRLF & '@error = ' & @error & ', @extended = ' & @extended), _Excel_Close($o_Excel))

;~  ; this will read the control values to an array and write the array to an excel file
    WriteArrayValuesToSpreadsheet($o_WorkBook)

    If Not @error Then
        ; create a file path based on time. If you want to keep overwriting the files the create a definite file name
        ; and use the commented line
        Local $s_FilePath = @ScriptDir & '\Report_' & @YEAR & '-' & @MON & '-' & @MDAY & '_' & @HOUR & @MIN & @SEC & '.xlsx'

        ; ; save the created report and overwrite the previous
;~      _Excel_BookSaveAs($o_WorkBook, $s_FilePath, $xlWorkbookDefault, True)

        ; save the created report to a new file
        _Excel_BookSaveAs($o_WorkBook, $s_FilePath, $xlWorkbookDefault)

        If @error Then MsgBox($MB_SYSTEMMODAL, _
                "_Excel_BookSaveAs", _
                "Error saving workbook to '" & $s_FilePath & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    EndIf

    _Excel_Close($o_Excel)

    ; update the splash text
    ControlSetText("submit", "", "Static1", 'Opening report...')

    ; open the report, remove if you don't need to
    ShellExecute($s_FilePath)

    SplashOff()
EndFunc   ;==>Report_Submit

; enables or disables the submit report button
Func SubmitBtn_SetState()
    If BitAND( _ ; check if two checkxoes are checked
            GUICtrlRead($g_aidControls[$AGREE_CHK]), _ ; if the agree checkbox
            GUICtrlRead($g_aidControls[$ACCEPT_CHK]), _ ; and the accept checkbox
            $GUI_CHECKED) = $GUI_CHECKED _ ; are checked
            Or _ ; they may not both be checked so check the reject checkbox
            BitAND( _
            GUICtrlRead($g_aidControls[$AGREE_CHK]), _ ; if the agree checkbox
            GUICtrlRead($g_aidControls[$REJECT_CHK]), _ ; and the reject checkbox
            $GUI_CHECKED) = $GUI_CHECKED _ ; are checked
            Then ; enable the button
        GUICtrlSetState($g_aidControls[$SUBMIT_BTN], $GUI_ENABLE)
    Else ; disable the button
        GUICtrlSetState($g_aidControls[$SUBMIT_BTN], $GUI_DISABLE)
    EndIf
EndFunc   ;==>SubmitBtn_SetState
#EndRegion #### Submit Report Actions ###################

KickStarter15 · January 9, 2018

15 hours ago, benners said:

Well that was easier than I thought. How's this?.

Well, How's this? Hahaha, this is amazing, amazing, AMAZING... I need to read you code for me to adopt and learn something in it. WOW!!! :sweating: That was fast...

15 hours ago, benners said:

You will still need to go and add error checking though. I'll leave that bit up to you

Okay, I think I can manage this. Thank you so much benners!

benners · January 9, 2018

No worries. Post if you need any help.

KickStarter15 · January 9, 2018

@benners,

One more thing, sorry... Is this possible to apply GUICtrlSetTip() in every entry of my GUI. See attached sample.

I tried having it like below, but all entries has the same GUICtrlSetTip(). If possible, I need to have each entry to have individual instruction when mouse is pointing at the entry. :sweating:

Else ; assume it's a sub
            ; draw the input
            $aid_Temp[$i][$ENTRYS_COL] = GUICtrlCreateInput($s_Value, $i_Left - 50, $i_Top, 155, 20, BitOR($GUI_SS_DEFAULT_INPUT, $ES_READONLY), $WS_EX_TRANSPARENT) ; use an input
            GUICtrlSetBkColor(-1, $COLOR_WHITE)
            GUICtrlSetTip(-1, "Instruction: Do not change any casing for this entry."); SetTip here but all entries has the same
;~          $aid_Temp[$i][0] = GUICtrlCreateLabel($s_Value, $i_Left - 50, $i_Top, 155, 40) ; use a label if you want

            ; draw the N/A check box
            $aid_Temp[$i][$NA_COL] = GUICtrlCreateCheckbox('', $i_Left + 165, $i_Top, 15, 20)
            GUICtrlSetOnEvent(-1, 'NaChk_SetState')

            ; draw the no error check box
            $aid_Temp[$i][$NOERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 260, $i_Top, 15, 20)
            GUICtrlSetOnEvent(-1, 'NoErrorChk_SetState')

            ; draw the with error check box
            $aid_Temp[$i][$WITHERROR_COL] = GUICtrlCreateCheckbox('', $i_Left + 355, $i_Top, 15, 20)
            GUICtrlSetOnEvent(-1, 'WithErrorChk_SetState')

            ; draw the edit control
            $aid_Temp[$i][$REMARKS_COL] = GUICtrlCreateEdit('', $i_Left + 440, $i_Top, 240, 24, BitOR($ES_AUTOVSCROLL, $ES_WANTRETURN))
        EndIf

If not, no worries, this is already too much to ask. Hehehe!

Extract input to an Excel file

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members