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 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, DataForm..?

--

HTH

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


wrote in message
ps.com...
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 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).Enti reRow.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

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

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 a
having one slight problem with it. When it copies the data back int
the sheet the formatting is all wrong and it is confusing when date
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 th
sheet - I have tried stepping through the code, but i can't work ou
where to put the format changes.

THANKYOU :

--
Sami8
-----------------------------------------------------------------------
Sami82's Profile: http://www.excelforum.com/member.php...fo&userid=2711
View this thread: http://www.excelforum.com/showthread.php?threadid=33594

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


--
Sami82
------------------------------------------------------------------------
Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111
View this thread: http://www.excelforum.com/showthread...hreadid=335941

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

Hi Dave,

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

The only problem is that these dates need to be used on variou
occasions, is it possible to change and store the date as dd/mm/yyy
after the dateserial has been completed. I tried the following, but
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'
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 (an
windows
settings).

By making it a real value (datesearial), there isn't any doubt what yo
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 whe
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 Peterso

--
Sami8
-----------------------------------------------------------------------
Sami82's Profile: http://www.excelforum.com/member.php...fo&userid=2711
View this thread: http://www.excelforum.com/showthread.php?threadid=33594

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

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

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

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


--
Sami82
------------------------------------------------------------------------
Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111
View this thread: http://www.excelforum.com/showthread...hreadid=335941

Hi again,

Ok, I've kept the dates in the dateserial formatting, and now i'
trying to find out how many weeks there are between the start and en
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 week
as integer, but I am still getting decimals, and it is bypassing m
case test.

How can I turn it into a whole number

--
Sami8
-----------------------------------------------------------------------
Sami82's Profile: http://www.excelforum.com/member.php...fo&userid=2711
View this thread: http://www.excelforum.com/showthread.php?threadid=33594

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

Worked!
Thank you so much.
Sam


--
Sami82
------------------------------------------------------------------------
Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111
View this thread: http://www.excelforum.com/showthread...hreadid=335941