Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Edit Save ListBox Records
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Edit Save ListBox Records
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 doesnt 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

--
Message posted via http://www.officekb.com

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Edit Save ListBox Records
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...

ViViC wrote:

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

--
Message posted via http://www.officekb.com

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Edit Save ListBox Records
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

Dave Peterson wrote:

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

ViViC wrote:

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

--
Message posted via http://www.officekb.com

--

Dave Peterson

--

Dave Peterson
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Edit Save ListBox Records
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


Dave Peterson wrote:
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


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200902/1

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrive Records in UserForm and Edit - from shahzad [email protected] Excel Programming 2 June 28th 07 09:13 PM
Populate ListBox with Filtered Records mikeg710 Excel Programming 2 April 28th 07 03:12 AM
ListBox edit Geoff Excel Programming 3 October 26th 06 06:58 PM
Edit listbox Items vandaley Excel Programming 0 October 8th 06 10:26 AM
Edit Add Delete Listbox Records [email protected] Excel Programming 17 October 10th 05 11:58 PM


All times are GMT +1. The time now is 07:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement