Tony
02-14-2013, 04:22 AM
Hi everyone.
Im using the code below to change a row in a listbox. As i'm using cellformatting i'm using the Application.Transpose(var) what is working fine in excel 2010 and showing when putting in 3% changing to 3.0% automaticly. When i'm only using = var it put it in as a text and not as a value.
But when i test the code below in excel 2007 it takes the first value from the row and copy's it in the other 6 columns in the rows.. when can be wrong?
Option Explicit
Private Sub MenuButton_Click()
Unload Me
Menu.Show
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim x As Long
Dim k As Long
Dim j As Long
Dim rng As Range
Set rng = Sheets("INPUT WV LISTBOX").Range("A8:H282") '<--If you change the Range
Rx1 = 8 '<----------------------------------------------------------------------You Must Change this
With ListBox1
.Clear
For k = 1 To rng.Rows.Count 'rows
.AddItem
For j = 1 To rng.Columns.Count
.List(k - 1, j - 1) = rng.Cells(k, j).Text 'columns
Next j, k
End With
For j = 1 To 8
Me.Controls("TextBox" & j).Text = ""
Next j
End Sub
Private Sub CommandButton2_Click()
Dim var(3 To 8) As String
Dim j As Long
Dim x As Long
If Me.ListBox1.ListIndex = -1 Then
MsgBox "No Data Selected"
Exit Sub
End If
If Me.TextBox1.Value = "" Then Exit Sub
Rx1 = Rx1 + Me.ListBox1.ListIndex
With Sheets("INPUT WV LISTBOX")
For j = 3 To 8
var(j) = Me.Controls("TextBox" & j).Text
Next j
.Cells(Rx1, 3).Resize(, 6).Value = Application.Transpose(var)
End With
Call UserForm_Initialize
End Sub
Private Sub ListBox1_Click()
Dim k As Long
Dim j As Long
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "TextBox" Then
k = Me.ListBox1.ListIndex
For j = 1 To 8
Me.Controls("TextBox" & j).Text = Me.ListBox1.List(k, j - 1)
Next j
End If
Next Ctrl
If Me.TextBox1.Value = "" Then
MsgBox "Can't Select this Row"
Exit Sub
End If
End Sub
Im using the code below to change a row in a listbox. As i'm using cellformatting i'm using the Application.Transpose(var) what is working fine in excel 2010 and showing when putting in 3% changing to 3.0% automaticly. When i'm only using = var it put it in as a text and not as a value.
But when i test the code below in excel 2007 it takes the first value from the row and copy's it in the other 6 columns in the rows.. when can be wrong?
Option Explicit
Private Sub MenuButton_Click()
Unload Me
Menu.Show
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim x As Long
Dim k As Long
Dim j As Long
Dim rng As Range
Set rng = Sheets("INPUT WV LISTBOX").Range("A8:H282") '<--If you change the Range
Rx1 = 8 '<----------------------------------------------------------------------You Must Change this
With ListBox1
.Clear
For k = 1 To rng.Rows.Count 'rows
.AddItem
For j = 1 To rng.Columns.Count
.List(k - 1, j - 1) = rng.Cells(k, j).Text 'columns
Next j, k
End With
For j = 1 To 8
Me.Controls("TextBox" & j).Text = ""
Next j
End Sub
Private Sub CommandButton2_Click()
Dim var(3 To 8) As String
Dim j As Long
Dim x As Long
If Me.ListBox1.ListIndex = -1 Then
MsgBox "No Data Selected"
Exit Sub
End If
If Me.TextBox1.Value = "" Then Exit Sub
Rx1 = Rx1 + Me.ListBox1.ListIndex
With Sheets("INPUT WV LISTBOX")
For j = 3 To 8
var(j) = Me.Controls("TextBox" & j).Text
Next j
.Cells(Rx1, 3).Resize(, 6).Value = Application.Transpose(var)
End With
Call UserForm_Initialize
End Sub
Private Sub ListBox1_Click()
Dim k As Long
Dim j As Long
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "TextBox" Then
k = Me.ListBox1.ListIndex
For j = 1 To 8
Me.Controls("TextBox" & j).Text = Me.ListBox1.List(k, j - 1)
Next j
End If
Next Ctrl
If Me.TextBox1.Value = "" Then
MsgBox "Can't Select this Row"
Exit Sub
End If
End Sub