Subscript out of Range and Sheet gets protected

Question

Private Sub cmdsub_Click()
UnProtect
'Copy input values to sheet.
Dim lrow As Long
Dim ws As Worksheet
Dim Lvalue As Date
Lvalue = Now
If Me.cbox4.Value = "pending" Then
Set ws = ThisWorkbook.Sheets("Pending")
Else
Set ws = ThisWorkbook.Sheets("Completed")
End If
lrow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
If Me.txt13.Value = "" Then
Me.txt13.SetFocus
MsgBox "'Mandatory", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.cbox1.Value = "" Then
Me.cbox1.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.cbox2.Value = "" Then
Me.cbox2.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.cbox2.Value = "" Then
Me.cbox2.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.txt1.Value = "" Then
Me.txt1.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.txt2.Value = "" Then
Me.txt2.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.txt3.Value = "" Then
Me.txt3.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.txt4.Value = "" Then
Me.txt4.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.txt5.Value = "" Then
Me.txt5.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.txt6.Value = "" Then
Me.txt6.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.txt7.Value = "" Then
Me.txt7.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
ElseIf Me.cbox4.Value = "" Then
Me.cbox4.SetFocus
MsgBox "'Mandatory...", vbOKOnly, "Required Field"
Exit Sub
End If
With ws
    .Cells(lrow, 1).Value = Me.lbl1.Caption
    .Cells(lrow, 2).Value = Me.txt13.Value
    .Cells(lrow, 3).Value = cbox1.Value
    .Cells(lrow, 4).Value = cbox2.Value
    .Cells(lrow, 5).Value = cbox3.Value
    .Cells(lrow, 6).Value = Me.txt1.Value
    .Cells(lrow, 7).Value = Me.txt2.Value
    .Cells(lrow, 8).Value = Me.txt3.Value
    .Cells(lrow, 9).Value = Me.txt4.Value
    .Cells(lrow, 10).Value = Me.txt5.Value
    .Cells(lrow, 11).Value = Me.txt6.Value
    .Cells(lrow, 12).Value = Me.txt7.Value
    .Cells(lrow, 13).Value = cbox4.Value
    .Cells(lrow, 23).Value = Me.txt15.Value
    .Cells(lrow, 24).Value = "Unknown"
            End With
'Clear input controls.
Me.lbl1.Caption = Now()
cbox1.Value = ""
cbox2.Value = ""
cbox3.Value = ""
Me.txt1.Value = ""
Me.txt2.Value = ""
Me.txt3.Value = ""
Me.txt4.Value = ""
Me.txt5.Value = ""
Me.txt6.Value = ""
Me.txt7.Value = ""
Me.txt13.Value = ""
Me.txt15.Value = ""
cbox4.Value = ""
Cmb1.Value = ""
MoveAndDelete
cbox1.Visible = False
cbox2.Visible = False
cbox3.Visible = False
txt1.Visible = False
txt2.Visible = False
txt3.Visible = False
txt4.Visible = False
txt5.Visible = False
txt6.Visible = False
txt7.Visible = False
cbox4.Visible = False
txt13.Visible = False
Label1.Visible = False
Label2.Visible = False
Label3.Visible = False
Label4.Visible = False
Label5.Visible = False
Label6.Visible = False
Label7.Visible = False
Label8.Visible = False
Label9.Visible = False
Label10.Visible = False
Label11.Visible = False
Label12.Visible = False
txt15.Visible = False
Label21.Visible = False
cmdsub.Visible = False
cmdnxt.Visible = False
abc
bcd
Protect
Application.DisplayAlerts = False
    End Sub

  Sub UnProtect()
  Dim wSheet As Worksheet
   For Each wSheet In Worksheets
    wSheet.UnProtect Password:="Passworld"
  Next wSheet
  End Sub

 Sub Protect()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
    wSheet.Protect Password:="Passworld"
Next wSheet
 End Sub

When I run the above code it gives me an error:

runtime error 9. Subscript out of range.

I'm unable to debug it. I have tried several possibilities but of no use. Also I have this protect and unprotect sheet what happens is I have other sheet as well when I'm using this macro it also gets protected.

Can you give us which line throws the error and what's the detail of it? — qshng, May 11 '15 at 14:19
It does't throws any error as such, but the sheet get protected automatically when I try to add or copy something it gives an error that the sheet is protected. — user2842252, May 11 '15 at 14:27
The sheets get protected because you have this `Protect` at the end of the code. If you don't want it to be protected then simply delete this line — qshng, May 11 '15 at 14:36
For debug, use **breakpoints on almost every line** and when it's going through, **get rid of previous breakpoint**. You will see on which line you have the error (this is specific for Out of Range errors as they are not always specified on a line) — R3uK, May 11 '15 at 14:46
yes, but when the Userform is opened or someone is working on this worksheet the other workbooks get protected as well. Which should'nt be the case. — user2842252, May 11 '15 at 14:50
When you say you are "unable to debug it", do you mean that you haven't been able to resolve the problem by debugging, or that the debugger isn't working? — FreeMan, May 11 '15 at 15:04
Couple of questions `1` Do you have a sheets called "Pending" and "Completed". And if it looks like that you have those then check if they have any extra space(s) `2` What is "abc", "bcd" and "protect". If they are procedures then can you please include their code as well — Siddharth Rout, May 11 '15 at 22:14
Similarly what code does `UnProtect` and `MoveAndDelete` contain. I have a feeling that the actual problem is in your sheet names. Also regarding you protection issue, it could be because of the code in `Unprotect/Protect` procedures... — Siddharth Rout, May 11 '15 at 22:20
I have updated the code to reflect the protect and unprotect precedures. Also update the same. — user2842252, May 12 '15 at 08:54

score 0 · Answer 1 · answered May 12 '15 at 08:58

0

Check the lrow value setting (ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row) the 0 in the offset function may be causing your problem, remove it as it is not required.

answered May 12 '15 at 08:58

Tom

747
5
16

I have tried removing the 0 from the offset but still I'm getting the same error. Runtime error 9. – user2842252 May 12 '15 at 09:16
Things may also go wrong if the first row is row number 1. This will cause lrow to be 0, leading to errors in the .Cells(lrow, 1). parts of your procedure. But this would not hinder you in debugging the code. Sure there is no way to debug the code? – Tom May 12 '15 at 09:24
Is there anything else to do this. Since the data starts from the second row after the header. – user2842252 May 12 '15 at 09:43
lrow = ws.UsedRange.Cells(1, 1).Offset(RowOffset:=1).Row After that check if lrow is not 0 because this would cause a problem later on – Tom May 12 '15 at 09:52
Thanks Tom for the above code can you also please help me with the protect and Unprotect code. Also I'm getting the Error Application defined 1004. – user2842252 May 12 '15 at 10:27
It may be a problem with trying to protect or unprotect sheet where this is not applicable. You could try to add an if statement before protecting or unprotecting a worksheet (wSheet.ProtectionMode) to see if a change is needed – Tom May 12 '15 at 10:53
Tom, I really appreciate your effort, I'm very much new to VBA so can you please help me with the code also assist as to where to insert the same. – user2842252 May 12 '15 at 11:20
There are two places where you may want/need to add this: If wSheet.ProtectionMode Then wSheet.UnProtect Password:="Passworld" End If If not wSheet.ProtectionMode Then wSheet.Protect Password:="Passworld" – Tom May 12 '15 at 12:04

Subscript out of Range and Sheet gets protected

1 Answers1