#1 · Posted April 19, 2015 I am doing a GUI to work with the shape objects in excel. Sort of a poor-mans' autocad. Where I am running into trouble is avoiding errors if no object is selected. The intent being: if an object is selected, do this. Else, do the other. Function of focus is setZero(), line 306. Any help would be appreciated. expandcollapse popup;OPEN THE EXCEL BOOK FIRST - AS IN BEFORE YOU OPEN THE SCRIPT #cs TO DO: At setZero(): if no object is selected, read the inputs? Something like that. Set all to use lower-left corner of selection rather than upper-left(subtract height). Set all to use positive as upward rather than down (multiple of -1) Secondary pop-up gui with controls for: get size, get location, group selection(s), group all, un-group all. Get your S- - T together with the multiplier-thing. *GET SOME FRIGGIN' ERROR PREVENTION IN THERE others may not understand the crash if no object is selected.* *last on list as not all Funcs are known yet =============================================================== IN PROGRESS: funcs that use fixed/relative: setLocation() insert() =============================================================== Funcs that NEED fixed/relative: setLocation() insert() getLocation();===========not in use =============================================================== Funcs that use lower-left rather than upper-left: none yet =============================================================== Funcs that use positive as upward rather than down: none yet #ce #include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <WinAPI.au3> Opt("GUIOnEventMode", 1) Opt("WinTitleMatchMode", 2) Global $relativeOrFixedChoice = 0;0 for relative, 1 for fixed Global $zeroX_Coordinate, $zeroY_Coordinate;direct-read, does not have multiplier $guiWidth = @DesktopWidth - 900 $guiHeight = (@DesktopHeight * .955) - 105 $hGUI = GUICreate("", 897, 78, $guiWidth, $guiHeight, $WS_POPUP, $WS_EX_TOOLWINDOW) GUISetOnEvent($GUI_EVENT_PRIMARYDOWN, "move_it") GUISetBkColor(0xABCDEF);(0x00ff00)while messing with gui. Else, (0xABCDEF) GUISetStyle($WS_POPUP, $WS_EX_LAYERED, $hGUI) _WinAPI_SetLayeredWindowAttributes($hGUI, 0xABCDEF, 255);ask someone who understands it. I copied it. WinSetOnTop($hGUI, "", 1);keeps you visible while touching the Excel sheet ;=======================================================offset copy controls $offsetX_Input = GUICtrlCreateInput("", 530, 8, 50, 21) $offsetY_Input = GUICtrlCreateInput("", 530, 32, 50, 21) $offsetX_Label = GUICtrlCreateLabel("X", 515, 8, 11, 17) GUICtrlSetColor(-1, 0xff00ff) $offsetY_Label = GUICtrlCreateLabel("Y", 515, 32, 11, 17) GUICtrlSetColor(-1, 0xff00ff) $offsetCopyButton = GUICtrlCreateButton("offset copy", 458, 8, 49, 41, $BS_MULTILINE) GUICtrlSetOnEvent($offsetCopyButton, "offsetCopy") ;=======================================================insert drawing object controls $insertW_Input = GUICtrlCreateInput("1.5", 368, 7, 50, 16) $insertH_Input = GUICtrlCreateInput("96", 368, 25, 50, 16) $insertWidthLabel = GUICtrlCreateLabel("W", 352, 7, 15, 16) GUICtrlSetColor(-1, 0xff00ff) $insertHeightLabel = GUICtrlCreateLabel("H", 352, 25, 12, 16) GUICtrlSetColor(-1, 0xff00ff) $insertX_Input = GUICtrlCreateInput("0", 368, 43, 50, 16) $insertY_Input = GUICtrlCreateInput("0", 368, 61, 50, 16) $insertX_Label = GUICtrlCreateLabel("X", 352, 43, 15, 16) GUICtrlSetColor(-1, 0xff00ff) $insertY_Label = GUICtrlCreateLabel("Y", 352, 61, 15, 16) GUICtrlSetColor(-1, 0xff00ff) $insertButton = GUICtrlCreateButton("insert", 296, 8, 49, 41) GUICtrlSetOnEvent($insertButton, "insert") ;=======================================================move. relocate selection controls $setX_LocationInput = GUICtrlCreateInput("", 208, 8, 49, 21) $setY_LocationInput = GUICtrlCreateInput("", 208, 32, 49, 21) $setX_LocationLabel = GUICtrlCreateLabel("X", 192, 8, 11, 17) GUICtrlSetColor(-1, 0xff00ff) $setY_LocationLabel = GUICtrlCreateLabel("Y", 192, 32, 11, 17) GUICtrlSetColor(-1, 0xff00ff) $setLocationButton = GUICtrlCreateButton("move", 144, 8, 41, 41) GUICtrlSetOnEvent($setLocationButton, "setLocation") ;======================================================= toggle co-ordinate system from 'relative to selected object' to 'from a fixed point' from set-zero section controls. $labelFixedCoOrd = GUICtrlCreateLabel("", 207, 54, 51, 19) GUICtrlSetState($labelFixedCoOrd, $GUI_DISABLE) GUICtrlSetBkColor($labelFixedCoOrd, 0xff0000) $setLocationFixedButton = GUICtrlCreateButton("fixed", 208, 55, 49, 17) GUICtrlSetOnEvent($setLocationFixedButton, "setRelativeOrFixedChoiceToFixed") $labelRelativeCoOrd = GUICtrlCreateLabel("", 143, 54, 51, 19) GUICtrlSetState($labelRelativeCoOrd, $GUI_DISABLE) GUICtrlSetBkColor($labelRelativeCoOrd, 0x00ff00) $setLocationRelativeButton = GUICtrlCreateButton("relative", 144, 55, 49, 17) GUICtrlSetOnEvent($setLocationRelativeButton, "setRelativeOrFixedChoiceToRelative") ;=======================================================set grid $setGridButton = GUICtrlCreateButton("set grid", 78, 8, 41, 41, $BS_MULTILINE) GUICtrlSetOnEvent($setGridButton, "setGrid") ;=======================================================resize object $setWidthInput = GUICtrlCreateInput("", 690, 8, 50, 21) $setHeightInput = GUICtrlCreateInput("", 690, 32, 50, 21) $setWidthLabel = GUICtrlCreateLabel("W", 665, 8, 15, 17) GUICtrlSetColor(-1, 0xff00ff) $setHeightLabel = GUICtrlCreateLabel("H", 665, 32, 11, 17) GUICtrlSetColor(-1, 0xff00ff) $setSizeButton = GUICtrlCreateButton("set size", 620, 8, 41, 41, $BS_MULTILINE) GUICtrlSetOnEvent($setSizeButton, "setSize") ;=======================================================set the zero co-ordinate $setFixedZeroX_Input = GUICtrlCreateInput("48", 836, 8, 50, 21) $setFixedZeroY_Input = GUICtrlCreateInput("240", 836, 32, 50, 21) $setFixedZeroX_Label = GUICtrlCreateLabel("X", 815, 8, 11, 17) GUICtrlSetColor(-1, 0xff00ff) $setFixedZeroY_Label = GUICtrlCreateLabel("Y", 815, 32, 11, 17) GUICtrlSetColor(-1, 0xff00ff) $setFixedZeroButton = GUICtrlCreateButton("set zero", 778, 8, 33, 41, $BS_MULTILINE) GUICtrlSetOnEvent($setFixedZeroButton, "setZero") $xItButton = GUICtrlCreateButton("X", 26, 8, 33, 25);Not good physical placement within GUI.Re-work. GUICtrlSetOnEvent($xItButton, "xIt") GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif") GUICtrlSetColor(-1, 0xFF0000) $oExcel = ObjGet("", "Excel.Application") $wintitle = WinGetTitle(".xlsx") ;=======================================================a solid grab-point for moving the GUI If $wintitle = 0 Then $labelText = "Excel must be open first. Press here to refresh" Else $labelText = " X-LK-cad " & $wintitle EndIf $move_ItLabel = GUICtrlCreateLabel($labelText, 422, 56, 464, 17) GUICtrlSetOnEvent($move_ItLabel, "refreshOexcel") GUICtrlSetBkColor($move_ItLabel, 0x777777) GUICtrlSetColor($move_ItLabel, 0xffffff) ;=======================================================enable/disable tooltips $tipEnableCheckbox = GUICtrlCreateCheckbox("", 28, 40, 20, 20) GUICtrlSetOnEvent($tipEnableCheckbox, "tipOnOff") GUICtrlSetState(-1, $GUI_UNCHECKED) GUICtrlSetTip(-1, "enable button tips") GUISetState(@SW_SHOW) readZero();set default zero X & Y coordinates While 1 Sleep(10) WEnd Func move_it();Grab the gray label to scoot the GUI around. _SendMessage($hGUI, $WM_SYSCOMMAND, 0xF012, 0) EndFunc ;==>move_it Func xIt() Exit EndFunc ;==>xIt Func setGrid();=======================================================sets grid to an emulation of 16" centers horizontal, 12" centers vertical.Gotta love Excels' 2-different-measurement system. With $oExcel .Rows.RowHeight = 19.50 .Columns.ColumnWidth = 4.14 EndWith EndFunc ;==>setGrid Func offsetCopy();=======================================================copy & paste by a specified offset (inches). Multiple clicks/press enter gets multiple copies. Local $xMultiplier = 1.6048 Local $yMultiplier = 1.604995 Local $deltaX = Execute(GUICtrlRead($offsetX_Input)) * $xMultiplier Local $deltaY = Execute(GUICtrlRead($offsetY_Input)) * $yMultiplier With $oExcel $xLocation = .Selection.ShapeRange.Left $yLocation = .Selection.ShapeRange.Top $shapObject = .Selection.ShapeRange .Selection.Copy .ActiveSheet.Paste $x = $deltaX + $xLocation $y = $deltaY + $yLocation Sleep(10) .Selection.Left = $x .Selection.Top = $y EndWith EndFunc ;==>offsetCopy Func insert();=======================================================insert drawing object. Rectangle, 1-point lineweight, black Local $width = Execute(GUICtrlRead($insertW_Input)) * 1.6048 Local $height = Execute(GUICtrlRead($insertH_Input)) * 1.604995 Local $xInsertPoint = Execute(GUICtrlRead($insertX_Input)) * 1.6048 Local $yInsertPoint = Execute(GUICtrlRead($insertY_Input)) * 1.604995 If $relativeOrFixedChoice = 0 Then $x = $xInsertPoint $y = $yInsertPoint EndIf If $relativeOrFixedChoice = 1 Then $x = $xInsertPoint + ($zeroX_Coordinate * 1.6048) $y = $yInsertPoint + ($zeroY_Coordinate * 1.604995) EndIf With $oExcel .ActiveSheet.Shapes.AddShape(1, $x, $y, $width, $height).Select; shape type, X location, Y location, width, height $shapObject = .Selection.ShapeRange $shapObject.Fill.Visible = False $shapObject.Line.Weight = 1 $shapObject.Line.ForeColor.SchemeColor = 0;black ;$shapObject.Placement = .xlPlacement.xlFreeFloating (my) attempts to format as 'don't move or size with cells' has been fruitless EndWith EndFunc ;==>insert Func setLocation();=======================================================essentially, 'move' selected. In excel, negatives move toward the left & top. A pain Local $deltaX = Execute(GUICtrlRead($setX_LocationInput)) * 1.6048;'Execute': Want to be able to enter '48.375+36.125' without doing head-math, focusing on the drawing instead. Local $deltaY = Execute(GUICtrlRead($setY_LocationInput)) * 1.604995 Local $fixedX = GUICtrlRead($setFixedZeroX_Input) * 1.6048 Local $fixedY = GUICtrlRead($setFixedZeroY_Input) * 1.604995 If $relativeOrFixedChoice = 0 Then;move to a position relative to its' current position With $oExcel $xLocation = .Selection.ShapeRange.Left $yLocation = .Selection.ShapeRange.Top $shapObject = .Selection.ShapeRange $x = $deltaX + $xLocation $y = $deltaY + $yLocation Sleep(10) .Selection.Left = $x .Selection.Top = $y EndWith Else; move to location from fixed-zero point With $oExcel $x = $deltaX + $fixedX $y = ($deltaY * -1) + $fixedY Sleep(10) .Selection.Left = $x .Selection.Top = $y EndWith EndIf EndFunc ;==>setLocation #cs Func getSize();================not in use With $oExcel Local $width = .Selection.ShapeRange.Width / 1.6048 Local $height = .Selection.ShapeRange.Height / 1.604995 GUICtrlSetData($getSizeInput, "w= " & $width & " h= " & $height) EndWith EndFunc ;==>getSize Func getLocation();===========not in use With $oExcel $xLocation = .Selection.ShapeRange.Left / 1.6048 $yLocation = .Selection.ShapeRange.Top / 1.604995 EndWith GUICtrlSetData($getLocationInput, "X= " & $xLocation & " Y= " & $yLocation) EndFunc ;==>getLocation Func groupAll();===========not in use With $oExcel .Application.ScreenUpdating = False .ActiveSheet.Shapes.SelectAll .Selection.Group .Application.ScreenUpdating = True EndWith EndFunc Func unGroupAll();===========not in use With $oExcel .Application.ScreenUpdating = False .ActiveSheet.Shapes.SelectAll .Selection.Ungroup .Application.ScreenUpdating = True EndWith EndFunc #ce Func setSize();=======================================================resize selected drawing object Local $widthMultiplier = 1.6048 Local $htMultiplier = 1.604995 Local $width = Execute(GUICtrlRead($setWidthInput)) Local $height = Execute(GUICtrlRead($setHeightInput)) Local $translatedWidth = $width * $widthMultiplier Local $translatedHeight = $height * $htMultiplier With $oExcel $shapObject = .Selection.ShapeRange $shapObject.Width = $translatedWidth $shapObject.Height = $translatedHeight EndWith EndFunc ;==>setSize Func setRelativeOrFixedChoiceToFixed();=================================(fixed) set co-ordinate system to: relative to selected object; or from a fixed point from 'set zero' section. $relativeOrFixedChoice = 1 GUICtrlSetBkColor($labelFixedCoOrd, 0x00ff000);change border-color surrounding the 2 buttons to help with visual I.D. GUICtrlSetBkColor($labelRelativeCoOrd, 0xff0000) EndFunc ;==>setRelativeOrFixedChoiceToFixed Func setRelativeOrFixedChoiceToRelative();=============================(relative) set co-ordinate system to: relative to selected object; or from a fixed point from 'set zero' section. $relativeOrFixedChoice = 0 GUICtrlSetBkColor($labelFixedCoOrd, 0xff0000) GUICtrlSetBkColor($labelRelativeCoOrd, 0x00ff00) EndFunc ;==>setRelativeOrFixedChoiceToRelative Func readZero();====================================================set global variable for zero co-ordinates $zeroX = GUICtrlRead($setFixedZeroX_Input) $zeroY = GUICtrlRead($setFixedZeroY_Input) $zeroX_Coordinate = $zeroX $zeroY_Coordinate = $zeroY EndFunc ;==>readZero Func setZero();====================================================get co-ordinates of selection ;still lacking the all-important lower-left feature With $oExcel;>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>attempting to make an exception for "no object selected". Not working-out well. ;IE: if a shape is selected, do this. Otherwise, do the other. If .TypeName.Object.Selection <> "Range" Then;>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>bad syntax. don't know what proper would be. Tried many methods. .Range, etc...crash $xLocation = .Selection.ShapeRange.Left / 1.6048; works fine without the "if" $yLocation = .Selection.ShapeRange.Top / 1.604995; works fine without the "if" Else Sleep(100) $xLocation = GUICtrlRead($setFixedZeroX_Input) $yLocation = GUICtrlRead($setFixedZeroY_Input) EndIf EndWith $xLocationRounded = Round($xLocation, 3) $yLocationRounded = Round($yLocation, 3) GUICtrlSetData($setFixedZeroX_Input, $xLocationRounded) GUICtrlSetData($setFixedZeroY_Input, $yLocationRounded) readZero() EndFunc ;==>setZero Func tipOnOff();====================================================Allows tips when needed & removes the annoyance when not needed. If GUICtrlRead($tipEnableCheckbox) = $GUI_CHECKED Then tipsOn() ElseIf GUICtrlRead($tipEnableCheckbox) = $GUI_UNCHECKED Then;My very first ElseIf! A simple Else seemed fine.... tipsOff() EndIf EndFunc ;==>tipOnOff Func tipsOn() GUICtrlSetTip($offsetCopyButton, "Copy selection & paste with x, y offset." & @CRLF & " Multiple clicks (or press 'Enter') gets multiple pastes.") GUICtrlSetTip($insertButton, "Insert object. Set height, width & location first") GUICtrlSetTip($setLocationButton, "Move selection to x, y distance from current (relative)" & @CRLF & "Or to specific location (fixed)") GUICtrlSetTip($setGridButton, 'Set grid to an emulation of 16" x, 12" y.') GUICtrlSetTip($setSizeButton, "resize selection") GUICtrlSetTip($setFixedZeroButton, "Set fixed zero co-ordinates using location of selection") GUICtrlSetTip($setLocationRelativeButton, "Co-ordinates for 'move' will come from selections' current location") GUICtrlSetTip($setLocationFixedButton, "Co-ordinates for 'move' will come from the 'set zero' option") GUICtrlSetTip($xItButton, "Close") EndFunc ;==>tipsOn Func tipsOff() GUICtrlSetTip($offsetCopyButton, "") GUICtrlSetTip($insertButton, "") GUICtrlSetTip($setLocationButton, "") GUICtrlSetTip($setGridButton, "") GUICtrlSetTip($setSizeButton, "") GUICtrlSetTip($setFixedZeroButton, "") GUICtrlSetTip($setLocationRelativeButton, "") GUICtrlSetTip($setLocationFixedButton, "") GUICtrlSetTip($xItButton, "") EndFunc ;==>tipsOff Func refreshOexcel();==========================Allows reading Excel book that was started after script. I forget sometimes to stert Excel first. $oExcel = "" $wintitle = "" Sleep(100) $oExcel = ObjGet("", "Excel.Application") $wintitle = WinGetTitle(".xlsx") Sleep(100) If $wintitle <> "" Then $labelText = " X-LK-cad " & $wintitle GUICtrlSetData($move_ItLabel, $labelText) EndIf If $wintitle = "" Then $labelText = "Excel must be open first. Press here to refresh" GUICtrlSetData($move_ItLabel, $labelText) EndIf EndFunc ;==>refreshOexcel Share this post Link to post Share on other sites
#2 · Posted April 19, 2015 Use a COM error handler to catch such errors. Check ObjEvent in the help file. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example ScriptsExcel - Example Scripts - WikiWord - WikiPowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support Tutorials:ADO - Wiki Share this post Link to post Share on other sites
#3 · Posted April 19, 2015 (edited) That did stop the crash. I could not seem to get the _ErrFunc() to run without my Rube Goldberg style workaround. I appear to be missing a point. setZero is the func of interest. Edit: thanks for the reply & help Water. Func readZero();====================================================set global variable for zero co-ordinates $zeroX = GUICtrlRead($setFixedZeroX_Input) $zeroY = GUICtrlRead($setFixedZeroY_Input) $zeroX_Coordinate = $zeroX; a global $zeroY_Coordinate = $zeroY EndFunc ;==>readZero Func setZero();====================================================get co-ordinates of selection ;still lacking the all-important lower-left feature Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc");requires much more study of ObjEvent $tempZero_X = GUICtrlRead($setFixedZeroX_Input); a global $tempZero_y = GUICtrlRead($setFixedZeroY_Input) Sleep(100) With $oExcel $xLocation = .Selection.ShapeRange.Left / 1.6048; works fine without the "if" $yLocation = .Selection.ShapeRange.Top / 1.604995; works fine without the "if" EndWith $xLocationRounded = Round($xLocation, 3) $yLocationRounded = Round($yLocation, 3) GUICtrlSetData($setFixedZeroX_Input, $xLocationRounded) GUICtrlSetData($setFixedZeroY_Input, $yLocationRounded) readZero() If $zeroX_Coordinate = 0 And $zeroY_Coordinate = 0 Then _ErrFunc() EndIf EndFunc ;==>setZero Func _ErrFunc();($oError)$sel $xLocation = $tempZero_X $yLocation = $tempZero_y $xLocationRounded = Round($xLocation, 3) $yLocationRounded = Round($yLocation, 3) GUICtrlSetData($setFixedZeroX_Input, $xLocationRounded) GUICtrlSetData($setFixedZeroY_Input, $yLocationRounded) readZero() EndFunc Edited April 19, 2015 by lorenkinzel Share this post Link to post Share on other sites
