Tom,

Please have a look at my below code, where I'm trying to insert new lines
whenever the last but one line data is entered.
(Note I've mentioned LastLine-2, coz I've merged the cells)

It is working very fine and no errors, but What I want here is that,
whenever the lines are getting inserted it is asking a message box on its own
(excel msg box) with the below comments.
"Do you want to replace the contents of the destination cells?" Yes/No.

So, if I enter Yes, no problem it is working fine, if I enter No, and error
occured and it is been captured by your err handler (u gave me previously),
which is showing "Error 1004 occured. PasteSpecial method of Range class
failed."

And again if I enter the data then it is asking the same question, if click
Yes working fine no problem. But if I click No it is showing the error and I
protected the sheet with a Password mentioned in the code, which is Not
Working if I click No.

What I want here is that, is there any method / option to find out and click
the Yes button automatically when the dialog box or msg window is been shown
by excel on its own.

Please have a look at my codes.
------- Code Starts

If Target.Column = 2 Then
LastLine = Cells(2, 40).Value 'In this cell, I entered the lastline
value from the workbook open event itself
'MsgBox "SHEET LSTLIN: " & LastLine
Me.Unprotect Password:="123"
If Target.Row = LastLine - 2 Then
If Cells(Target.Row, Target.Column).Value < "" Then
'MsgBox LastLine
Range(Cells(Target.Row, 1), Cells(LastLine,
38)).EntireRow.Copy
Range(Cells(LastLine, 1), Cells(LastLine + 2,
38)).PasteSpecial xlPasteAll
Range(Cells(LastLine, 2), Cells(LastLine, 6)).Value = ""
LastLine = LastLine + 2
Cells(2, 40).Value = LastLine
End If
End If
Call RepeatTask
Me.Protect Password:="123", AllowFiltering:=True
End If

------- Code Ends

If you want to know about the code of the function RepeatTask, here it is.

------- Code Begins

Sub RepeatTask()

For I = 3 To LastLine - 1
Cells(I, 40).Value = Cells(I, 37).Value
Next I

End Sub

------- Code Ends

Kindly help me how to find out and click the 'Yes' button whenever excel is
showing a msg box or dialog window.

Thanks

Sriram



"Tom Ogilvy" wrote:

Look in Excel VBA help at the VarType function.

--
Regards,
Tom Ogilvy


"Sriram" wrote in message
...
Tom,

It is working fine till now.

Now I've come up with one mroe problem. Could you tell me how to find if
the
cell's valu is Non Date.

I'm trying this with Select Case event like below,

If Target.Column = 18 Then
Me.Unprotect Password:="123"
Select Case (Target)
Case "":
Cells(Target.Row, 34).Value = "NOT OFFERED"
Case "NR":
If Cells(Target.Row, 8).Value = "REQ." Then
Msg = "SORRY. ONLY DATE VALUE CAN BE ENTERED. ENTER THE
VALUE IN THE BELOW FORMAT." & vbNewLine & " MM/DD/YYYY "
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
End If
Case Is < Cells(Target.Row, 17).Value:
Msg = "SORRY. ENTERED DATE SHOULD BE GREATER THAN OR
EQUAL TO THE INTERNAL INSPECTION PASSED DATE." & vbNewLine & " KINDLY
ENTER A
NEW DATE IN THIS FORMAT 'MM/DD/YYYY' ."
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
Case Is = Cells(Target.Row, 17).Value:
Cells(Target.Row, 20).Locked = False
Cells(Target.Row, 34).Value = "NOT INSPECTED"
Case Else:
Msg = "SORRY. ONLY DATE VALUE CAN BE ENTERED. ENTER THE
VALUE IN THE BELOW FORMAT." & vbNewLine & " MM/DD/YYYY "
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
End Select
Me.Protect Password:="123", AllowFiltering:=True
End If

Now the problem is, if I entered some text values (means string values,
like
A, B or something else), it is taking as such without showing any error
and
updating the 34th column as Not Inspected.

I want to find out if the value of the cell is String, then my witten
error
msg should dispaly.

Please tell me is there anything available for find string like IsDate,
IsNumeric etc.

Sriram.