UserForm data to locked cell

[leopardhawk]

Hello forum friends, trying to find out if there is a way to move data from a UserForm to a 'locked' cell on a worksheet. I want to leave the cell locked, forcing users to use the UserForm to enter data. Appreciate any suggestions!

Thanks!

 

[Logit]

In your form code, unlock the cell ... move the data ... lock the cell again.

 

[leopardhawk]

If only I knew how to do that. Here is my code to move the data.

Private Sub OKCommandButton_Click()



With Sheets(3)

If Not AllmostEmpty(PayrollTextBox1) Then .Range("F13").Value = PayrollTextBox1.Value

If Not AllmostEmpty(PayrollTextBox2) Then .Range("L13").Value = PayrollTextBox2.Value

If Not AllmostEmpty(RentalTextBox1) Then .Range("F16").Value = RentalTextBox1.Value

If Not AllmostEmpty(RentalTextBox2) Then .Range("L16").Value = RentalTextBox2.Value

If Not AllmostEmpty(PercentComboBox1) Then .Range("F47").Value = PercentComboBox1.Value

If Not AllmostEmpty(PercentComboBox2) Then .Range("F76").Value = PercentComboBox2.Value

If Not AllmostEmpty(PercentComboBox3) Then .Range("F106").Value = PercentComboBox3.Value

End With



With Sheets(5)

If Not AllmostEmpty(PensionTextBox1) Then .Range("D14").Value = PensionTextBox1.Value

If Not AllmostEmpty(PensionTextBox2) Then .Range("H14").Value = PensionTextBox2.Value

If Not AllmostEmpty(BridgeTextBox1) Then .Range("D15").Value = BridgeTextBox1.Value

If Not AllmostEmpty(BridgeTextBox2) Then .Range("H15").Value = BridgeTextBox2.Value

End With



With Sheets(6)

If Not AllmostEmpty(EstCPPTextBox1) Then .Range("D36").Value = EstCPPTextBox1.Value

If Not AllmostEmpty(EstOASTextBox1) Then .Range("D37").Value = EstOASTextBox1.Value

If Not AllmostEmpty(EstCPPTextBox2) Then .Range("I36").Value = EstCPPTextBox2.Value

If Not AllmostEmpty(EstOASTextBox2) Then .Range("I37").Value = EstOASTextBox2.Value



If Not AllmostEmpty(ActCPPTextBox1) Then .Range("D47").Value = ActCPPTextBox1.Value

If Not AllmostEmpty(ActOASTextBox1) Then .Range("D50").Value = ActOASTextBox1.Value

If Not AllmostEmpty(ActCPPTextBox2) Then .Range("I47").Value = ActCPPTextBox2.Value

If Not AllmostEmpty(ActOASTextBox2) Then .Range("I50").Value = ActOASTextBox2.Value



If Not AllmostEmpty(NetTextBox1) Then .Range("D53").Value = NetTextBox1.Value

If Not AllmostEmpty(NetTextBox2) Then .Range("I53").Value = NetTextBox2.Value



If Not AllmostEmpty(SurvivorTextBox1) Then .Range("H91").Value = SurvivorTextBox1.Value

If Not AllmostEmpty(SurvivorTextBox2) Then .Range("H94").Value = SurvivorTextBox2.Value

If Not AllmostEmpty(DeathTextBox1) Then .Range("J91").Value = DeathTextBox1.Value

If Not AllmostEmpty(DeathTextBox2) Then .Range("J94").Value = DeathTextBox2.Value

End With



With Sheets(7)

If Not AllmostEmpty(RRSPTextBox1) Then .Range("F11").Value = RRSPTextBox1.Value

If Not AllmostEmpty(RRSPTextBox2) Then .Range("L11").Value = RRSPTextBox2.Value

End With



With Sheets(9)

If Not AllmostEmpty(InvestTextBox1) Then .Range("F11").Value = InvestTextBox1.Value

If Not AllmostEmpty(InvestTextBox2) Then .Range("L11").Value = InvestTextBox2.Value

End With



Unload Me



End Sub

Thanks!

 

[leopardhawk]

HELP!

The workbook and the worksheets (except one) are all 'protected' and all of the cells are locked.

When my users have completed filling in the field(s) on the UserForm in question, they will press the OK button which then moves the data to the applicable worksheet(s).

My question is, "Using VBA, how do I unlock the cell(s) in question, enter the data and then lock the cell(s) afterward?" Or perhaps another way would be, that when the user clicks the OK button, the worksheet(s) are unprotected, the data is entered and then the worksheet(s) are protected again immediately?? Maybe this is a long shot but I think there probably is a way to do one or the other but I need some advice on how to integrate it into my existing code. Thanks!

 

[Norie]

You can easily unprotect/protect a worksheet.

With Sheets(3)

    .Unprotect ' Passoword:="Optional"

    If Not AllmostEmpty(PayrollTextBox1) Then .Range("F13").Value = PayrollTextBox1.Value

    If Not AllmostEmpty(PayrollTextBox2) Then .Range("L13").Value = PayrollTextBox2.Value

    If Not AllmostEmpty(RentalTextBox1) Then .Range("F16").Value = RentalTextBox1.Value

    If Not AllmostEmpty(RentalTextBox2) Then .Range("L16").Value = RentalTextBox2.Value

    If Not AllmostEmpty(PercentComboBox1) Then .Range("F47").Value = PercentComboBox1.Value

    If Not AllmostEmpty(PercentComboBox2) Then .Range("F76").Value = PercentComboBox2.Value

    If Not AllmostEmpty(PercentComboBox3) Then .Range("F106").Value = PercentComboBox3.Value

    .Protect ' Passoword:="Optional"

End With

 

[leopardhawk]

@Norie Thank you so much! Works perfectly.