Edit Save ListBox Records

[ViViC]

Hi everbody,

The code below was provided by Dave Paterson in 2005 on this site and it
works great. But I am having one slight problem with it. When it copies the
data back into the sheet the formatting is changed to text which is all wrong.


My columns are formatted as follows

1 – Date (no problem)
2 – General (no problem)
3 – Number (“0.00â€)
4 – General (no problem)
5 – General (no problem)
6 - Number (“0â€)
7 – Number (“0â€)
8 - Number (“0â€)
9 – Number (“0â€)

I have tried changing the format after saving the record by running a macro
that resets the cells format but it doesn’t change the data to the format, it
still shows the error checking option.

Marco code

Columns("C:C").Select
Selection.NumberFormat = "0.00"
Columns("F:I").Select
Selection.NumberFormat = "0"

Is there any way I can get this to fix the format in the sheet after saving-

Any help will be greatly appreciated as this is the last problem to fix and I
can handover the spreadsheet to staff for their use.

Private Sub UserForm_Initialize()
Dim iCtr As Long

Me.Label28.Caption = Worksheets("Trans Types & Sources").Range("J3")
Me.lblDate.Caption = Format(Date, "dddd, d mmmm, yyyy")
With Application
Top = .Top
Left = .Left
Height = .Height
Width = .Width
End With
Me.ListBox1.ColumnCount = 11
Me.ListBox1.ColumnWidths = "50;55;55;90;425;50;0;0;0;0;0;"
Me.ListBox1.RowSource = ""
With Worksheets("DataEntry")
' If .Cells(1).Value = "No Entries" Then
' .Rows(1).Delete
' End If
Set myInputRange = .Range("A1:K" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)
Me.ListBox1.RowSource = myInputRange.Address(external:=True)
End With
For iCtr = 1 To 11
Me.Controls("textbox" & iCtr).Enabled = False
Next iCtr
Me.cmdCancel.Caption = "Exit"
Me.ListBox1.Enabled = True
Me.ListBox1.ListIndex = 0 'prime the pump
Me.cmdSave.Enabled = False
Me.cmdCancel.Enabled = True
Me.cmdEdit.Enabled = True
End Sub

Private Sub CmdCancel_Click()
If Me.cmdCancel.Caption = "Exit" Then
Unload Me
Else
Call UserForm_Initialize
End If
End Sub

Private Sub cmdEdit_Click()
Dim iCtr As Long
Dim myRng As Range
Set myRng = Worksheets("DataEntry").Range("A1")
Worksheets("DataEntry").Activate

Application.EnableEvents = False
If Application.CountBlank(myRng) > 0 Then
Application.ScreenUpdating = False
Me.cmdEdit.Enabled = False
MsgboxResult = MsgBox("There are no invoices created", _
vbExclamation, "Shared Services")
Application.ScreenUpdating = True
Exit Sub
Else
For iCtr = 1 To 11
Me.Controls("textbox" & iCtr).Enabled = True
Next iCtr
Me.cmdCancel.Caption = "Cancel Edit"
Me.ListBox1.Enabled = False
Me.cmdSave.Enabled = True
Me.cmdCancel.Enabled = True
Me.cmdEdit.Enabled = False
If myProcessing = "" Then
myProcessing = "Edit"
End If
End If
End Sub

Private Sub cmdSave_Click()
Dim iCtr As Long
Dim DestCell As Range

With myInputRange
Set DestCell = .Cells(1).Offset(Me.ListBox1.ListIndex)
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
Call Macro4
End If
End With
End Sub

 

[Dave Peterson]

This portion:

For iCtr = 1 To Me.ListBox1.ColumnCount
DestCell.Offset(0, iCtr - 1).Value = Me.Controls("textbox" & iCtr)
Next iCtr

does the writing to the worksheet.

You can replace it with:

with DestCell.Offset(0, 0)
.numberformat = "General"
.Value = Me.Controls("textbox" & 1).value
end with

with DestCell.Offset(0, 1)
.numberformat = "General"
.Value = Me.Controls("textbox" & 2).value
end with

with DestCell.Offset(0, 2)
.numberformat = "0.0"
.Value = Me.Controls("textbox" & 3).value
end with

.....and so on...

 

[Dave Peterson]

Ps. You won't want want to use General for dates.

 

[ViViC via OfficeKB.com]

Hi Dave,

Brilliant, many thanks for your knowledge and expertise. A big STAR. I am now
into final testing and can now finish the program on time.

Vic

ViViC

Ps. You won't want want to use General for dates.

with DestCell.Offset(0, 0)
.numberformat = "mm/dd/yyyy"
.Value = cdate(Me.Controls("textbox" & 1).value)
end with

This portion:

[quoted text clipped - 164 lines]

Dave Peterson