Excel VBA Edit Record in User Form?

Question

Is there a way to edit the already entered records (rows) within the UserForm rathe than directly on the spreadsheet? I want to lock the cells so they can View, but not Edit directly on the spreadsheet.

I can perhaps add "previous" and "next record" buttons, OR they can select the record on the sheet, & the form appears populated with that record info for them to edit? Is that possible? How on earth would I code that?

The most records / rows they'd enter would be about 5 ... 10 max but extremely unlikely.

I have it like this for entry:

'assign obj names
Dim iRow as Long
Dim ws as Worksheet
Set ws = Worksheets ("DBase")

'find first empty row
iRow = ws.Cells (rows.Count, 1) _
.End (x1up).offset(1, 0).Row

'populate cells with field values
ws.cells(iRow, 1).Value = Me.cmbName.Value
ws.cells(iRow, 2).Value = me.txtEmail.Value

'clear data upon record entry
Me.cmbName.Value= ""
Me.txtEmail.value = ""
End Sub

and that works perfect, but how do i get the spreadsheet record info to populate the field so they can edit within the field and not mess around with my beautiful spreadsheet? lol. Because it's going to be on LOCK down, the most they'll be able to do is See the Record pop up when they hit the Add button.

there are exactly 28 fields for entry

Answer 1

It seems that you want to be able to only edit data through entries on the userform.



If so, the simple solution is to unprotect the worksheet when you show the userform, provided your userform 'Modal' property is set to Show Modal = True. With this setting, the user cannot access the underlying worksheet as long as the userform is active. Then, after data entry is made, simply protect the worksheet on exit.



For example:



Sub shwUserform1 ()

UserForm1.Show

Activesheet.Unprotect Password:= "Your Password"

End Sub



Then when closing the userform include code such as:



Unload Me

Activesheet.Protect UserInterfaceOnly:=True, Password:= "Your Password"

Answer 2

1

Answer 3

Excel find feature works just as fast, VBA is required as part of a complicated VBA script.

Answer 4

You can't edit locked cells. And a spreadsheet isn't a database.



If you want them to be able to see existing data and enter new data, use a database. (Being able to edit data, but not being able to change the data - that seems to be what you're saying - is a self-contradiction.)

Answer 5

Protect your sheet. Then when you want to allow the editing of a cell...try this in your code::



'Next Line allows the editing of cell A1 ONLY. You can obtain the address of any cell you want programatically.

ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range("A1")



'Your code

'for editing

'goes here



'Next line prevents further editing by user

ActiveSheet.Protection.AllowEditRanges(1).Delete





EDIT

Stupid yahoo is modifying my code..wtf?

Put This: ActiveSheet.Protection.

and This: AllowEditRanges.Add Title:="Range1", Range:=Range("A1")



together like this: Protection.AllowEditRanges