Edit Add Delete Listbox Records

Im looking for an example of something along this line.

On my userform1 I have a listbox, an edit button, a new button, and a
delete button.

I've gotten a list box to display A1:D8 which has 4 columns.

But I dont know were to even start as far as the edit button.
I would like it so that when I select the row in the listbox and press
edit, a new userform pops up, with 4 textbox displaying the row. Then
you can edit it, and click 'save changes' and it updates that row in
the list.

If anyone could either email me at [email protected] or
post an example that could help me. I've spent hours seaching for
something like this but came up with nothing.

Thanks for the help
Mike

Why not just use the built-in form, Data>Form..?

--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> Im looking for an example of something along this line.
>
> On my userform1 I have a listbox, an edit button, a new button, and a
> delete button.
>
> I've gotten a list box to display A1:D8 which has 4 columns.
>
> But I dont know were to even start as far as the edit button.
> I would like it so that when I select the row in the listbox and press
> edit, a new userform pops up, with 4 textbox displaying the row. Then
> you can edit it, and click 'save changes' and it updates that row in
> the list.
>
> If anyone could either email me at [email protected] or
> post an example that could help me. I've spent hours seaching for
> something like this but came up with nothing.
>
> Thanks for the help
> Mike
>

I know i could user the excel data form or the Jwalk dataform but was
wondering how to do this using vba

I put 5 buttons on a userform:

CmdEdit
CmdNew
CmdDelete
CmdSave
CmdCancel

I also put a listbox (listbox1).

and 4 textboxes on this form--not a new form.
textbox1 through textbox4

This seemed to work ok under light testing.

Option Explicit
Dim myInputRange As Range
Dim myProcessing As String
Dim blkProc As Boolean
Private Sub CmdCancel_Click()
If Me.CmdCancel.Caption = "Cancel Form" Then
Unload Me
Else
'cancel edit
Call UserForm_Initialize
End If
End Sub
Private Sub CmdDelete_Click()
If Me.ListBox1.ListIndex > -1 Then
myInputRange(1).Offset(Me.ListBox1.ListIndex).EntireRow.Delete
Call UserForm_Initialize
If Application.CountA(myInputRange) = 0 Then
Me.CmdSave.Enabled = False
Me.CmdCancel.Enabled = True
Me.CmdNew.Enabled = True
Me.CmdEdit.Enabled = False
Me.CmdDelete.Enabled = False
End If
End If
End Sub
Private Sub cmdEdit_Click()
Dim iCtr As Long

For iCtr = 1 To 4
Me.Controls("textbox" & iCtr).Enabled = True
Next iCtr

Me.CmdCancel.Caption = "Cancel Change"

Me.ListBox1.Enabled = False
Me.CmdSave.Enabled = True
Me.CmdCancel.Enabled = True
Me.CmdNew.Enabled = False
Me.CmdEdit.Enabled = False
Me.CmdDelete.Enabled = False

If myProcessing = "" Then
myProcessing = "Edit"
End If

End Sub
Private Sub CmdNew_Click()

Dim iCtr As Long

For iCtr = 1 To 4
Me.Controls("textbox" & iCtr).Value = ""
Next iCtr

myProcessing = "New"

Call cmdEdit_Click
End Sub

Private Sub CmdSave_Click()

Dim iCtr As Long
Dim DestCell As Range
With myInputRange
If myProcessing = "New" Then
Set DestCell = .Cells(1).Offset(.Rows.Count)
Else
Set DestCell = .Cells(1).Offset(Me.ListBox1.ListIndex)
End If
End With

blkProc = True
For iCtr = 1 To Me.ListBox1.ColumnCount
DestCell.Offset(0, iCtr - 1).Value = Me.Controls("textbox" & iCtr)
Next iCtr
blkProc = False
myProcessing = ""
Call UserForm_Initialize

End Sub
Private Sub ListBox1_Click()
Dim iCtr As Long
If blkProc Then Exit Sub
With Me.ListBox1
If .ListIndex > -1 Then
For iCtr = 1 To .ColumnCount
Me.Controls("textbox" & iCtr).Value _
= .List(.ListIndex, iCtr - 1)
Next iCtr
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

