Link forms

Ed B. Delen

Board Regular
Joined
Feb 16, 2005
Messages
78
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
Code:
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

Board Regular
Joined
Feb 16, 2005
Messages
78
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

Board Regular
Joined
Feb 16, 2005
Messages
78

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

Board Regular
Joined
Feb 16, 2005
Messages
78

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,118
Messages
5,599,817
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top