Edit Add Delete Listbox Records

[[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

[Bob Phillips]

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
>

[killaV]

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

[Dave Peterson]

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

[killaV]

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

Thanks

[killaV]

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

Thanks

[Sami82]

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

[Dave Peterson]

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

[Sami82]

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

[Dave Peterson]

dim ClaimStartDate as long
claimstartdate = dateserial(cboSDYear,CboCSDMonth,cboCSDDay)

with range("a1")
.value = claimstartdate
.numberformat = "yyyy/mm/dd" 'or whatever you want
end with

=====
By building the string the way you did (with the slashes in the code, you ended
up with a string that excel would interpret according to its rules (and windows
settings).

By making it a real value (datesearial), there isn't any doubt what you want.

===
Ps. Did you look at that calendar control?

Sami82 wrote:
>
> 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
>
> --
> 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

[Sami82]

Hi Dave,

Thanks so much for you help, and also explaining why I'm getting the errors, and not just how to fix them.

The only problem is that these dates need to be used on various occasions, is it possible to change and store the date as dd/mm/yyyy after the dateserial has been completed. I tried the following, but I haven't been able to get it to work:

Dim ClaimStartDateLong As Long
Dim ClaimStartDate As Date

ClaimStartDateLong = DateSerial(cboSDYear, cboCSDMonth, cboCSDDay)
ClaimStartDate = Format(ClaimStartDateLong, "dd/mm/yyyy")

I keep getting the year returned as 2000, even when I put in 2005, other than that everything seems to be fine.

Sami

PS: I did look at the calendar control, but using it for what I'm working on would take too much time for the user.

dim ClaimStartDate as long
claimstartdate = dateserial(cboSDYear,CboCSDMonth,cboCSDDay)

with range("a1")
.value = claimstartdate
.numberformat = "yyyy/mm/dd" 'or whatever you want
end with

=====
By building the string the way you did (with the slashes in the code, you ended
up with a string that excel would interpret according to its rules (and windows
settings).

By making it a real value (datesearial), there isn't any doubt what you want.

===
Ps. Did you look at that calendar control?

Sami82 wrote:
>
> 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
>
> --
> 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

[Sami82]

As another part of the code, I need to have another variable which transforms the date into a Sunday. So far I have been doing startdate + 6 or the like. But I was wondering if there is any code which can guarantee it will change to a sunday.

Thanks

[Dave Peterson]

Don't worry about the format of the date in VBA.

This would, er, should work ok:

Dim ClaimStartDate As Date
ClaimStartDate = DateSerial(cboSDYear, cboCSDMonth, cboCSDDay)

As soon as you use Format(), it'll be a string--VBA will try to coerce back to a
date and you'll have that same trouble.

For example:

Dim ClaimStartDate As Date
ClaimStartDate = DateSerial(2005, 11, 1)
Debug.Print ClaimStartDate

ClaimStartDate = Format(ClaimStartDate, "dd/mm/yyyy")
Debug.Print ClaimStartDate

Returned this:
11/01/2005
01/11/2005

Both were still dates, but the second wasn't what you want.




Sami82 wrote:
>
> Hi Dave,
>
> Thanks so much for you help, and also explaining why I'm getting the
> errors, and not just how to fix them.
>
> The only problem is that these dates need to be used on various
> occasions, is it possible to change and store the date as dd/mm/yyyy
> after the dateserial has been completed. I tried the following, but I
> haven't been able to get it to work:
>
> Dim ClaimStartDateLong As Long
> Dim ClaimStartDate As Date
>
> ClaimStartDateLong = DateSerial(cboSDYear, cboCSDMonth, cboCSDDay)
> ClaimStartDate = Format(ClaimStartDateLong, "dd/mm/yyyy")
>
> I keep getting the year returned as 2000, even when I put in 2005,
> other than that everything seems to be fine.
>
> Sami
>
> PS: I did look at the calendar control, but using it for what I'm
> working on would take too much time for the user.
>
> Dave Peterson Wrote:
> > dim ClaimStartDate as long
> > claimstartdate = dateserial(cboSDYear,CboCSDMonth,cboCSDDay)
> >
> > with range("a1")
> > .value = claimstartdate
> > .numberformat = "yyyy/mm/dd" 'or whatever you want
> > end with
> >
> > =====
> > By building the string the way you did (with the slashes in the code,
> > you ended
> > up with a string that excel would interpret according to its rules (and
> > windows
> > settings).
> >
> > By making it a real value (datesearial), there isn't any doubt what you
> > want.
> >
> > ===
> > Ps. Did you look at that calendar control?
> >
> > Sami82 wrote:
> > >
> > > 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
> > >
> > > --
> > > 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
>
> --
> 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

[Dave Peterson]

Which Sunday?

Monday-Saturday goes to next Sunday. Sunday stays that same Sunday???

Option Explicit
Sub testme01()
Dim ClaimStartDate As Date
Dim ClaimStartSunday As Date

ClaimStartDate = DateSerial(2005, 10, 10)

If Weekday(ClaimStartDate) = vbSunday Then
ClaimStartSunday = ClaimStartDate
Else
ClaimStartSunday = ClaimStartDate + 7 - Weekday(ClaimStartDate) + 1
End If

Debug.Print Format(ClaimStartSunday, "mm/dd/yyyy dddd")

End Sub

The format statement is just so I could check the code.

Sami82 wrote:
>
> As another part of the code, I need to have another variable which
> transforms the date into a Sunday. So far I have been doing startdate +
> 6 or the like. But I was wondering if there is any code which can
> guarantee it will change to a sunday.
>
> Thanks
>
> --
> 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

[Sami82]

All working....
(For this part anyway)
Thank you so much for your help!

[Sami82]

Hi again,

Ok, I've kept the dates in the dateserial formatting, and now i'm trying to find out how many weeks there are between the start and end date. So i have

Weeks = enddate - startdate / 7

the result is then looked at in a select case,
case "1"
do this...
case "2"
ra ra ra...

The weeks need to be a whole number without decimals. I have Dim weeks as integer, but I am still getting decimals, and it is bypassing my case test.

How can I turn it into a whole number?

[Dave Peterson]

First, watch your parentheses:

Dim Weeks as Long
Weeks = int((enddate - startdate) / 7)

would be one way.

And since this is a number, you don't need the double quotes:

case is = "1"
would be:
case is = 1



Sami82 wrote:
>
> Hi again,
>
> Ok, I've kept the dates in the dateserial formatting, and now i'm
> trying to find out how many weeks there are between the start and end
> date. So i have
>
> Weeks = enddate - startdate / 7
>
> the result is then looked at in a select case,
> case "1"
> do this...
> case "2"
> ra ra ra...
>
> The weeks need to be a whole number without decimals. I have Dim weeks
> as integer, but I am still getting decimals, and it is bypassing my
> case test.
>
> How can I turn it into a whole number?
>
> --
> 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

[Sami82]

Worked!
Thank you so much.
Sam