How to use onEvent

Hints, tips and tricks for newbies

Moderators: Dorian (MJT support), JRL

Post Reply
nodochau
Pro Scripter
Posts: 131
Joined: Wed Jan 16, 2019 12:59 pm

How to use onEvent

Post by nodochau » Thu Jan 11, 2024 2:37 pm

Hello ALl,
I am try to create a script that monitor a cell in the spreadsheet. When the operator enter a bad value then a warning popup window will occur. When excel is opened and I enter the value into that cell, nothing happen if the value is out of Specification.
But the code does not work. I appreciate your help
Here is my code:

Code: Select all

XLOpen>C:\Users\Documents\Template\149405.xlsx,1,xl
Wait>3
Let>row=15
Let>col=6
XLGetCell>xl,ProcessSheet,row,4,upper_val
XLGetCell>xl,ProcessSheet,row,5,lower_val
Let>warning=FALSE

//**BREAKPOINT**

OnEvent>CUSTOM,CheckCells,warning,PopupWindow

Label>start
SRT>CheckCells
    XLGetCell>xl,ProcessSheet,row,col,val
    If>val<>
      If>{(%val%>%upper_val%)OR(%val%<%lower_val%)}
        Let>warning=TRUE
      Endif
    Endif
END>CheckCells
Goto>start

SRT>PopupWindow
  MDL>Value is out of Spec
  Let>warning=FALSE
END>PopupWindow

Jiyahana
Newbie
Posts: 10
Joined: Thu Jan 18, 2024 10:03 am
Location: India
Contact:

Re: How to use onEvent

Post by Jiyahana » Thu Jan 18, 2024 10:10 am

Hey try this code.

Code: Select all

XLOpen>C:\Users\Documents\Template\149405.xlsx,1,xl
Wait>3
Let>row=15
Let>col=6
XLGetCell>xl,ProcessSheet,row,4,upper_val
XLGetCell>xl,ProcessSheet,row,5,lower_val
Let>warning=FALSE

//**BREAKPOINT**

OnEvent>CUSTOM,"CheckCells",,warning,"PopupWindow"

Label>start
SRT>CheckCells
    XLGetCell>xl,ProcessSheet,row,col,val
    If>val<>""
      If>{(%val%>%upper_val%) OR (%val%<%lower_val%)}
        Let>warning=TRUE
        RaiseEvent>CUSTOM,"CheckCells"
      Endif
    Endif
END>CheckCells
Goto>start

SRT>PopupWindow
  MDL>Value is out of Spec
  Let>warning=FALSE
END>PopupWindow

nodochau
Pro Scripter
Posts: 131
Joined: Wed Jan 16, 2019 12:59 pm

Re: How to use onEvent

Post by nodochau » Thu Jan 18, 2024 12:40 pm

Thanks a lot for your response. I have some questions.
Why do you use the double quotes for subroutine?
And codes don't work.
I added some delay time and it worked but sometime it did not work I don't know. Below is my revised code:

Code: Select all

XLOpen>C:\Users\Documents\149405.xlsx,1,xl
Wait>3
Let>row=15
Let>col=6
XLGetCell>xl,ProcessSheet,row,4,upper_val
XLGetCell>xl,ProcessSheet,row,5,lower_val

OnEvent>CUSTOM,CheckCells,warning,PopupWindow

Label>start
SRT>CheckCells
    Wait>1
    SetFocus>149405 - Excel
    Wait>2
    XLGetCell>xl,ProcessSheet,row,col,val
    If>val<>
      If>{(%val%>%upper_val%)OR(%val%<%lower_val%)}
        Let>warning=TRUE
      Endif
    Endif
END>CheckCells

Goto>start

SRT>PopupWindow

  MDL>Value %val% at row: %row% and column %col% %crlf% is out of Spec
  Let>empty_cell=
  XLSetCell>xl,ProcessSheet,row,col,empty_cell,
  Let>warning=FALSE
  
END>PopupWindow

Jiyahana
Newbie
Posts: 10
Joined: Thu Jan 18, 2024 10:03 am
Location: India
Contact:

Re: How to use onEvent

Post by Jiyahana » Tue Feb 06, 2024 5:23 am

I think I forget to add double quote but you added and you solved this.

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts