Userform-Transferred Data must not be editable

mrbuttons · Feb 26th 2020

Can we actually make a textbox data from a userform not editable when it is transferred to a column? I have a textbox that collects current date and time when it's submitted. When submitting the form, user will not be able to write or select any date as it comes automatically. (Refer photo below).

Code

With txtdateac
    txtdateac.Text = Format(Now(), "DD/MMM/YYYY/HH/NN/AM/PM")
    End With

However, user can change the date and time after it has been submitted. The only way I can think of is to lock the cells for editing.

Are there any options besides locking the column? Thanks for your support!

mrbuttons · Feb 27th 2020

Just to add on, here's the full code I designed for the form.

Code

Option Explicit
Private Sub UserForm_Initialize()
With Me
.StartUpPosition = 1
.Width = Application.Width * 0.5
.Height = Application.Height * 0.7
.Left = Application.Left + (Application.Width * 0.5) \ 1
.Top = Application.Top + (Application.Height * 0.5) \ 1
End With
'fill date drop down box - 1 to 31
With cmbdate
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
.AddItem "31"
End With
'Fill Month Drop Down box - Takes Jan to Dec
With cmbmonth
.AddItem "JAN"
.AddItem "FEB"
.AddItem "MAR"
.AddItem "APR"
.AddItem "MAY"
.AddItem "JUN"
.AddItem "JUL"
.AddItem "AUG"
.AddItem "SEP"
.AddItem "OCT"
.AddItem "NOV"
.AddItem "DEC"
End With
'Fill Year Drop Down box - Takes 1980 to 2014
With cmbyear
.AddItem "2020"
.AddItem "2021"
.AddItem "2022"
.AddItem "2023"
.AddItem "2024"
.AddItem "2025"
.AddItem "2026"
.AddItem "2027"
.AddItem "2028"
.AddItem "2029"
.AddItem "2030"
.AddItem "2031"
.AddItem "2032"
.AddItem "2033"
.AddItem "2034"
.AddItem "2035"
.AddItem "2036"
.AddItem "2037"
.AddItem "2038"
.AddItem "2039"
.AddItem "2040"
End With

'Fill chart with a list
With cmbchart
.AddItem "Location Chart"
.AddItem "Spread Chart"
.AddItem "Others"
End With

'Fill type of trigger
With cmbtrigger
.AddItem "Fail Alert Limit UCL"
.AddItem "Fail Control Limit UCL"
.AddItem "Fail Spec Limit"
End With

With txtdateac
txtdateac.Text = Format(Now(), "DD/MMM/YYYY/HH/NN/AM/PM")
End With
End Sub
Private Sub btncancel_Click()
Unload Me
End Sub

Private Sub btnsubmit_Click()
'Copy input values to sheet.
Dim irow As Long

'Determine empty row
'emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
With ActiveSheet
irow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(irow, 1).Value = Me.cmbdate.Value & "/" & Me.cmbmonth.Value & "/" & Me.cmbyear.Value
.Cells(irow, 2).Value = Me.txtbatch.Value
.Cells(irow, 3).Value = Me.cmbchart.Value
.Cells(irow, 4).Value = Me.cmbtrigger.Value
.Cells(irow, 5).Value = Me.txtfail.Value
.Cells(irow, 6).Value = Me.txtdisreview.Value
.Cells(irow, 7).Value = Me.txtdateac.Value
.Cells(irow, 8).Value = Me.txtempid.Value
End With
'Clear input controls.
Me.cmbdate.Value = ""
Me.cmbmonth.Value = ""
Me.cmbyear.Value = ""
Me.txtbatch.Value = ""
Me.cmbchart.Value = ""
Me.cmbtrigger.Value = ""
Me.txtfail.Value = ""
Me.txtdisreview.Value = ""
Me.txtempid.Value = ""
End Sub

Display More

**royUK** · Feb 27th 2020

You would need to lock the cells and protect the sheet.

The code would then need to unprotect the sheet before entering data, afterward restore the protection. Alternatively, use Protect with UserInterFace Only, read more and get an example file here

mrbuttons · Mar 2nd 2020

Thanks royUK. I have enabled password protection for the worksheet.

**royUK** · Mar 2nd 2020

Pleased to help

Userform-Transferred Data must not be editable

Participate now!

Share

Tags