Worksheet_calculate - mutiple conditions, different procedures

[Randy11]

Am having difficulty adding conditions to the worksheet_calculate code I am using. Assistance Appreciated

**This works. When B26 = 250 Message box appears. Click ok & you can continue.
Private Sub Worksheet_calculate()
Dim cellValue As Integer
cellValue = Range("B26").Value
If cellValue = 250 Then
MsgBox ("Cell value is " & cellValue)
End If

End Sub

What I am trying to accomplish:
1) is to have the message box pop up at the following values. 250, 500, 750, 1000, 1250, 1500, 1750 & 2000.
2) When the user clicks the OK button, (if Value is 250)Select Sheet 2 Range A:2:A10 & Print this selection. (if value is 500)Select Sheet 2 Range B2:B10 if value is 500 & Print this selection...... if value 750 select & print... etc etc

 

[SkipVought]

hi,

I despise having to answer a message box with and OK button. Is it ever NOT OK? And if so, what then?

It seems you should be using the Worksheet_Change event...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim quot  As Variant
    
    If Not Intersect(Target, Range("B26")) Is Nothing Then
        With Target
            quot = .Value = 250
            If quot = Int(quot) Then
                With Sheets(Sheet & quot + 1)
                    .PageSetup.PrintArea = Range(Cells(2, quot), Cells(10, quot)).Address
                    .PrintOut
                End With
            End If
        End With
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops that should be...

            quot = .Value / 250

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Thanks Skip I get your message. Message box msg that will actually be used, reminds the operator of a critical function they must perform related to the physical items they are hanlding.

Are you able to show me the 2nd condition added into the code with it's print range. This would be extremely helpful so I can peice togehter the remaining conditions.
Thank you.

 

[SkipVought]

Are you able to show me the 2nd condition added into the code with it's print range. This would be extremely helpful so I can peice togehter the remaining conditions.

Your conditions, as stated, seem to have a mathemetical logic.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim quot  As Variant
    
    If Not Intersect(Target, Range("B26")) Is Nothing Then
        With Target     '[b][highlight]
'250/250 = 1, 500/250 = 2, 750/250 = 3
'print
'Sheet2     , Sheet3     , Sheet4, etc -- which is Sheets(Sheet & quot + 1)
'print range
'A2:A10     , B2:B10, etc -- which is Range(Cells(2, quot), Cells(10, quot)).Address

            quot = .Value / 250    '[/highlight][/b]
            If quot = Int(quot) Then
                With Sheets(Sheet & quot + 1)
                    .PageSetup.PrintArea = Range(Cells(2, quot), Cells(10, quot)).Address
                    .PrintOut
                End With
            End If
        End With
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Am getting an error on this. See below. Also other snag if that my worksheet already has a Worksheet_Change in it, how do I also encopass this? Not sure what I have done wrong?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim quot As Variant

If Not Intersect(Target, Range("B26")) Is Nothing Then
With Target '
250/250 = 1, 500/250 = 2, 750/250 = 3 (****This line is Red.)
Print
Sheet2 , Sheet3, Sheet4, etc - -which Is Sheets(Sheet & quot + 1)
Print Range
A2: A10 , B2: B10 , etc - -which Is Range(Cells(2, quot), Cells(10, quot)).Address

quot = .Value / 250 '
If quot = Int(quot) Then
With Sheets(Sheet & quot + 1)
.PageSetup.PrintArea = Range(Cells(2, quot), Cells(10, quot)).Address
.PrintOut
End With
End If
End With
End If
End Sub

 

[Randy11]

Also lost needed pop up massage box?

 

[SkipVought]

error??? These lines ALL are preceeded with an APOSTROPHY, as they are COMMENTS!

'250/250 = 1, 500/250 = 2, 750/250 = 3
'print
'Sheet2     , Sheet3     , Sheet4, etc -- which is Sheets(Sheet & quot + 1)
'print range
'A2:A10     , B2:B10, etc -- which is Range(Cells(2, quot), Cells(10, quot)).Address

You NEVER answered what purpose the Message Box performs? So it "pops up" to "remind the operator of a critical function they must perform related to the physical items they are hanlding." What it they FORGOT??? What happens that is DIFFERENT than having NO message box? Again, what purpose does the Message Box perform?

But if you really want one, no one is keeping you from inserting one anywhere you would like.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Hi Again Am struggling with this as you can see. To be more clear on the message box. The box is intended to stop the operator so they can batch the physical items they are handling & match the 1st & last items to the listing I am attempting to print at that moment. Is important that they cut off at 250 intervals as they process.

There are mutiple worksheets in this workbook with a common count that runs in cell B26. The operator is keying items on worksheet that exist for each work source. The output from each of the sources inputs are printed on a consolidated list. So the code I am trying to right.

what I really need at minimum to get me going is to get the Message box up at the 250 item intervals. The print peice I can find a wat around for now if needed.

 

[SkipVought]

A message box is NOT going to allow the user to do ANYTHING! Try doing something on the sheet with a Message box displaying.

What you need is an Event Driven process, that may require some edit checking to validate that the sheet is properly loaded.

This is what I provided, sans edit checking.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's how you might use a Message Box...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim quot  As Variant, ans
    
    If Not Intersect(Target, Range("B26")) Is Nothing Then
        With Target     '[b][highlight]
            ans = MsgBox ("Cell value is " & .value, vbok)
            if ans <> vbok then exit sub    '[/highlight][/b]

            quot = .Value / 250    '
            If quot = Int(quot) Then
                With Sheets(Sheet & quot + 1)
                    .PageSetup.PrintArea = Range(Cells(2, quot), Cells(10, quot)).Address
                    .PrintOut
                End With
            End If
        End With
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Randy,

what I really need at minimum to get me going is to get the Message box up at the 250 item intervals

Have you understood that the condition in the IF statement in Skip's code is met when the value of the target cell is exactly divisible by 250. i.e. 250, 500, 750 etc.?

If you don't like using worksheet change then you can still use Skip's logic. Substituting
With Range("B26")
For
With Target.

The print peice I can find a wat around for now if needed.

The .Printout part of Skip's code should do what you want. Have you tried it?





Gavin

 

[Gavona]

Also other snag if that my worksheet already has a Worksheet_Change in it, how do I also encompass this?

How about:

If Not Intersect(Target, Range("Whatever")) Is Nothing Then
     Do some stuff
ELSE
    If Not Intersect(Target, Range("B26")) Is Nothing Then
          Do something different
    End if
End if

Or, if more complex use Select Case.

Gavin