February 18, 2020

Allow Data Entry into Excel Worksheet Via User Form Only

How to Allow data Entry into an Excel Worksheet Via User-Form or Macro Only.

Here’s the complete VBA code:

Option Explicit

Private Sub cmdExit_Click()
Unload Me
End Sub

Private Sub cmdTransfer_Click()
Dim erow As Long
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = txtCustomerName
Cells(erow, 2) = txtMobile
End Sub

Private Sub Workbook_Open()
ActiveWorkbook.Sheets(“Sheet1″).Protect Password:=”[email protected]”, userinterfaceonly:=True

‘we can also use the line of code below

‘ThisWorkbook.Sheets(“Sheet1″).Protect Password:=”[email protected]”, userinterfaceonly:=True

UserForm1.Show
End Sub

Private Sub cmdDisplayUserForm_Click()
UserForm1.Show
End Sub

Further Reading:

Worksheet.Protect method (Excel)

Advanced User-Form

One thought on “Allow Data Entry into Excel Worksheet Via User Form Only

  1. Thank you for sharing these wonderful features of VBA.

    In the same program of UserForm if I want to restrict duplicate entries who can I do it.

    A user shouldn’t be allowed to enter his details twice and should prompt an error message “Data already exists”.

    Regards,

Comments are closed.