Hi,

"I'm working on a sheet were sheet 1 has the summary of all the sheets
from sheet 2 to sheet 32 day(where sheet 2 to sheet 32 are given name as day
1,2,3, and so on to 31st)and which has the actual data

I have to work on number of batches, in which 1 batch have an
max count of 50. So I have given a countif function in cell D2. saying that
whenever there is "completed" in cell G10:G110 show me the count of
"Completed" in Cell "D2", as once I reach to the count of 50 I need to close
that particular batch. and so on I have given Countif function in other cells
as well, which refer to other ranges for example in the case of Cell "E2"
Countif function will refer to range G150:G250.
So I have given Countif function in Cell as below: -
D2,E2,F2,G2,H2,I2
and J2 on each sheet. Were cell D2 is for the first batch, cell E2 is for
second batch, cell F2 for third batch and so on.
What I want is whenever cell D2,E2,F2,G2,H2,I2 and J2 shows up a count of
50, it should show up a message box saying "" Do you want to Close the
Batch?"".
Whereas Cell D2 will show up count 50 for the first batch and thereafter the
second batch, so there is the time difference in each batch. So whenever the
above cells show an count of 50 it should pop up with the above message.
I want a macro for this. but the macro should not include sheet 1 because it
has the summary of the rest of the sheets.


--
vijay

Hi,

I'm not at all clear on what you want but we can start here. Alt+F11 to open
VB editor. Double click 'ThisWorkbook' and paste the code below in. Now if d2
in any sheet except the first reaches 50 you will get a popup message

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
For x = 2 To Worksheets.Count
If Sheets(x).Range("D2").Value = 50 Then
MsgBox Sheets(x).Name & " " & "has reached 50"
End If
Next
End Sub

Mike

"vijay" wrote:

Hi,

"I'm working on a sheet were sheet 1 has the summary of all the sheets
from sheet 2 to sheet 32 day(where sheet 2 to sheet 32 are given name as day
1,2,3, and so on to 31st)and which has the actual data

I have to work on number of batches, in which 1 batch have an
max count of 50. So I have given a countif function in cell D2. saying that
whenever there is "completed" in cell G10:G110 show me the count of
"Completed" in Cell "D2", as once I reach to the count of 50 I need to close
that particular batch. and so on I have given Countif function in other cells
as well, which refer to other ranges for example in the case of Cell "E2"
Countif function will refer to range G150:G250.
So I have given Countif function in Cell as below: -
D2,E2,F2,G2,H2,I2
and J2 on each sheet. Were cell D2 is for the first batch, cell E2 is for
second batch, cell F2 for third batch and so on.
What I want is whenever cell D2,E2,F2,G2,H2,I2 and J2 shows up a count of
50, it should show up a message box saying "" Do you want to Close the
Batch?"".
Whereas Cell D2 will show up count 50 for the first batch and thereafter the
second batch, so there is the time difference in each batch. So whenever the
above cells show an count of 50 it should pop up with the above message.
I want a macro for this. but the macro should not include sheet 1 because it
has the summary of the rest of the sheets.


--
vijay

Hi Mike,

What I want is a message box should pop up saying "Do you want to Close the
Batch" whenever there is a count showing as only 50 in the Cells
D2,E2,F2,G2,H2 and J2 on all the sheets that I have in the workbook, except
the sheet1 which has the summary report.
--
vijay


"Mike H" wrote:

Hi,

I'm not at all clear on what you want but we can start here. Alt+F11 to open
VB editor. Double click 'ThisWorkbook' and paste the code below in. Now if d2
in any sheet except the first reaches 50 you will get a popup message

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
For x = 2 To Worksheets.Count
If Sheets(x).Range("D2").Value = 50 Then
MsgBox Sheets(x).Name & " " & "has reached 50"
End If
Next
End Sub

Mike

"vijay" wrote:

Hi,

"I'm working on a sheet were sheet 1 has the summary of all the sheets
from sheet 2 to sheet 32 day(where sheet 2 to sheet 32 are given name as day
1,2,3, and so on to 31st)and which has the actual data

I have to work on number of batches, in which 1 batch have an
max count of 50. So I have given a countif function in cell D2. saying that
whenever there is "completed" in cell G10:G110 show me the count of
"Completed" in Cell "D2", as once I reach to the count of 50 I need to close
that particular batch. and so on I have given Countif function in other cells
as well, which refer to other ranges for example in the case of Cell "E2"
Countif function will refer to range G150:G250.
So I have given Countif function in Cell as below: -
D2,E2,F2,G2,H2,I2
and J2 on each sheet. Were cell D2 is for the first batch, cell E2 is for
second batch, cell F2 for third batch and so on.
What I want is whenever cell D2,E2,F2,G2,H2,I2 and J2 shows up a count of
50, it should show up a message box saying "" Do you want to Close the
Batch?"".
Whereas Cell D2 will show up count 50 for the first batch and thereafter the
second batch, so there is the time difference in each batch. So whenever the
above cells show an count of 50 it should pop up with the above message.
I want a macro for this. but the macro should not include sheet 1 because it
has the summary of the rest of the sheets.


--
vijay