Formiram UserForm za jednu Excel bazu. "Napravio" sam modul, prema instrukcijama, za popunu jedne od tabela - i to funkcioniše... ali mi mi VBA "pravi" tabelu na Sheet-u na kome se nalazi dugme za poketanje forme.
Gde, u VBA kod-u, treba navesti putanju do ciljane tabele - odnosno "List"-a?
UserForm kod:
Code:
Option Explicit
Private Sub CommandButton1_Click()
EditAdd
End Sub
Private Sub CommandButton2_Click()
ClearForm
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub TextBox1_Change()
GetData
End Sub
Private Sub UserForm_Initialize()
TextBox1.SetFocus
End Sub
Modul kod:
Code:
Dim id As Integer, i As Integer, j As Integer, flag As Boolean
Sub GetData()
If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
Do While Cells(i + 1, 1).Value <> ""
If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 10
UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
Next j
End If
i = i + 1
Loop
If flag = False Then
For j = 2 To 10
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End If
Else
ClearForm
End If
End Sub
Sub ClearForm()
For j = 1 To 10
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End Sub
Sub EditAdd()
Dim emptyRow As Long
If UserForm1.TextBox1.Value <> "" Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Do While Cells(i + 1, 1).Value <> ""
If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 10
Cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
End If
i = i + 1
Loop
If flag = False Then
For j = 1 To 10
Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
End If
End If
End Sub
Da li je, možda, pravilo da Forma (kao i dugme koje ga pookreće) bude na istom "Sheet"-u na kome je i tabela?
[Ovu poruku je menjao pcpopovic dana 03.04.2016. u 11:11 GMT+1]
[Ovu poruku je menjao pcpopovic dana 03.04.2016. u 11:12 GMT+1]