Userform Listbox Edit Rows Within Sheet

[pure vito]

Hi All,

I am trying to fit VBA code I found here to fit my userform, and as you can image I have hit a snag, hopefully the pictures below will help you better understand the issue,

Clicking a row on the list box populates all the text boxes within the userform the goal is to be able to update the sheet "People" with any changes made to these textboxes,

So I would select a row I want to edit make a change to the textbox and have it update the sheet so the code I am using (Private Sub UpdateRow_Click) is working to a degree it is transferring data but to the wrong row and also to the wrong columns,

But it is also giving me a runtime error "70" permission denied,

I have exhausted my knowledge in trying to make this work I can not see or understand the issues within the code,

The next issue I have is that a number of columns G:K have formula so transferring data from the userform textbox's removes this, the data range populating the textboxes is C:AM so the columns with formula fall into this and I can't really change that without making many changes to my document,

Hope this makes sense and I appreciate any help please

I may have other issues as I have only worked on 2 functions of this code aligning it with my userform

Overall the goal is to be able to,

Update (edit) row's
Delete row's
Add row's

Oh and possibly search the Listbox

Private Sub UserForm_Activate()

Dim sh As Worksheet
Set sh = Sheets("People")
Dim iRow As Long
iRow = sh.Range("C" & Rows.Count).End(xlUp).Row
'to populate listbox  from Database sheet
   With Me.ListBox1
       .ColumnCount = 37
       .ColumnHeads = True
       .ColumnWidths = "150,100,80,80,150,0,0,0,0,80,80,80,120,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0"
       .RowSource = "People!C2:AM" & iRow
   End With

'loads userform, selects 1st row of data
'Lastrow = Sheets("People").Cells(Rows.Count, "C").End(xlUp).Row
'Nameinput.ListBox1.ColumnCount = 37
'Nameinput.ListBox1.ColumnWidths = "1cm"
'Nameinput.ListBox1.List = Sheets("People").Range("C2:AM" & Lastrow).Value
'Nameinput.ListBox1.ListIndex = 0
End Sub

Private Sub ListBox1_Click()
Dim i As Integer
'Loads textboxes with selected listbox values
For i = 1 To 37
Nameinput.Controls("TextBox" & i).Value = Nameinput.ListBox1.List(, i - 1)
Next i
End Sub

Private Sub CommandButton1_Click()
'loads textboxes with 1-7 values????
Dim i As Long
For i = 1 To 37
Nameinput.Controls("TextBox" & i).Value = i
Next i
Nameinput.ListBox1.ListIndex = -1
End Sub

Private Sub UpdateRow_Click()
'Loads People with textbox values
'People row update based on listbox selection
'updates listbox. Selects updated row
Dim i As Long, Temp As Integer
If Nameinput.ListBox1.ListIndex < 0 Then
MsgBox "You must select some row in the List Box"
Exit Sub
End If
Lastrow = Sheets("People").Cells(Rows.Count, "C").End(xlUp).Row + 1
For i = 3 To 37
Sheets("People").Cells(Nameinput.ListBox1.ListIndex + 1, i).Value = _
       Nameinput.Controls("TextBox" & i).Value
Next i
Temp = Nameinput.ListBox1.ListIndex
Nameinput.ListBox1.List = Sheets("People").Range("C1:AM" & Lastrow).Value
Nameinput.ListBox1.ListIndex = Temp
End Sub

Private Sub DeleteRow_Click()
'Delete People data based on listbox selection
'updates listbox. Selects same listbox location that was removed
Dim Temp As Integer
If Nameinput.ListBox1.ListIndex < 0 Then
MsgBox "You must select some row in the List Box"
Exit Sub
End If
Temp = Nameinput.ListBox1.ListIndex
Sheets("People").Rows(Nameinput.ListBox1.ListIndex + 1).EntireRow.Delete
Lastrow = Sheets("People").Cells(Rows.Count, "A").End(xlUp).Row + 1
Nameinput.ListBox1.List = Sheets("People").Range("A1:G" & Lastrow).Value
'listbox selection
If Temp + 1 = Lastrow Then
Nameinput.ListBox1.ListIndex = Temp - 1
Else
Nameinput.ListBox1.ListIndex = Temp
End If
End Sub

Private Sub AddRow_Click()
'Adds Row bottom of data on People
'updates listbox and selects new addition
Dim i As Long
Lastrow = Sheets("People").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To 7
Sheets("People").Cells(Lastrow + 1, i).Value = Nameinput.Controls("TextBox" & i).Value
Next i
Nameinput.ListBox1.List = Sheets("People").Range("A1:G" & Lastrow + 1).Value
Nameinput.ListBox1.ListIndex = Lastrow
End Sub

 

[pure vito]

Hi all, I’d really appreciate any help with this if anyone has the time please