Link forms

[Ed B. Delen]

can you create me a sample for this"

spreadsheet page:

____________________________________
Firstname MiddleName Surname
data1
data2
data3
data4
and so on


for this list I want User Entryform that when i enter names on my form, data automatically fill-in the list or include in the list.



THanks

pls help.

ed

 

[jindon]

Hi
Assuming:
userform1 has: 3 textboxes and 1 commandbutton
textbox1 corresponds First name : Sheet1 col.A
textbox2-------------- Mid name : sheet1 col.B
textbox3-------------- Sur name : sheet1 Col.C

Private Sub CommandButton1_Click()
Dim LastR As Long, ws1 As Worksheet, r As Range, i As Integer
Set ws1 = Sheets("sheet1")
Me.Controls.Item (TextBox)
If Me.TextBox1 = "" Then GoTo last
    Set r = ws1.Range("a:a").Find(what:=Me.TextBox1)
    If Not r Is Nothing Then
        If MsgBox("Would you like to update the record for :" & vbLf & vbLf & Me.TextBox1, vbYesNo) <> vbYes Then
            GoTo last
        Else
            With ws1
                For i = 2 To 3: .Cells(r.Row, i).Value = Me.Controls("textbox" & i): Next
            End With
        End If
Else
    If MsgBox("Would you like to append the record? :" & vbLf & vbLf & "First name: " & Me.TextBox1 & vbLf & _
                "Midde name: " & Me.TextBox2 & vbLf & "Sur name: " & Me.TextBox3, vbYesNo) <> vbYes Then
                GoTo last
        Else
            With ws1
                LastR = .Range("a65536").End(xlUp).Row + 1
                For i = 1 To 3: .Cells(LastR, i).Value = Me.Controls("textbox" & i): Next
            End With
    End If
End If
last:
    With Me
        For i = 1 To 3: .Controls("textbox" & i) = "": Next
        .TextBox1.SetFocus
    End With
End Sub

hope this helps
jindon

 

[Ed B. Delen]

Hi Jindon,

Thanks It really works... but i want to ask one question..how did your arrived with this code? Did you typed it per syntax or is there anyway i can have this code or syntax automatically. Olease guide me how.

Thanks a lot.
ed

 

[jindon]

Ed B. Delen

The code should be pasted onto Form module.
When you are in VB editor, right click on userform icon in the Project viewer
and select View Code then paste the code there.

Good luck
jindon

 

[Ed B. Delen]

Hi Jindon,

Thanks It really works... but i want to ask one question..how did your arrived with this code? Did you typed it per syntax or is there anyway i can have this code or syntax automatically. Olease guide me how.

Thanks a lot.
ed

 

[jindon]

Ed B. Delen

There would be no MAGIC in writing VBA.

First you need to concentrate to the logic and find the rule then encode it.

That's all

rgds,
jindon

 

[Ed B. Delen]

Hi jindon

Thanks Again,

I thought it's like in simple macro...just simply record the movement or the commads.

Thanks


AGAIN... sorry but im really interested to learn more from you... Is there any online reference where i can get the syntax and codes.

ed

 

[jindon]

Ed B. Delen

You can learn and get information about vba from this board as well as internet like myself.

Try to write anything in vba for the first step and if you have problem, anybody here will help you.

rgds,
jindon

 

[HalfAce]

Hi jindon,
I've been looking at the code above and have a question.
The 4th line down:
Me.Controls.Item (TextBox)
Can you please tell me what the purpose of that line is? I don't see what it does.

Thanks,
Dan

 

[jindon]

Hi HalfAce,

Confusing!

It is nothing to do with the code.
I was to delete that line, but it was too late.
I doesn't harm anything though.

Sorry for confusing you.

rgds,
jindon