UserForm data to locked cell

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,964
In your form code, unlock the cell ... move the data ... lock the cell again.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
If only I knew how to do that. Here is my code to move the data.
Code:
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

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
HELP! :unsure:

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
You can easily unprotect/protect a worksheet.
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,540
Messages
5,625,412
Members
416,101
Latest member
CCoetzee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top