回答編集履歴

4

コード修正

2018/05/02 07:53

投稿

hatena19
hatena19

スコア33620

test CHANGED
@@ -26,7 +26,7 @@
26
26
 
27
27
  ElseIf EndR.Row = ActiveCell.Row Then
28
28
 
29
- Set r = ActiveCell
29
+ Set r = Cells(ActiveCell.Row, 1)
30
30
 
31
31
  Else
32
32
 
@@ -148,7 +148,7 @@
148
148
 
149
149
  ElseIf EndR.Row = ActiveCell.Row Then
150
150
 
151
- Set r = ActiveCell
151
+ Set r = Cells(ActiveCell.Row, 1)
152
152
 
153
153
  Else
154
154
 

3

コード修正

2018/05/02 07:53

投稿

hatena19
hatena19

スコア33620

test CHANGED
@@ -74,11 +74,15 @@
74
74
 
75
75
  ```vba
76
76
 
77
+ Option Explicit
78
+
79
+
80
+
77
81
  Private Sub CB_Change()
78
82
 
79
83
  With Me.ActiveControl
80
84
 
81
- Cells(.Tag, 4).Value = .Value
85
+ If .Tag <> "" Then Cells(.Tag, 4).Value = .Value
82
86
 
83
87
  End With
84
88
 
@@ -126,8 +130,6 @@
126
130
 
127
131
 
128
132
 
129
-
130
-
131
133
  Private Sub CommandButton1_Click()
132
134
 
133
135
  Dim r As Range, c As Range, n As Long, EndR As Range
@@ -172,13 +174,13 @@
172
174
 
173
175
  For n = n + 1 To 5
174
176
 
175
- Me("TextBox" & n) = ""
177
+ Me("TextBox" & n).Value = ""
178
+
176
-
179
+ Me("ComboBox" & n).Tag = ""
180
+
177
- Next
181
+ Next
178
-
182
+
179
- End Sub
183
+ End Sub
180
-
181
-
182
184
 
183
185
 
184
186
 

2

コードの追記

2018/05/02 05:48

投稿

hatena19
hatena19

スコア33620

test CHANGED
@@ -57,3 +57,149 @@
57
57
  End Sub
58
58
 
59
59
  ```
60
+
61
+
62
+
63
+ 追記
64
+
65
+ ---
66
+
67
+ > 最終目的は、テキストBOXの横にあるドロップダウンリストから○×を選ぶと、対応する行のD列にその値が入るようにすることです。
68
+
69
+
70
+
71
+ この仕様にも対応するようにしてみました。
72
+
73
+
74
+
75
+ ```vba
76
+
77
+ Private Sub CB_Change()
78
+
79
+ With Me.ActiveControl
80
+
81
+ Cells(.Tag, 4).Value = .Value
82
+
83
+ End With
84
+
85
+ End Sub
86
+
87
+
88
+
89
+ Private Sub ComboBox1_Change()
90
+
91
+ CB_Change
92
+
93
+ End Sub
94
+
95
+
96
+
97
+ Private Sub ComboBox2_Change()
98
+
99
+ CB_Change
100
+
101
+ End Sub
102
+
103
+
104
+
105
+ Private Sub ComboBox3_Change()
106
+
107
+ CB_Change
108
+
109
+ End Sub
110
+
111
+
112
+
113
+ Private Sub ComboBox4_Change()
114
+
115
+ CB_Change
116
+
117
+ End Sub
118
+
119
+
120
+
121
+ Private Sub ComboBox5_Change()
122
+
123
+ CB_Change
124
+
125
+ End Sub
126
+
127
+
128
+
129
+
130
+
131
+ Private Sub CommandButton1_Click()
132
+
133
+ Dim r As Range, c As Range, n As Long, EndR As Range
134
+
135
+
136
+
137
+ Set EndR = Cells(Rows.Count, 1).End(xlUp)
138
+
139
+
140
+
141
+ If EndR.Row < ActiveCell.Row Then
142
+
143
+ MsgBox "データのあるセルを選択してください。"
144
+
145
+ Exit Sub
146
+
147
+ ElseIf EndR.Row = ActiveCell.Row Then
148
+
149
+ Set r = ActiveCell
150
+
151
+ Else
152
+
153
+ Set r = Range(Cells(ActiveCell.Row, 1), EndR).SpecialCells(xlCellTypeVisible)
154
+
155
+ End If
156
+
157
+
158
+
159
+ For Each c In r
160
+
161
+ n = n + 1
162
+
163
+ Me("TextBox" & n).Value = c.Value & c.Offset(, 1).Value
164
+
165
+ Me("ComboBox" & n).Tag = c.Row 'コンボボックスのTagに行インデックスを格納
166
+
167
+ If n = 5 Then Exit For
168
+
169
+ Next
170
+
171
+
172
+
173
+ For n = n + 1 To 5
174
+
175
+ Me("TextBox" & n) = ""
176
+
177
+ Next
178
+
179
+ End Sub
180
+
181
+
182
+
183
+
184
+
185
+ Private Sub UserForm_Initialize()
186
+
187
+ Dim i As Long
188
+
189
+
190
+
191
+ For i = 1 To 5
192
+
193
+ With Me("ComboBox" & i)
194
+
195
+ .AddItem ("○")
196
+
197
+ .AddItem ("×")
198
+
199
+ End With
200
+
201
+ Next
202
+
203
+ End Sub
204
+
205
+ ```

1

コードの修正

2018/05/02 05:10

投稿

hatena19
hatena19

スコア33620

test CHANGED
@@ -10,15 +10,29 @@
10
10
 
11
11
  Private Sub CommandButton1_Click()
12
12
 
13
- Dim r As Range, c As Range, n As Long
13
+ Dim r As Range, c As Range, n As Long, EndR As Range
14
14
 
15
15
 
16
16
 
17
- Set r = Range(Cells(ActiveCell.Row, 1), Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
17
+ Set EndR = Cells(Rows.Count, 1).End(xlUp)
18
18
 
19
19
 
20
20
 
21
+ If EndR.Row < ActiveCell.Row Then
22
+
23
+ MsgBox "データのあるセルを選択してください。"
24
+
25
+ Exit Sub
26
+
27
+ ElseIf EndR.Row = ActiveCell.Row Then
28
+
29
+ Set r = ActiveCell
30
+
31
+ Else
32
+
21
- Debug.Print Me("TextBox1") = ""; IsNull(Me("TextBox1"))
33
+ Set r = Range(Cells(ActiveCell.Row, 1), EndR).SpecialCells(xlCellTypeVisible)
34
+
35
+ End If
22
36
 
23
37
 
24
38
 
@@ -32,7 +46,7 @@
32
46
 
33
47
  Next
34
48
 
35
-
49
+
36
50
 
37
51
  For n = n + 1 To 5
38
52