Application.Transpose In Excel 2007 and Excel 2010
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?
Code: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
Reply With Quote
Bookmarks