Me.ListBox1.ColumnCount = 4
Me.ListBox1.RowSource = ""
With Worksheets("sheet1")
If .Cells(1).Value = "No Entries" Then
.Rows(1).Delete
End If
Set myInputRange = .Range("a1:D" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)
If Application.CountA(myInputRange) = 0 Then
myInputRange(1).Value = "No Entries"
End If
Me.ListBox1.RowSource = myInputRange.Address(external:=True)
End With

For iCtr = 1 To 4
Me.Controls("textbox" & iCtr).Enabled = False
Next iCtr

Me.CmdCancel.Caption = "Cancel Form"
Me.ListBox1.Enabled = True
Me.ListBox1.ListIndex = 0 'prime the pump
Me.CmdSave.Enabled = False
Me.CmdCancel.Enabled = True
Me.CmdNew.Enabled = True
Me.CmdEdit.Enabled = True
Me.CmdDelete.Enabled = True

End Sub

[email protected] wrote:
>
> Im looking for an example of something along this line.
>
> On my userform1 I have a listbox, an edit button, a new button, and a
> delete button.
>
> I've gotten a list box to display A1:D8 which has 4 columns.
>
> But I dont know were to even start as far as the edit button.
> I would like it so that when I select the row in the listbox and press
> edit, a new userform pops up, with 4 textbox displaying the row. Then
> you can edit it, and click 'save changes' and it updates that row in
> the list.
>
> If anyone could either email me at [email protected] or
> post an example that could help me. I've spent hours seaching for
> something like this but came up with nothing.
>
> Thanks for the help
> Mike

--

Dave Peterson

This is exactly what I needed, and is a huge help

Thanks

Hi Dave,

This code is extremely useful, thank you for posting it. But I am having one slight problem with it. When it copies the data back into the sheet the formatting is all wrong and it is confusing when dates are displayed on the form in text format.

My columns are as follows:

1 - Text
2 - Text
3 - Date (dd/mm/yyyy)
4 - Date (dd/mm/yyyy)
5 - Currency ($#,#00.00)
6 - Currency ($#,#00.00)

Is there any way i can get this to update in the form and also on the sheet - I have tried stepping through the code, but i can't work out where to put the format changes.

THANKYOU

The bad news is that when the user types an ambiguous date into a textbox in a
userform, then when you put it in a cell, excel will do it's best to make it fit
your windows setting.

If I put: 01/02/03
in a textbox and want it to mean "2001 January 03", then I'm gonna have trouble
when I plop it into excel (with my standard USA settings. I'm gonna get
"January 02, 2003".

One way around this is to get the date in an unambiguous way--multiple textboxes
(Year, month, day), spinners, scrollbars and labels???

Or maybe using a calendar control.
Ron de Bruin has some tips/links at:
http://www.rondebruin.nl/calendar.htm

And you can format the cell as currency after you populate the cell. If you're
having trouble with international issues (comma for the decimal symbol???), you
could convert it before you plop it back into that cell.



Sami82 wrote:
>
> Hi Dave,
>
> This code is extremely useful, thank you for posting it. But I am
> having one slight problem with it. When it copies the data back into
> the sheet the formatting is all wrong and it is confusing when dates
> are displayed on the form in text format.
>
> My columns are as follows:
>
> 1 - Text
> 2 - Text
> 3 - Date (dd/mm/yyyy)
> 4 - Date (dd/mm/yyyy)
> 5 - Currency ($#,#00.00)
> 6 - Currency ($#,#00.00)
>
> Is there any way i can get this to update in the form and also on the
> sheet - I have tried stepping through the code, but i can't work out
> where to put the format changes.
>
> THANKYOU
>
> --
> Sami82
> ------------------------------------------------------------------------
> Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111
> View this thread: http://www.excelforum.com/showthread...hreadid=335941

--

Dave Peterson

Hi again,

I have taken on your suggestion of having 3 drop down boxes for the dates, in the order dd (cboCSDDay) mm(cboCSDMonth ) yyyy(cboCSDYear)

ClaimStartDate = cboCSDDay & "/" & cboCSDMonth & "/" & cboCSDYear
MsgBox ClaimStartDate
Range("A1").Value = ClaimStartDate

In the message box the date comes out around the right way, but when it is pasted in the sheet it is around the wrong way again? How can i correct this?

Thank you

This is exactly what I needed, and is a huge help

Thanks