jazznaura
01-15-2008, 10:29 AM
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
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