How to call print macro if cell = certain value

[lazycreeks]

In Excel 2000, DDE data updates and causes frequent automatic recalculation. Formulas thus update, filling two ranges with data to drive two charts. When the range for chart 2 is full, Cell "I70" returns the value 1, and when chart 3 range is full, Cell "I134" returns the value 1.
In my worksheet is the following code;
Private Sub Worksheet_Calculate()
Call Print_Control
End Sub
In the Print_Control module, as soon as the first "If..." statement condition is met, the code needs to change the value in Cell "I70", or "I134" to 0 to prevent re-calling the same macro (on recalculation) before it executes one time.(turning off automatic calculation prevents incoming DDE data from being analyzed and processed while its off)
Then the line to actually call the Print_2 or Print_3 macros.
PROBLEM. Most of the time, the error 13 type mismatch trips on the "If...Then" statement line in Print_Control. Nor does
"If Range("I70&quot.Value = 1 Then" work any better.
Using Dim OK2Print As Integer, I can't use the "Set" command to define the cell location of "OK2Print".
QUESTION. I'd like to bypass calling the Print_Control sub by using an "If...Then" statement, or something - anything that calls directly from Private Sub Worksheet_Calculate when cell "I70" or "I134" = 1, but this is beyond me.
The actual print macros work fine, I just can't call them reliably when the worksheet is ready to print 1 or both charts - no matter what I do or where I put it. Since I need this to work unattended, I can't use a worksheet button or other user input. This sometimes works for a short while if I can "reset" the macro while DDE is coming in, but I need to get the code right at compile time.
Sub Print_Control()
Dim OK2Print2 As Object
Dim OK2Print3 As Object
Set OK2Print2 = Worksheets("Sheet1&quot.Range("I70&quot
Set OK2Print3 = Worksheets("Sheet1&quot.Range("I134&quot
If OK2Print2.Value = "1" Then
OK2Print2.FormulaR1C1 = "0"
Call Print_2
Else:
If OK2Print3.Value = "1" Then
OK2Print3.FormulaR1C1 = "0"
Call Print_3
End If
End If
End Sub
I have labored over this for weeks, and can't find where to learn the solution, so thanks in advance for any help here. Kudos to RobBroekhuis especially and others for all that I have learned by reading this and other forums.

 

[xlbo]

Don't define as objects - define as RANGE but you don't really need to do that
Sub Worksheet_Change()
If range("I70&quot.value = 1 then
application.enableevents = false
range("I70&quot.value = 0
application.enableevents = true
Print_2
elseif range("I134&quot.value = 1 then
application.enableevents = false
range("I134&quot.value = 0
application.enableevents = true
Print_3
Else
End if
End sub

This should check for the value of the 2 cells whenever there is a worksheet change and call a macro if either of them = 1

Hope that is what you were looking for

PS To get the value of a cell, if you want to define the cell, you must use Dim OK2Print2 as RANGE

you can then access all range properties
Set OK2Print2 = activesheet.range("I70&quot
mAddress = OK2Print2.address
mRow = OK2Print2.row
mText = OK2Print2.Text
mOtherCell = OK2Print2.OFFSET(0,3).address

and all kindsa lovely goodness like that Rgds
~Geoff~

 

[lazycreeks]

Thanks, Geoff- Its taken me this long to try all the possible combinations. I think dealing with DDE is a whole new ballgame. For one thing, while incoming DDE data drives all my formulae updating, just as it should, there is no actual recalculation event, as there is whenever a user inputs data. Therefore, anything in a Worksheet_Change sub that tests for a condition and calls another macro just sits there watching the condition be met and passed. Manually running the macro works fine, but it won't trigger on its own without prodding from me. Another difference is that I can't write any condition-testing code in Private Sub Worksheet_Calculate() (PSWC for short) without getting the same runtime 13 type mismatch error, so it seems I'm stuck with having to simply call Print_Control from the PSWC, then in the Print_Control sub I can get the condition-testing code to call the actual print routine, which is in, by now, a third-level sub, where, by the way, no condition-testing code works at all - only "do this," "do that" kind of stuff - anything but condition-testing. Tomorrow, I'm moving everything into seperate subs within in the PSWC module, to try and overcome the type mismatch in case the the Private part of PSWC doesn't like calls to modules outside of its own. Who knows? Everything works like a dream until the DDE data starts coming in, then it's, like, hang on to something secure. Oh, and Application.EnableEvents = False prevents any subsequent code from executing, like changing the cell value (whose condition triggers the event) to a neutral value while the rest of the macro executes.
But, thanks to your suggestions and code, I have cleaned up lots of my code to make it run cleaner, and take less time to get to the type mismatch error - just kidding...
But I very much appreciate your help, and I'll let you know if moving everything into the same vertical module (PSWC)solves the type mismatch. Actually, I can massage everything into working if I trick XL in just the right way. Once loaded and running, I get the inevitable type mismatch that stops everything, then if I click on "reset" in whichever sub triggered the error (forget recompiling while DDE is incoming)- just click on "Reset", and away it goes and runs for hours, like a runaway horse - there's no stopping it. So there are prolly some relationships that exist in fractions of a second that XL considers a type mismatch. Sheesch! But DDE is the future of data transfer, and I need to stick it out until I can learn what makes XL purr rather than sneeze and hiccough.
Thanks again

 

[rmikesmith]

lazycreeks,

Here' a suggestion if I understand some of the problem correctly: To prevent your macro code (in Print_Control?) from executing if it is already running, set up this procedure something like:

Sub MyMacro()
Static InUse As Boolean

  If InUse Then Exit Sub
  InUse = True
  
' Existing code to do whatever
  
  InUse = False
End Sub

Regards,
Mike

 

[lazycreeks]

Thanks Mike - I'll put that code to the test, and to Geoff, as promised, virtually all my code sneezes are ironed out. Moving everything into one long vertical module, with Worksheet_Calculate at the top has solved all "error 13 type mismatch" messages, and after 3 1/2 Mo. of 8-14 hour days, it all came together about 2pm today. One caveat to pass along - if one hopes to call a wave file (horn or any audio warning) using Media Player, be advised that Media Player blossoms full-screen, like a brazen hussy demanding to be admired, and can't be closed without suspending Worksheet_Calc. If using the Shell statement, one can follow the path & filename statement with
, vbMinimizeNoFocus
in order to hear the wav but not have WMedia Player obliterate your whole sceen. My code specifies a warning wave to play three times, with a 5 Sec. wait in between.
WMP stays down for the first two instances, but on the 3rd, it just can't resist the impulse to get in your face. Same code - no matter which wav file, nor how many times a wave is called, WMP only stays down for the 1st two. So I have to call the sub three times, with but 1 instance in the sub.
Kind of Mickey Mouse, but it works operating under DDE
Again, Thanks Mike and Geoff, for all your help.
Lazycreeks

 

[rmikesmith]

lazycreeks,

You certainly have a way with words!

Instead of using WMP, you might want to try the following code, which uses a Win32 API call to play a WAV file:

Private Declare Function PlaySound Lib "winmm.dll" _
   Alias "PlaySoundA" (ByVal lpszName As String, _
   ByVal hModule As Long, ByVal dwFlags As Long) As Long
   
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000


Sub PlayWAV()
Dim WAVFile As String

  WAVFile = "C:\Windows\Media\Tada.wav"
  Call PlaySound(WAVFile, 0&, SND_SYNC Or SND_FILENAME)
End Sub

Obviously, you will want to change the WAVFile variable to the particular sound you want played, or perhaps modify the sub to accept the path/filename as a parameter.

The SND_SYNC and SND_ASYNC constants direct whether the sound file should play synchronously (your code waits until the sound finishes playing before proceeding) or asynchronously (code execution continues immediately after the sound is initiated), respectively.

If there is concern that a target pc is not sound capable (unlikely these days) you can check the value of

Application.CanPlaySounds

before calling PlayWAV.


HTH
Mike

 

[lazycreeks]

Mike; Again tnx for the api code. I chose to bludgeon WMP into submission rather than use the api call because my SS is already over 3 megs, and I have to conserve every line of code. I can get the WMP to do it in 1 line;
Shell "c:\program files\windows media player\wmplayer.exe(space)c:\windows\media\horn.wav", vbMinimizedNoFocus
And the nice part is that it doesn't have to be in it's own Sub. So It's in a sort-of "catch all" sub with several other often used tasks. The 1st 2 lines at the top are;
Dim Make_A_Break
Make_A_Break = DoEvents
That allows all the system tasks to proceed while any code line tasks below (in that sub)are executing.
Don't know if you play with DDE data, but that's my focus since July, & writing code for it since Sept., so if you or someone needs to test their DDE-related code, and don't have a source for actual DDE data, I might be able to help.
Best wishes for the holiday to you and Geoff, & Rob -
Lazycreeks