Unlock Worksheet Protection, Update Cells based on User Form Data and relock on Button Click

[brianv]

(Excel 2010)
Its been a number of years since ive posted, but the last time I did you all helped out alot, and we havent had the need to make any changes until now.

We are a Fire Alarm Contractor and have a WB called "Project Flow Log" which lists all our Active Projects on its own Worksheet and Archive Projects on their respective archive by year worksheets.

This Workbook is shared by 12+ persons, (Sales, Design & Project Managers) so its actively used. Each row is a distinct project, and data extends to Column B:AU. Currently we have 450+ active projects and typically 10 new projects are added each week. The project are numbered sequentially by year, ex: 18-1000, 18-1001, 18-1002, etc....

Typically, when a new project is sold, the Salesman opens the workbook and enters data in the first or so 8-10 cells of the respective project row. However, recently in the last few months weve had 2 issue that have become problematic..
1- Often our salesman get lazy and dont enter all data needed,
2- Saleman forget to add a sold project, then to cover their a$$, enter a "Sold Date" from a few weeks prior and not the date they actually enter in the data like they are supposed to.

So I'm creating a UserForm that is opened by a simple button, and that UserForm will have those 8-10 required text boxes that must be filled in and upon clicking the submit button it will then populate their respective cells. In addition, the Sold Date cell will now be updated based on the date the UserForm was Submitted/completed.

So what i want to do is permanently Lock/Protect columns B:O (Sales Data columns). Then when the "New Project" button is clicked, the UserForm opens, the salesman enters data in those 8-10 text boxes and then when the UserForms "Submit" button is clicked:
1- Unlock Columns B:O (without prompting for a passcode, meaning can the passcode be written into the VBA code)
2- UserForm then populates their respective cells
3- Relock Columns B:O (again without promting for a passcode)

This way, these cells cant be manually overwritten, and if they need to be, i will do it manually, since Im the keyholder.

I can create the UserForm, I can link the Textboxes to the appropriate cells, but i dont know how to include the VBA Language to unlock and lock the columns.

Any help is appreciated.

Thanks
BV

 

[Yongle]

Try to update the values in one section of your code
Unprotect the sheet in the line above first cell edit and then reprotect immediately in the line below the last cell edit

  Sheets("NameOfSheet").Unprotect "password"

[I]... update cells from UserForm input[/I]

  Sheets("NameOfSheet").Protect "password"

 

[brianv]

Oh, That worked perfectly... Thank you. I added the protect & unprotect code to just before & after the "copy data to worksheet"... so the self checks test out first.

if i may, Im having a difficult time with the "Sold Date" cell... I would like that cell (O??) to be updated with the date (MM/DD/YY) that the project was entered in on. Can you help me with that code?

Heres the code i have...

Private Sub CmdSubmit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Active")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'Range("A65535").End(xlUp).Offset(1, 0).Select

'check for JobNumber
If Trim(Me.TextJobNumber.Value) = "" Then
Me.TextJobNumber.SetFocus
MsgBox "Please enter a Job Number"
Exit Sub
End If

'check for PV Wage
If Trim(Me.ComboPVWage.Value) = "" Then
Me.ComboPVWage.SetFocus
MsgBox "Please select if its Prevailing Wage"
Exit Sub
End If

'check for Project Name
If Trim(Me.TextProjectName.Value) = "" Then
Me.TextProjectName.SetFocus
MsgBox "Please enter a Project Name"
Exit Sub
End If

'check for SystemType
If Trim(Me.TextSystemType.Value) = "" Then
Me.TextSystemType.SetFocus
MsgBox "Please enter an System Type"
Exit Sub
End If

'check for Value
If Trim(Me.TextValue.Value) = "" Then
Me.TextValue.SetFocus
MsgBox "Please enter a Value:"
Exit Sub
End If

'check for Design Hours
If Trim(Me.TextDesignHours.Value) = "" Then
Me.TextValue.SetFocus
MsgBox "Please enter Design Hours:"
Exit Sub
End If

'check for Design OT
If Trim(Me.ComboDesignOT.Value) = "" Then
Me.TextValue.SetFocus
MsgBox "Is Design OT Authorized?"
Exit Sub
End If

'check for Install Hours
If Trim(Me.TextInstallHours.Value) = "" Then
Me.TextValue.SetFocus
MsgBox "Please enter Installation Hours:"
Exit Sub
End If

'check for Install OT
If Trim(Me.ComboInstallOT.Value) = "" Then
Me.TextValue.SetFocus
MsgBox "Is Installation OT Authorized?"
Exit Sub
End If

'Unprotect WorkSheet
Sheets("Active").Unprotect "password"

'copy the data to the worsheet
ws.Cells(iRow, 2).Value = Me.TextJobNumber.Value
ws.Cells(iRow, 5).Value = Me.ComboPVWage.Value
ws.Cells(iRow, 6).Value = Me.TextProjectName.Value
ws.Cells(iRow, 7).Value = Me.TextCustomer.Value
ws.Cells(iRow, 8).Value = Me.TextSalesman.Value
ws.Cells(iRow, 11).Value = Me.TextSystemType.Value
ws.Cells(iRow, 12).Value = Me.TextPanel.Value
ws.Cells(iRow, 47).Value = Me.TextValue.Value
ws.Cells(iRow, 20).Value = Me.TextDesignHours.Value
ws.Cells(iRow, 21).Value = Me.ComboDesignOT.Value
ws.Cells(iRow, 48).Value = Me.TextInstallHours.Value
ws.Cells(iRow, 49).Value = Me.ComboInstallOT.Value

'close the New Expense Record Form
Unload Me

Range("A65535").End(xlUp).Offset(1, 0).Select

'Reprotect Worksheet
Sheets("Active").Protect "password"

End Sub

 

[Yongle]

I would like that cell (O??) to be updated with the date (MM/DD/YY) that the project was entered in on

ws.Cells(iRow, 15).Value = Date

 

[brianv]

Thank you!!! is there a way that my UserFrm can be set popup in the center of the Active Excel Window?

 

[brianv]

If i may seek your assistance one last time.... I'm already finding the last row:

Private Sub CmdSubmit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Active")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

So how do i then Copy the next blank row and reinsert into the same position, thereby increasing my working range by 1 row.


Ex: DataRange is A12:BR257, so row 257 is the last row of data. How do i then copy row 258 and "insert copied cells" back into row 258?

Thanks in advance.