Solved: stop macro and restore password

[jazznaura]

Hi all,

I have written this code with some help from people on this forum.
I?m sure there is a better way to right this but I?m new to VBA.
I have a quick question about the highlighted code.
The Code checks named ranges to ensure required fields have been filled, if not display a message, ?Please Complete All Fields?. My question is about the next line:
If Response = vbOK Then Exit Sub
I don?t know how to stop the macro and restore the password. Tried the following but that dosen't work. anybody help.
If Response = vbOK Then Sheets("input").protect Password:="**********"
Exit Sub
next
Any help would by appericated.
Thanks
jazznaura

Dim Cell As Range, Source As Range
Dim Response As String
Dim rangeName As String
Sheets("input").Unprotect Password:="**********"
If Range("errofgen") = "N" Then
Set Source = Sheets("input").Range("WeekNumber, dates, names, shift, apma, reason, tpnd, description, tihi, qtyad, cash, selqty, sysqty, res, dsr, goods, reshis, adjhis, selhis, textbox, errofgen")
ElseIf Range("errofgen") = "n" Then
Set Source = Sheets("input").Range("WeekNumber, dates, names, shift, apma, reason, tpnd, description, tihi, qtyad, cash, selqty, sysqty, res, dsr, goods, reshis, adjhis, selhis, textbox, errofgen")
Else
Set Source = Sheets("input").Range("WeekNumber, dates, names, shift, apma, reason, tpnd, description, tihi, qtyad, cash, selqty, sysqty, res, dsr, goods, reshis, adjhis, selhis, textbox, errofgen, genhan, sir")
If Application.WorksheetFunction.CountA(Source) < Source.Cells.Count Then
For Each Cell In Source.SpecialCells(xlCellTypeBlanks)
rangeName = Cell.Address(0, 0)
On Error Resume Next
rangeName = Cell.Name.Name:
On Error GoTo 0
Response = MsgBox("Please Complete All Fields ", (vbInformation))
If Response = vbOK Then Exit Sub ' need to protect "input" sheet here
Next
End If
End If
Range("a1").Select
Call UPDATE1
End Sub

[Bob Phillips]

That should work, what happens for you?

[jazznaura]

if i use the following it says Next without For error. even though there is a For.

If Response = vbOK Then
Sheets("input").protect Password:="**********"
Exit Sub
Next

My problem is i'm still learning how to structure the code.
thanks for the reply.

[jazznaura]

got it !

end if for the If Response inside the for next statement.

that seems to work. didn't know i needed it for a If Response.

could you explain why i didn't need it for

If Response = vbOK Then Exit Sub

but do need it for

If Response = vbOK Then
Sheets("input").protect Password:="**********"
Exit Sub
end if
Next





thanks again xld for having the time to respond.


jazznaura

[Bob Phillips]

Well doen yourself for cracking it. It's obvious now you show it <bg>