Automated message

[Guest]

Hi

I've created a bat file to be send to a certain user. I used VB to send the bat file, but I want the message to be send automatically when their are any changes made to the Excel file. I've tried to use an IF function, but didn't get right.

Function Message()
Range("A4").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A3").Select
Dim RetVal
RetVal = Shell("H:\Commands\ADR.bat", 1) ' Run bat
End Function

Thanks
Zander

 

[Jim Rech]

Why are you housing this code in a function? Are you trying to call it from
a cell in a formula? Although the Shell will run that way you cannot select
cells and presumably run queries from a function triggered by a worksheet
formula.

A VB function, called from a worksheet formula, cannot take actions in the
worksheet like print, copy cells, select cells, etc. It can only return a
result to the cell it is in.

--
Jim Rech
Excel MVP
| Hi
|
| I've created a bat file to be send to a certain user. I used VB to send
the bat file, but I want the message to be send automatically when their are
any changes made to the Excel file. I've tried to use an IF function, but
didn't get right.
|
| Function Message()
| Range("A4").Select
| Selection.QueryTable.Refresh BackgroundQuery:=False
| Range("A3").Select
| Dim RetVal
| RetVal = Shell("H:\Commands\ADR.bat", 1) ' Run bat
| End Function
|
| Thanks
| Zander
|
|