The docs for the AskYesNo function state it will return @YES or @NO but will display Cancel. I was testing the UDF below to allow users to autoformat data downloaded into Excel. The main dialog is a simple dropdown with a Cancel Button, and if pressed exit UDF. However, if a format is selected, a YesNO box is displayed to see if it is acceptable. The intention is that clicking either NO or Cancel will return to the dialog. A CANCEL: handler is set in the UDF and a variable cOn is set to indicate how Cancel in handled in either instance. But, regardless, clicking Cancel from AskYesNo terminates the UDF. AAARRRGGGHHH!!
#DefineSubRoutine xlFmt()
;assumes oWS is Active Worksheet
;Tested with ADO queries into Excel from large Database Sources
;therefore, UsedRange normally accounts for all data
IntControl(73,1,0,0,0)
cFmts="xlRangeAutoFormat3DEffects1,xlRangeAutoFormat3DEffects2,xlRangeAutoFormatAccounting1xlRangeAutoFormatAccounting2,"
cFmts=cFmts:"xlRangeAutoFormatAccounting3,xlRangeAutoFormatAccounting4,xlRangeAutoFormatClassic1,xlRangeAutoFormatClassic2,"
cFmts=cFmts:"xlRangeAutoFormatClassic3,xlRangeAutoFormatClassicPivotTable,xlRangeAutoFormatColor1,xlRangeAutoFormatColor2,"
cFmts=cFmts:"xlRangeAutoFormatColor3,xlRangeAutoFormatList1,xlRangeAutoFormatList2,xlRangeAutoFormatList3,xlRangeAutoFormatLocalFormat1,"
cFmts=cFmts:"xlRangeAutoFormatLocalFormat2,xlRangeAutoFormatLocalFormat3,xlRangeAutoFormatLocalFormat4,xlRangeAutoFormatNone,"
cFmts=cFmts:"xlRangeAutoFormatPTNone,xlRangeAutoFormatReport1,xlRangeAutoFormatReport10,xlRangeAutoFormatReport2,xlRangeAutoFormatReport3,"
cFmts=cFmts:"xlRangeAutoFormatReport4,xlRangeAutoFormatReport5,xlRangeAutoFormatReport6,xlRangeAutoFormatReport7,xlRangeAutoFormatReport8,"
cFmts=cFmts:"xlRangeAutoFormatReport9,xlRangeAutoFormatSimple,xlRangeAutoFormatTable1,xlRangeAutoFormatTable10,xlRangeAutoFormatTable2,"
cFmts=cFmts:"xlRangeAutoFormatTable3,xlRangeAutoFormatTable4,xlRangeAutoFormatTable5,xlRangeAutoFormatTable6,"
cFmts=cFmts:"xlRangeAutoFormatTable7,xlRangeAutoFormatTable8,xlRangeAutoFormatTable9"
nFmts="13,14,4,5,6,17,1,2,3,31,7,8,9,10,11,12,15,16,19,20,-4142,42,21,30,22,23,24,25,26,27,28,29,-4154,32,41,33,34,35,36,37,38,39,40"
cFmt1="xlRangeAutoFormat3DEffects1"
:start
cFmt=StrReplace(cFmts,",",@TAB)
cOn=1
XLFFormat=`WWWDLGED,6.2`
XLFCaption=`Used Range Format`
XLFX=9999
XLFY=9999
XLFWidth=170
XLFHeight=097
XLFNumControls=004
XLFProcedure=`DEFAULT`
XLFFont=`DEFAULT`
XLFTextColor=`DEFAULT`
XLFBackground=`DEFAULT,DEFAULT`
XLFConfig=0
XLF001=`011,059,036,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,10,32,DEFAULT,DEFAULT,"0|255|0"`
XLF002=`113,059,036,012,PUSHBUTTON,"PushButton_Cancel",DEFAULT,"Cancel",0,20,DEFAULT,DEFAULT,DEFAULT,"255|0|0"`
XLF003=`009,011,142,096,DROPLISTBOX,"DropListBox_1",cFmt,"%cFmt1%",DEFAULT,30,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
XLF004=`013,031,134,012,STATICTEXT,"StaticText_1",DEFAULT,"Select Auto Format from Drop Down List",DEFAULT,40,DEFAULT,"Microsoft Sans Serif|6144|70|34",DEFAULT,DEFAULT`
ButtonPushed=Dialog("XLF")
cFmt1=cFmt
nFmt = ItemExtract(ItemLocate(cFmt,cFmts,","),nFmts,",")
oWS.UsedRange.AutoFormat(:: Format=%nFmt%)
cOn=0
If ! AskYesNo("Data Format","Acceptable?")==@YES Then Goto Start
Return(1)
CANCEL:
If !cOn Then Goto Start
If cOn Then Return(0)
:WBERRORHANDLER
Display(2,"Cannot Process","Unable To Set AutoFormat: ":cFmt)
IntControl(73,1,0,0,0)
Goto start
#EndSubRoutine
You need IntControl 72.
Jim
Quote from: JTaylor on September 02, 2013, 08:06:08 AM
You need IntControl 72.
Jim
Thanx. I was under the impression that if the CANCEL: handler was in the UDF IC 72 was not necessary.
By default, if you don't use this
IntControl at all, then every time a Cancel event occurs, WIL does a "
Goto Cancel".
but even with IntControl(72,1,@FALSE,1,0) set same behavior. Same AAARRRGGHH!
Quote from: JTaylor on September 02, 2013, 08:06:08 AM
You need IntControl 72.
Jim
With all due respect, I think this is wrong.
Including a :CANCEL label in a UDF/UDS does, indeed, do the right thing. It always works for me (and I never use IC(72))
There must be something else going on in Stan's use case...
Hi Stan
Try
:CANCEL
If !cOn Then Goto Start
If cOn Then Return(0)You had
QuoteCANCEL:
no label to go to.
:)
My apologies...Guess I didn't read far enough. I just verified that 72 handled the CANCEL event and saw you were using 73.
Jim
Quote from: George Vagenas on September 02, 2013, 03:57:55 PM
Hi Stan
Try
:CANCEL
If !cOn Then Goto Start
If cOn Then Return(0)
You had QuoteCANCEL:
no label to go to.
:)
Big ooops. Surprised the compiler didn't flag it. My wife summed it up for me:
"When you are dead, you don't know it. Only others close to you know it. Same goes for stupid."
Not sure why you would think the compiler would flag that particular error. The compiler doesn't interpret the script it is compiling. It simple strips the white noise, encrypts, compresses and adds the stuff necessary to make a PE file.
Quote from: td on September 03, 2013, 11:35:18 AM
Not sure why you would think the compiler would flag that particular error. The compiler doesn't interpret the script it is compiling. It simple strips the white noise, encrypts, compresses and adds the stuff necessary to make a PE file.
Well, ...
CANCEL:is a syntax error (isn't it?), but of course it will only generate an error message when it is actually hit in the code.
But that's the cost of an interpreted language - which WB is (regardless of whether or not it is "compiled").
Quote from: td on September 03, 2013, 11:35:18 AM
Not sure why you would think the compiler would flag that particular error.
Not so much what I think, but what I saw. Regardless of where the : is located, CANCEL is highlighted in WB studio. If it hadn't been highlighted I would have probably never had to make the original post. I messed up.. no need to rub it in.
Quote
Well, ...
CANCEL:
is a syntax error (isn't it?), but of course it will only generate an error message when it is actually hit in the code.
But that's the cost of an interpreted language - which WB is (regardless of whether or not it is "compiled").
It's only a syntax error when it is interpreted.
if 0
Novel (but not recommend) way to document
your code <grin>
endif
Quote from: stanl on September 03, 2013, 03:04:45 PM
Not so much what I think, but what I saw. Regardless of where the : is located, CANCEL is highlighted in WB studio. If it hadn't been highlighted I would have probably never had to make the original post. I messed up.. no need to rub it in.
Wasn't trying to rub it in. Just a bit surprised given the number of years you have been using WinBatch. You are right about the WBS syntax highlighter. 'Cancel' is a constant as well as a label so WBS likes to give it the 'constant' color even when no at sign is present. It has gotten me a few times as well.
Quote from: td on September 03, 2013, 03:05:40 PM
It's only a syntax error when it is interpreted.
Exactly my situation. The original UDF was written for Office 2003 years ago, and just popped up the dialog. In A rush I hurried in the AskYesNo to give the original UDF a little more variability. CANCEL: was a typo, and the way the UDF was coded, it would never be interpreted and error.