| |
エクセル2010で次のようにREFEDITとTEXTBOXからリストボックスへデータを入力・表示しています。
リストボックス内のデータをREFEDITとTEXTBOXに戻し、編集し、それをまたリストボックスに戻したいのです。
戻し方を教えてください。
Private Sub CommandButton1_Click() '追加
Dim C As Shape, A() As String, I As Integer
On Error Resume Next
If Range(RefEdit1.Value) Is Nothing Then Exit Sub
If Range(RefEdit2.Value) Is Nothing Then Exit Sub
If Range(RefEdit1.Value).Cells.Count > 1 Then MsgBox " ": Exit Sub
If Range(RefEdit1.Value).Parent.Name <> ActiveSheet.Name Then MsgBox " ": Exit Sub
If Range(RefEdit2.Value).Areas.Count > 1 Then MsgBox " ": Exit Sub
On Error GoTo 0
ListBox1.AddItem TextBox1.Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Range(RefEdit1.Value).Address
ListBox1.List(ListBox1.ListCount - 1, 2) = RefEdit2.Value
For Each C In ActiveSheet.Shapes
If C.Name = "aaaaa" Then C.Delete
Next C
For I = 0 To ListBox1.ListCount - 1
ReDim Preserve A(I) As String
A(I) = Join(Array(ListBox1.List(I, 1), ListBox1.List(I, 2), ListBox1.List(I, 0)), ",")
Next I
Set C = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 0, 0)
C.Name = "aaaaa"
If Application.version <= 11 Then
For I = 1 To Len(Join(A, vbCr)) Step 255
C.TextFrame.Characters(I, 255).Text = Mid(Join(A, vbCr), I, 255)
Next I
Else
C.TextFrame.Characters.Text = Join(A, vbCr)
End If
C.Visible = False
RefEdit1.Value = ""
RefEdit2.Value = ""
TextBox1.Value = ""
End Sub
Private Sub CommandButton2_Click() '削除
Dim C As Shape, A() As String, I As Integer
If ListBox1.ListIndex = -1 Then Exit Sub
ListBox1.RemoveItem ListBox1.ListIndex
For Each C In ActiveSheet.Shapes
If C.Name = "aaaaa" Then C.Delete
Next C
For I = 0 To ListBox1.ListCount - 1
ReDim Preserve A(I) As String
A(I) = Join(Array(ListBox1.List(I, 1), ListBox1.List(I, 2), ListBox1.List(I, 0)), ",")
Next I
Set C = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 0, 0)
C.Name = "aaaaa"
If Application.version <= 11 Then
For I = 1 To Len(Join(A, vbCr)) Step 255
C.TextFrame.Characters(I, 255).Text = Mid(Join(A, vbCr), I, 255)
Next I
Else
C.TextFrame.Characters.Text = Join(A, vbCr)
End If
C.Visible = False
End Sub
Private Sub CommandButton3_Click() '編集
？？？？？？？？？ この部分の記述がわかりません ？？？？？？？？
Command Button1でListBoxにデータを追加
Command Button2でListBoxのデータを削除しています
Command Button3でListBoxのデータをRefEditあるいはTextBoxに戻し、
編集したものをまたListBoxに元の位置で表示し直したいのです。
データは１行に３個並んでいます
？？？？？？？
End Sub
Private Sub UserForm_Initialize()
Dim I As Integer, C As Shape, A As String, R1 As Variant, R2 As Variant
For Each C In ActiveSheet.Shapes
If C.Name = "aaaaa" Then
If Application.version <= 11 Then
For I = 1 To C.TextFrame.Characters.Count Step 255
A = A & C.TextFrame.Characters(I, 255).Text
Next I
Else
A = C.TextFrame.Characters.Text
End If
Exit For
End If
Next C
If A <> "" Then
R1 = Split(Replace(A, vbCr, vbLf), vbLf)
On Error Resume Next
For I = 0 To UBound(R1)
R2 = Split(R1(I), ",")
ListBox1.AddItem R2(2)
ListBox1.List(ListBox1.ListCount - 1, 1) = R2(0)
ListBox1.List(ListBox1.ListCount - 1, 2) = R2(1)
Next I
End If
A = ""
End Sub
|
|
acch
- 14/5/26(月) 20:54 -
