lorenkinzel Posted April 19, 2015 Share 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 Link to comment Share on other sites More sharing options...
water Posted April 19, 2015 Share 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki  Link to comment Share on other sites More sharing options...
lorenkinzel Posted April 19, 2015 Author Share 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now