Listbox Userform VBA单元格中的大量数据 [英] Listbox Userform VBA To much data in a cell

问题描述

我在Excel 2007中的用户窗体中的列表框有一些问题。我的工作表中的某些单元格在1个单元格中包含超过10行（使用ALT ENTER的数据）。现在..我试图找到一种方式，方便和清洁显示数据。我不想将列宽更改为1000，但我想使用鼠标在工具箱上显示所有单元格数据。有谁在这些事情中经历过吗？或者另一个想法？

提前感谢

解决方案

strong>使用鼠标悬停可以做到这一点，但复杂的我认为。在这里，我有另一个简单的想法：在双击列表框中，将显示具有所选列表项数据的多行文本框。此文本框与列表框具有相同的位置和大小。在用户表单上单击文本框隐藏。这里的示例代码，要测试它，你需要的表单与列表框名为ListBox1：

  Option Explicit 
 
 Public ListItemInfo As Control 
 
 Private Sub UserForm_Initialize（）
设置ListItemInfo = Me.Controls.Add（Forms.TextBox.1，ListItemInfo，False）
与Me.ListItemInfo 
 .Top = Me.ListBox1.Top 
 .Left = Me.ListBox1.Left 
 .Width = Me.ListBox1.Width 
 .Height = Me.ListBox1 .Height 
 .MultiLine = True 
 End with 
 End Sub 
 
 Private Sub ListBox1_Change（）
 Me.ListItemInfo.text = GetSelectedItemsText 
 End Sub 
 
 Private Sub ListBox1_DblClick（ByVal取消为MSForms.ReturnBoolean）
 SwitchListItemInfo 
 End Sub 
 
 Private Sub UserForm_Click（）
 SwitchListItemInfo 
 End Sub 
 
私有函数GetSelectedItemsText（）As String 
 Dim text As String 
 Dim i As Integer 
 For i = 0 To Me.ListBox1。李氏tCount  -  1 
如果Me.ListBox1.Selected（i）然后
 text = text& Me.ListBox1.List（i）& vbNewLine 
 End If 
 Next i 
 GetSelectedItemsText = text 
结束函数
 
 Private Sub SwitchListItemInfo（）
如果Me.ListItemInfo.text = Then Exit Sub 
 Me.ListItemInfo.Visible = Not Me.ListItemInfo.Visible 
 Me.ListBox1.Visible = Not Me.ListBox1.Visible 
 End Sub 
  

I have a little problem with my listbox in my userform in Excel 2007. Some cells in my worksheet contains more than 10 rows in 1 cell (data with ALT ENTER). Now.. im trying to find a way to easy and clean display the data. I don't want to change the column width to 1000 but i'd like to use a mouse over toolbox to show all the cell data. Anyone experienced in those things? Or maybe another idea?

Thanks in advance!

解决方案

With mouse over would be possible to do it but it is complicated i think. Here i have another simpler idea: on double click in list box a multi line text box with selected list item(s) data will be shown. This text box has the same position and size as list box. On user form click text box hides. Here sample code, to test it you need form with list box named "ListBox1":

Option Explicit

Public ListItemInfo As Control

Private Sub UserForm_Initialize()
    Set ListItemInfo = Me.Controls.Add("Forms.TextBox.1", "ListItemInfo", False)
    With Me.ListItemInfo
        .Top = Me.ListBox1.Top
        .Left = Me.ListBox1.Left
        .Width = Me.ListBox1.Width
        .Height = Me.ListBox1.Height
        .MultiLine = True
    End With
End Sub

Private Sub ListBox1_Change()
    Me.ListItemInfo.text = GetSelectedItemsText
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    SwitchListItemInfo
End Sub

Private Sub UserForm_Click()
    SwitchListItemInfo
End Sub

Private Function GetSelectedItemsText() As String
    Dim text As String
    Dim i As Integer
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            text = text & Me.ListBox1.List(i) & vbNewLine
        End If
    Next i
    GetSelectedItemsText = text
End Function

Private Sub SwitchListItemInfo()
    If Me.ListItemInfo.text = "" Then Exit Sub
    Me.ListItemInfo.Visible = Not Me.ListItemInfo.Visible
    Me.ListBox1.Visible = Not Me.ListBox1.Visible
End Sub

这篇关于Listbox Userform VBA单元格中的大量数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！