Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - For run time error '1004'
Dear Friends,
I'm getting the run time error '1004' - Application defined or Object defined and some times run time error '1104' - unable to set the locked property for the range class. i'm getting this error on the line 37. here is my code. kindly help me at the earliest. Workbook Open code:- Dim I As Integer Dim J As Integer Dim LastLine As Integer Private Sub Workbook_Open() For I = 4 To 500 If Sheet6.Cells(I, 1).Value = "-" Then LastLine = I 'MsgBox LastLine Exit For End If Next I Sheet6.Unprotect Password:="123" Sheet6.Cells(1, 6).Value = Date Sheet6.Range(Cells(1, 1), Cells(2, 256)).Locked = True For I = 3 To LastLine - 1 Sheet6.Cells(I, 7).Locked = True Next I For I = 3 To LastLine - 1 For J = 9 To 38 Sheet6.Cells(I, J).Locked = True Next J Next I For I = 3 To LastLine - 1 For J = 9 To 13 If Sheet6.Cells(I, 8).Value = "" Or Sheet6.Cells(I, 8).Value = "NR" Then Sheet6.Cells(I, J).Locked = True Else Sheet6.Cells(I, J).Locked = False End If Next J Next I For I = 4 To LastLine Step 2 Sheet6.Cells(I, 9).Locked = True Sheet6.Cells(I, 10).Locked = True Next I Sheet6.Range(Cells(3, 39), Cells(LastLine, 256)).Locked = True Sheet6.Range(Cells(LastLine, 1), Cells(65536, 256)).Locked = True Sheet6.Protect Password:="123", AllowFiltering:=True End Sub Worksheet Change Code:- Dim I As Integer Dim Material As String Dim Response As String Dim Msg As String Dim SiteName As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row Mod 2 = 0 Then Material = "SHELTER" SiteName = Cells(Target.Row - 1, 5).Value Else Material = "TOWER" SiteName = Cells(Target.Row, 5).Value End If If Target.Column = 8 Then Me.Unprotect Password:="123" Select Case (Target) Case "": MsgBox "SORRY. YOU SHOULD ENTER EITHER REQ. / NR IN THIS CELL.", vbCritical, "ERROR" Case "NR": Msg = "ARE YOU SURE " & SiteName & "'S " & Material & " IS NOT REQUIRED?. IF YOU CLICK 'YES' THEN YOU CAN NOT CHANGE THE VALUES BACK AGAIN." Response = MsgBox(Msg, vbYesNo, "CONFIRM MATERIAL NOT REQUIRED") If Response = vbYes Then For I = 9 To 38 Cells(Target.Row, I).Value = "NR" Cells(Target.Row, I).Locked = True Next I Else Target.Value = "REQ." End If Case "REQ.": If Material = "TOWER" Then Cells(Target.Row, 9).Locked = False Cells(Target.Row, 9).Value = "-" Cells(Target.Row, 13).Value = "ND" Msg = "PLEASE ENTER THE TOWER TYPE." MsgBox Msg, vbInformation, "ENTER TOWER TYPE" Else Cells(Target.Row, 11).Locked = False Cells(Target.Row, 11).Value = "-" Cells(Target.Row, 13).Value = "ND" Msg = "PLEASE ENTER THE SHELTER SUPPLY VENDOR NAME." MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" End If End Select Me.Protect Password:="123", AllowFiltering:=True End If ' If Target.Column = 9 Then ' Me.Unprotect Password:="123" ' Select Case (Target) ' Case "-": ' 'DO NOTHING ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' Cells(Target.Row, 10).Value = "-" ' End If ' Case "GBT", "RTT", "POLE", "DELTA": ' Cells(Target.Row, 10).Locked = False ' Cells(Target.Row, 10).Value = "-" ' Msg = "PLEASE ENTER THE TOWER HEIGHT." ' MsgBox Msg, vbInformation, "ENTER TOWER HEIGHT" ' Case Else: ' Target.Value = "-" ' Cells(Target.Row, 10).Value = "-" ' End Select ' Me.Protect Password:="123", AllowFiltering:=True ' End If ' ' If Target.Column = 10 Then ' Me.Unprotect Password:="123" ' If Cells(Target.Row, 9).Value = "-" And Target < "-" Then ' Msg = "PLEASE ENTER THE TOWER TYPE DATA FIRST." ' MsgBox Msg, vbInformation, "DATA UNACCEPTANCE" ' Target.Value = "-" ' Else ' Select Case (Target) ' Case "": ' Target.Value = "-" ' Msg = "PLEASE ENTER THE TOWER HEIGHT." ' MsgBox Msg, vbInformation, "ENTER TOWER HEIGHT" ' Case "-": ' 'DO NOTHING ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' Cells(Target.Row, 11).Value = "-" ' End If ' Case Is <= 21: ' If Cells(Target.Row, 9).Value = "GBT" Then ' Msg = "INVALID TOWER HEIGHT." ' MsgBox Msg, vbCritical, "INVALID DATA ENTRY" ' Target.Value = "-" ' Else ' Cells(Target.Row, 11).Value = "-" ' Cells(Target.Row, 11).Locked = False ' Msg = "PLEASE ENTER THE TOWER SUPPLY VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" ' End If ' Case Is 21: ' If Cells(Target.Row, 9).Value < "GBT" Then ' Msg = "INVALID TOWER HEIGHT." ' MsgBox Msg, vbCritical, "INVALID DATA ENTRY" ' Target.Value = "-" ' Else ' Cells(Target.Row, 11).Value = "-" ' Cells(Target.Row, 11).Locked = False ' Msg = "PLEASE ENTER THE TOWER SUPPLY VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" ' End If ' Case Else: ' Target.Value = "-" ' Cells(Target.Row, 11).Value = "-" ' End Select ' End If ' Me.Protect Password:="123", AllowFiltering:=True ' End If ' ' If Target.Column = 11 Then ' Me.Unprotect Password:="123" ' Select Case (Target) ' Case "-": ' 'DO NOTHING ' Case "", " ", "NA": ' Msg = "DON'T FORGET TO ENTER THE SUPPLY VENDOR NAME LATER." ' MsgBox Msg, vbInformation, "INFORMATION" ' Target.Value = "-" ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' End If ' Case Else: ' Cells(Target.Row, 12).Locked = False ' Cells(Target.Row, 12).Value = "-" ' Msg = "PLEASE ENTER THE ERECTION VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER ERECTION VENDOR NAME" ' End Select ' Me.Protect Password:="123", AllowFiltering:=True ' End If End Sub Thanks, Sriram |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - For run time error '1004'
That's an End If by my calculation. Would you care to be more specific?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sriram" wrote in message ... Dear Friends, I'm getting the run time error '1004' - Application defined or Object defined and some times run time error '1104' - unable to set the locked property for the range class. i'm getting this error on the line 37. here is my code. kindly help me at the earliest. Workbook Open code:- Dim I As Integer Dim J As Integer Dim LastLine As Integer Private Sub Workbook_Open() For I = 4 To 500 If Sheet6.Cells(I, 1).Value = "-" Then LastLine = I 'MsgBox LastLine Exit For End If Next I Sheet6.Unprotect Password:="123" Sheet6.Cells(1, 6).Value = Date Sheet6.Range(Cells(1, 1), Cells(2, 256)).Locked = True For I = 3 To LastLine - 1 Sheet6.Cells(I, 7).Locked = True Next I For I = 3 To LastLine - 1 For J = 9 To 38 Sheet6.Cells(I, J).Locked = True Next J Next I For I = 3 To LastLine - 1 For J = 9 To 13 If Sheet6.Cells(I, 8).Value = "" Or Sheet6.Cells(I, 8).Value = "NR" Then Sheet6.Cells(I, J).Locked = True Else Sheet6.Cells(I, J).Locked = False End If Next J Next I For I = 4 To LastLine Step 2 Sheet6.Cells(I, 9).Locked = True Sheet6.Cells(I, 10).Locked = True Next I Sheet6.Range(Cells(3, 39), Cells(LastLine, 256)).Locked = True Sheet6.Range(Cells(LastLine, 1), Cells(65536, 256)).Locked = True Sheet6.Protect Password:="123", AllowFiltering:=True End Sub Worksheet Change Code:- Dim I As Integer Dim Material As String Dim Response As String Dim Msg As String Dim SiteName As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row Mod 2 = 0 Then Material = "SHELTER" SiteName = Cells(Target.Row - 1, 5).Value Else Material = "TOWER" SiteName = Cells(Target.Row, 5).Value End If If Target.Column = 8 Then Me.Unprotect Password:="123" Select Case (Target) Case "": MsgBox "SORRY. YOU SHOULD ENTER EITHER REQ. / NR IN THIS CELL.", vbCritical, "ERROR" Case "NR": Msg = "ARE YOU SURE " & SiteName & "'S " & Material & " IS NOT REQUIRED?. IF YOU CLICK 'YES' THEN YOU CAN NOT CHANGE THE VALUES BACK AGAIN." Response = MsgBox(Msg, vbYesNo, "CONFIRM MATERIAL NOT REQUIRED") If Response = vbYes Then For I = 9 To 38 Cells(Target.Row, I).Value = "NR" Cells(Target.Row, I).Locked = True Next I Else Target.Value = "REQ." End If Case "REQ.": If Material = "TOWER" Then Cells(Target.Row, 9).Locked = False Cells(Target.Row, 9).Value = "-" Cells(Target.Row, 13).Value = "ND" Msg = "PLEASE ENTER THE TOWER TYPE." MsgBox Msg, vbInformation, "ENTER TOWER TYPE" Else Cells(Target.Row, 11).Locked = False Cells(Target.Row, 11).Value = "-" Cells(Target.Row, 13).Value = "ND" Msg = "PLEASE ENTER THE SHELTER SUPPLY VENDOR NAME." MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" End If End Select Me.Protect Password:="123", AllowFiltering:=True End If ' If Target.Column = 9 Then ' Me.Unprotect Password:="123" ' Select Case (Target) ' Case "-": ' 'DO NOTHING ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' Cells(Target.Row, 10).Value = "-" ' End If ' Case "GBT", "RTT", "POLE", "DELTA": ' Cells(Target.Row, 10).Locked = False ' Cells(Target.Row, 10).Value = "-" ' Msg = "PLEASE ENTER THE TOWER HEIGHT." ' MsgBox Msg, vbInformation, "ENTER TOWER HEIGHT" ' Case Else: ' Target.Value = "-" ' Cells(Target.Row, 10).Value = "-" ' End Select ' Me.Protect Password:="123", AllowFiltering:=True ' End If ' ' If Target.Column = 10 Then ' Me.Unprotect Password:="123" ' If Cells(Target.Row, 9).Value = "-" And Target < "-" Then ' Msg = "PLEASE ENTER THE TOWER TYPE DATA FIRST." ' MsgBox Msg, vbInformation, "DATA UNACCEPTANCE" ' Target.Value = "-" ' Else ' Select Case (Target) ' Case "": ' Target.Value = "-" ' Msg = "PLEASE ENTER THE TOWER HEIGHT." ' MsgBox Msg, vbInformation, "ENTER TOWER HEIGHT" ' Case "-": ' 'DO NOTHING ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' Cells(Target.Row, 11).Value = "-" ' End If ' Case Is <= 21: ' If Cells(Target.Row, 9).Value = "GBT" Then ' Msg = "INVALID TOWER HEIGHT." ' MsgBox Msg, vbCritical, "INVALID DATA ENTRY" ' Target.Value = "-" ' Else ' Cells(Target.Row, 11).Value = "-" ' Cells(Target.Row, 11).Locked = False ' Msg = "PLEASE ENTER THE TOWER SUPPLY VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" ' End If ' Case Is 21: ' If Cells(Target.Row, 9).Value < "GBT" Then ' Msg = "INVALID TOWER HEIGHT." ' MsgBox Msg, vbCritical, "INVALID DATA ENTRY" ' Target.Value = "-" ' Else ' Cells(Target.Row, 11).Value = "-" ' Cells(Target.Row, 11).Locked = False ' Msg = "PLEASE ENTER THE TOWER SUPPLY VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" ' End If ' Case Else: ' Target.Value = "-" ' Cells(Target.Row, 11).Value = "-" ' End Select ' End If ' Me.Protect Password:="123", AllowFiltering:=True ' End If ' ' If Target.Column = 11 Then ' Me.Unprotect Password:="123" ' Select Case (Target) ' Case "-": ' 'DO NOTHING ' Case "", " ", "NA": ' Msg = "DON'T FORGET TO ENTER THE SUPPLY VENDOR NAME LATER." ' MsgBox Msg, vbInformation, "INFORMATION" ' Target.Value = "-" ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' End If ' Case Else: ' Cells(Target.Row, 12).Locked = False ' Cells(Target.Row, 12).Value = "-" ' Msg = "PLEASE ENTER THE ERECTION VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER ERECTION VENDOR NAME" ' End Select ' Me.Protect Password:="123", AllowFiltering:=True ' End If End Sub Thanks, Sriram |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - For run time error '1004'
No Bob,
If there is a problem with end if in compiling itself the error might have come. if you are not mentioning like this, please tell em clearly. Sriram "Bob Phillips" wrote: That's an End If by my calculation. Would you care to be more specific? -- HTH Bob Phillips |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - For run time error '1004'
Which line of code is highlighted when you get the error?
-- Regards, Tom Ogilvy "Sriram" wrote: Dear Friends, I'm getting the run time error '1004' - Application defined or Object defined and some times run time error '1104' - unable to set the locked property for the range class. i'm getting this error on the line 37. here is my code. kindly help me at the earliest. Workbook Open code:- Dim I As Integer Dim J As Integer Dim LastLine As Integer Private Sub Workbook_Open() For I = 4 To 500 If Sheet6.Cells(I, 1).Value = "-" Then LastLine = I 'MsgBox LastLine Exit For End If Next I Sheet6.Unprotect Password:="123" Sheet6.Cells(1, 6).Value = Date Sheet6.Range(Cells(1, 1), Cells(2, 256)).Locked = True For I = 3 To LastLine - 1 Sheet6.Cells(I, 7).Locked = True Next I For I = 3 To LastLine - 1 For J = 9 To 38 Sheet6.Cells(I, J).Locked = True Next J Next I For I = 3 To LastLine - 1 For J = 9 To 13 If Sheet6.Cells(I, 8).Value = "" Or Sheet6.Cells(I, 8).Value = "NR" Then Sheet6.Cells(I, J).Locked = True Else Sheet6.Cells(I, J).Locked = False End If Next J Next I For I = 4 To LastLine Step 2 Sheet6.Cells(I, 9).Locked = True Sheet6.Cells(I, 10).Locked = True Next I Sheet6.Range(Cells(3, 39), Cells(LastLine, 256)).Locked = True Sheet6.Range(Cells(LastLine, 1), Cells(65536, 256)).Locked = True Sheet6.Protect Password:="123", AllowFiltering:=True End Sub Worksheet Change Code:- Dim I As Integer Dim Material As String Dim Response As String Dim Msg As String Dim SiteName As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row Mod 2 = 0 Then Material = "SHELTER" SiteName = Cells(Target.Row - 1, 5).Value Else Material = "TOWER" SiteName = Cells(Target.Row, 5).Value End If If Target.Column = 8 Then Me.Unprotect Password:="123" Select Case (Target) Case "": MsgBox "SORRY. YOU SHOULD ENTER EITHER REQ. / NR IN THIS CELL.", vbCritical, "ERROR" Case "NR": Msg = "ARE YOU SURE " & SiteName & "'S " & Material & " IS NOT REQUIRED?. IF YOU CLICK 'YES' THEN YOU CAN NOT CHANGE THE VALUES BACK AGAIN." Response = MsgBox(Msg, vbYesNo, "CONFIRM MATERIAL NOT REQUIRED") If Response = vbYes Then For I = 9 To 38 Cells(Target.Row, I).Value = "NR" Cells(Target.Row, I).Locked = True Next I Else Target.Value = "REQ." End If Case "REQ.": If Material = "TOWER" Then Cells(Target.Row, 9).Locked = False Cells(Target.Row, 9).Value = "-" Cells(Target.Row, 13).Value = "ND" Msg = "PLEASE ENTER THE TOWER TYPE." MsgBox Msg, vbInformation, "ENTER TOWER TYPE" Else Cells(Target.Row, 11).Locked = False Cells(Target.Row, 11).Value = "-" Cells(Target.Row, 13).Value = "ND" Msg = "PLEASE ENTER THE SHELTER SUPPLY VENDOR NAME." MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" End If End Select Me.Protect Password:="123", AllowFiltering:=True End If ' If Target.Column = 9 Then ' Me.Unprotect Password:="123" ' Select Case (Target) ' Case "-": ' 'DO NOTHING ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' Cells(Target.Row, 10).Value = "-" ' End If ' Case "GBT", "RTT", "POLE", "DELTA": ' Cells(Target.Row, 10).Locked = False ' Cells(Target.Row, 10).Value = "-" ' Msg = "PLEASE ENTER THE TOWER HEIGHT." ' MsgBox Msg, vbInformation, "ENTER TOWER HEIGHT" ' Case Else: ' Target.Value = "-" ' Cells(Target.Row, 10).Value = "-" ' End Select ' Me.Protect Password:="123", AllowFiltering:=True ' End If ' ' If Target.Column = 10 Then ' Me.Unprotect Password:="123" ' If Cells(Target.Row, 9).Value = "-" And Target < "-" Then ' Msg = "PLEASE ENTER THE TOWER TYPE DATA FIRST." ' MsgBox Msg, vbInformation, "DATA UNACCEPTANCE" ' Target.Value = "-" ' Else ' Select Case (Target) ' Case "": ' Target.Value = "-" ' Msg = "PLEASE ENTER THE TOWER HEIGHT." ' MsgBox Msg, vbInformation, "ENTER TOWER HEIGHT" ' Case "-": ' 'DO NOTHING ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' Cells(Target.Row, 11).Value = "-" ' End If ' Case Is <= 21: ' If Cells(Target.Row, 9).Value = "GBT" Then ' Msg = "INVALID TOWER HEIGHT." ' MsgBox Msg, vbCritical, "INVALID DATA ENTRY" ' Target.Value = "-" ' Else ' Cells(Target.Row, 11).Value = "-" ' Cells(Target.Row, 11).Locked = False ' Msg = "PLEASE ENTER THE TOWER SUPPLY VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" ' End If ' Case Is 21: ' If Cells(Target.Row, 9).Value < "GBT" Then ' Msg = "INVALID TOWER HEIGHT." ' MsgBox Msg, vbCritical, "INVALID DATA ENTRY" ' Target.Value = "-" ' Else ' Cells(Target.Row, 11).Value = "-" ' Cells(Target.Row, 11).Locked = False ' Msg = "PLEASE ENTER THE TOWER SUPPLY VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" ' End If ' Case Else: ' Target.Value = "-" ' Cells(Target.Row, 11).Value = "-" ' End Select ' End If ' Me.Protect Password:="123", AllowFiltering:=True ' End If ' ' If Target.Column = 11 Then ' Me.Unprotect Password:="123" ' Select Case (Target) ' Case "-": ' 'DO NOTHING ' Case "", " ", "NA": ' Msg = "DON'T FORGET TO ENTER THE SUPPLY VENDOR NAME LATER." ' MsgBox Msg, vbInformation, "INFORMATION" ' Target.Value = "-" ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' End If ' Case Else: ' Cells(Target.Row, 12).Locked = False ' Cells(Target.Row, 12).Value = "-" ' Msg = "PLEASE ENTER THE ERECTION VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER ERECTION VENDOR NAME" ' End Select ' Me.Protect Password:="123", AllowFiltering:=True ' End If End Sub Thanks, Sriram |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - For run time error '1004'
Line 37 would depend on where you start counting. You have posted two
procedures plus some declarations. Saying line 37 is not clear as to which line is giving the error. The question is, paste a small sub-section of code that can be uniquely identified and indicate which specific line in that section is where the error occurs. -- Regards, Tom Ogilvy "Sriram" wrote: No Bob, If there is a problem with end if in compiling itself the error might have come. if you are not mentioning like this, please tell em clearly. Sriram "Bob Phillips" wrote: That's an End If by my calculation. Would you care to be more specific? -- HTH Bob Phillips |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - For run time error '1004'
Try modifying your code like this
Private Sub Worksheet_Change(ByVal Target As Range) On Error goto ErrHandler Application.EnableEvents = False ' your existing code ErrHandler: if err.Number < 0 then msgbox "Error " & err.Number & "has occured" & vbnewline & err.Description end if Err.clear Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Sriram" wrote: Tom u back. Really i'm looking for you. In worksheet change event see the below codes if targer.column=8 then ... ... case"req.": ... ... CELLS(TARGET.ROW,13).VALUE="ND" - HERE I'M GETTING THE ERROR, mentioning run time error '1004' - application defined or object defined else sometimes i'm getting the error run time error '1004' - unable to set the locked property for the range class, in the line of code... if target.column=8 then ... ... if response=vbyes then ... Cells(Target.Row, I).Value = "NR" CELLS(TARGET.ROW,I).LOCKED=TRUE if i marked this line as comment then the above error coming to its above line CELLS(TARGET.ROW,I).VALUE="NR" Please help me out. And one more thing, can you please tell me ur email id or messenger id, so that we can get in touch mroe and i can send my file also to you, so that you can go thru clearly. you can contact me on yahoo msngr or mail in or hotmail / msn msngr with Sriram "Tom Ogilvy" wrote: Which line of code is highlighted when you get the error? -- Regards, Tom Ogilvy "Sriram" wrote: Dear Friends, I'm getting the run time error '1004' - Application defined or Object defined and some times run time error '1104' - unable to set the locked property for the range class. i'm getting this error on the line 37. here is my code. kindly help me at the earliest. Workbook Open code:- Dim I As Integer Dim J As Integer Dim LastLine As Integer Private Sub Workbook_Open() For I = 4 To 500 If Sheet6.Cells(I, 1).Value = "-" Then LastLine = I 'MsgBox LastLine Exit For End If Next I Sheet6.Unprotect Password:="123" Sheet6.Cells(1, 6).Value = Date Sheet6.Range(Cells(1, 1), Cells(2, 256)).Locked = True For I = 3 To LastLine - 1 Sheet6.Cells(I, 7).Locked = True Next I For I = 3 To LastLine - 1 For J = 9 To 38 Sheet6.Cells(I, J).Locked = True Next J Next I For I = 3 To LastLine - 1 For J = 9 To 13 If Sheet6.Cells(I, 8).Value = "" Or Sheet6.Cells(I, 8).Value = "NR" Then Sheet6.Cells(I, J).Locked = True Else Sheet6.Cells(I, J).Locked = False End If Next J Next I For I = 4 To LastLine Step 2 Sheet6.Cells(I, 9).Locked = True Sheet6.Cells(I, 10).Locked = True Next I Sheet6.Range(Cells(3, 39), Cells(LastLine, 256)).Locked = True Sheet6.Range(Cells(LastLine, 1), Cells(65536, 256)).Locked = True Sheet6.Protect Password:="123", AllowFiltering:=True End Sub Worksheet Change Code:- Dim I As Integer Dim Material As String Dim Response As String Dim Msg As String Dim SiteName As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row Mod 2 = 0 Then Material = "SHELTER" SiteName = Cells(Target.Row - 1, 5).Value Else Material = "TOWER" SiteName = Cells(Target.Row, 5).Value End If If Target.Column = 8 Then Me.Unprotect Password:="123" Select Case (Target) Case "": MsgBox "SORRY. YOU SHOULD ENTER EITHER REQ. / NR IN THIS CELL.", vbCritical, "ERROR" Case "NR": Msg = "ARE YOU SURE " & SiteName & "'S " & Material & " IS NOT REQUIRED?. IF YOU CLICK 'YES' THEN YOU CAN NOT CHANGE THE VALUES BACK AGAIN." Response = MsgBox(Msg, vbYesNo, "CONFIRM MATERIAL NOT REQUIRED") If Response = vbYes Then For I = 9 To 38 Cells(Target.Row, I).Value = "NR" Cells(Target.Row, I).Locked = True Next I Else Target.Value = "REQ." End If Case "REQ.": If Material = "TOWER" Then Cells(Target.Row, 9).Locked = False Cells(Target.Row, 9).Value = "-" Cells(Target.Row, 13).Value = "ND" Msg = "PLEASE ENTER THE TOWER TYPE." MsgBox Msg, vbInformation, "ENTER TOWER TYPE" Else Cells(Target.Row, 11).Locked = False Cells(Target.Row, 11).Value = "-" Cells(Target.Row, 13).Value = "ND" Msg = "PLEASE ENTER THE SHELTER SUPPLY VENDOR NAME." MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" End If End Select Me.Protect Password:="123", AllowFiltering:=True End If ' If Target.Column = 9 Then ' Me.Unprotect Password:="123" ' Select Case (Target) ' Case "-": ' 'DO NOTHING ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' Cells(Target.Row, 10).Value = "-" ' End If ' Case "GBT", "RTT", "POLE", "DELTA": ' Cells(Target.Row, 10).Locked = False ' Cells(Target.Row, 10).Value = "-" ' Msg = "PLEASE ENTER THE TOWER HEIGHT." ' MsgBox Msg, vbInformation, "ENTER TOWER HEIGHT" ' Case Else: ' Target.Value = "-" ' Cells(Target.Row, 10).Value = "-" ' End Select ' Me.Protect Password:="123", AllowFiltering:=True ' End If ' ' If Target.Column = 10 Then ' Me.Unprotect Password:="123" ' If Cells(Target.Row, 9).Value = "-" And Target < "-" Then ' Msg = "PLEASE ENTER THE TOWER TYPE DATA FIRST." ' MsgBox Msg, vbInformation, "DATA UNACCEPTANCE" ' Target.Value = "-" ' Else ' Select Case (Target) ' Case "": ' Target.Value = "-" ' Msg = "PLEASE ENTER THE TOWER HEIGHT." ' MsgBox Msg, vbInformation, "ENTER TOWER HEIGHT" ' Case "-": ' 'DO NOTHING ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' Cells(Target.Row, 11).Value = "-" ' End If ' Case Is <= 21: ' If Cells(Target.Row, 9).Value = "GBT" Then ' Msg = "INVALID TOWER HEIGHT." ' MsgBox Msg, vbCritical, "INVALID DATA ENTRY" ' Target.Value = "-" ' Else ' Cells(Target.Row, 11).Value = "-" ' Cells(Target.Row, 11).Locked = False ' Msg = "PLEASE ENTER THE TOWER SUPPLY VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" ' End If ' Case Is 21: ' If Cells(Target.Row, 9).Value < "GBT" Then ' Msg = "INVALID TOWER HEIGHT." ' MsgBox Msg, vbCritical, "INVALID DATA ENTRY" ' Target.Value = "-" ' Else ' Cells(Target.Row, 11).Value = "-" ' Cells(Target.Row, 11).Locked = False ' Msg = "PLEASE ENTER THE TOWER SUPPLY VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME" ' End If ' Case Else: ' Target.Value = "-" ' Cells(Target.Row, 11).Value = "-" ' End Select ' End If ' Me.Protect Password:="123", AllowFiltering:=True ' End If ' ' If Target.Column = 11 Then ' Me.Unprotect Password:="123" ' Select Case (Target) ' Case "-": ' 'DO NOTHING ' Case "", " ", "NA": ' Msg = "DON'T FORGET TO ENTER THE SUPPLY VENDOR NAME LATER." ' MsgBox Msg, vbInformation, "INFORMATION" ' Target.Value = "-" ' Case "NR": ' If Cells(Target.Row, 8).Value = "REQ." Then ' Target.Value = "-" ' End If ' Case Else: ' Cells(Target.Row, 12).Locked = False ' Cells(Target.Row, 12).Value = "-" ' Msg = "PLEASE ENTER THE ERECTION VENDOR NAME." ' MsgBox Msg, vbInformation, "ENTER ERECTION VENDOR NAME" ' End Select ' Me.Protect Password:="123", AllowFiltering:=True ' End If End Sub Thanks, Sriram |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - For run time error '1004'
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. "Tom Ogilvy" wrote: Try modifying your code like this Private Sub Worksheet_Change(ByVal Target As Range) On Error goto ErrHandler Application.EnableEvents = False ' your existing code ErrHandler: if err.Number < 0 then msgbox "Error " & err.Number & "has occured" & vbnewline & err.Description end if Err.clear Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - For run time error '1004'
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. "Tom Ogilvy" wrote: Try modifying your code like this Private Sub Worksheet_Change(ByVal Target As Range) On Error goto ErrHandler Application.EnableEvents = False ' your existing code ErrHandler: if err.Number < 0 then msgbox "Error " & err.Number & "has occured" & vbnewline & err.Description end if Err.clear Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - For run time error '1004'
Tom,
Thanks, Thank You and Thanks a Lot... Now the time my problem is solved. Because of your support I came out from my fedups and now I trust that I can complete my project in next 3-4 days. Thanks a lot my friend. Somethign Personal:- I saw your details by clicking your name and really great job Tom. You got the MVP certification in 2 years after starting posting here. Now I too made a decision to acheive this MVP in next 6 months by helping more and more new comers. Wish me for my better than the best growth. And I got your email id also. Please don't mind if contact you thru email. Bye & Take Care Ur Friend 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. "Tom Ogilvy" wrote: Try modifying your code like this Private Sub Worksheet_Change(ByVal Target As Range) On Error goto ErrHandler Application.EnableEvents = False ' your existing code ErrHandler: if err.Number < 0 then msgbox "Error " & err.Number & "has occured" & vbnewline & err.Description end if Err.clear Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - For run time error '1004'
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Run time error '1004': Generaol ODBC error | Excel Programming | |||
Run time error 1004 General ODCB Error | Excel Programming | |||
Application Run Time Error 1004 and Stack Error | Excel Programming |