User Form

[ImranBhatti]

Hi respected Excellers!

First of all I want to tell that I am not a programmer but the following application I need to develop because it will make my work easy as I have much more data to enter and the fields are too many


I have a table in a sheet.My table's first row contains headings(15 fields).I have created a user form that consists of the labels and text boxes,for some fields combo boxes. and then there are command buttons name "send data to sheet", "Quit"button ,"clear", Last Record","First Record","Find Record"
What code should I write in a way that after filling the last field for one record it should start from the next row for a new record.
What would be the codes for my last command buttons? I know how to change the names and captions of the forms items in the property boxes.

also please suggest me some links for user form DESIGNING.

I think it is the longest question but I hope you will help me.I am ready to further explain if it is not understandable.

Best Regards
Excelisfun

[bakerman2]

First post example file with table sheet and userform so we know what we're dealing with.

[ImranBhatti]

Thanks bakerman for reply.here is the sample file

[AmirSoft]

At first you should try this
This is built in solution in excel to simply enter data through a form.
I think you should try this at first.

https://support.office.com/en-us/Art...3-291fc03e4d77

Click on link and follow

[cfinch100]

hi
is this roughly what you want
cheers

[cfinch100]

hi
this loads the userform (look in workbook) and unloads with close form ,clear form now clears the first 3 boxes
cheers colin

[ImranBhatti]

Well here is a completed form.there is a sheet "MasterTable".The should be trasferred to that sheet in the table there in. I have Written the code to load the form from that sheet "mastertable".and also found the code to make the form go away.Rest of the codes are needed.The fields having comboxes I have managed by putting the row source in the properties.

I would be grateful to the forum and its members if I got this sheet work the way I wanted.Thanks for your time.

[cfinch100]

hi
why have you taken out what I had given you it was a starting point for you
colin

[bakerman2]

Let's start with this.

[AmirSoft]

Hi bakerman2

Please Login or Register  to view this content.

is not working....

[AmirSoft]

And I think at start we prefer to save .xlsm workbook.
It's easy to understand the macro.

The guy (Imran) started this thread is still saving .xls workbook and using Excel 2013.
How is it possible?

[bakerman2]

@ Amirsoft

Don't say it's not working if you havent' tried filling in all fields.
Then you would see it works perfectly.

[Potholes]

Let's start with this.

Good Evening

Did you get my PM earlier today.

Potholes

[AmirSoft]

Thanks bakerman2

But you know there is a possibility of empty control. Maybe any information is late or Not Available and temporary we have to left that control empty.

That's why I was saying.

The guy who posted this thread do not know much about the vba. So we should start from initial stage.

And he should start from simple forms then he can learn easily.

[ImranBhatti]

Hi respected members and moderators! I just logged in and saw many reply to my thread. Thanks to all for your time and I will write again after reading your replies and downloading the sheets and trying the same. I felt a quick reply/thanks a MUST.

Regards

[ImranBhatti]

Hello again! I have written the rest of the code according to your guide lines.The problem with "Send Data to sheet" command button code that I have edited and put my references there. It does sends the data to sheet but leaves first 3 rows empty.I don't know whats wrong with the code or is it my mistake to edit it. the sheet is again attached.

@bakerman and clon! your codes are used .

[cfinch100]

hi
see if this helps you a bit the 5 at the end of the line is the row number you want to start on
cheers colin

[ImranBhatti]

Now I have got many things only one problem left then I will mark this thread as SOLVED.Problem is that my userform although transferring data to worksheet but in the beginning it left one row.For your kind review the sheet is again attached.I tried repeatedly by subtracting the number of lines upward of my MasterTable .but still it left the first row of the table as blank.Nothing dangrious but it looks odd when viewing the table.

looking forward your kind suggestions

[cfinch100]

hi
refer to previous one I sent you can move to the row by changing + 5 is that what you are wanting ,to begin at the start
cheers colin

[ImranBhatti]

Here is my code for "Add data to worksheet". colin how can I insert your specific lines of code to this coz when I did the same it produces some errors perhaps the variables are declared differently in both codes.

Private Sub cmdAdd_Click()
Dim lngWriteRow As Long

Dim ws As Worksheet
Set ws = Worksheets("MasterTable")

lngWriteRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


If lngWriteRow < -8 Then lngWriteRow = -8



ws.Range("a" & lngWriteRow) = txtSrNo.Value
ws.Range("b" & lngWriteRow) = txtName.Value
ws.Range("c" & lngWriteRow) = txtFGN.Value
ws.Range("d" & lngWriteRow) = cbCat.Value
ws.Range("e" & lngWriteRow) = cbRlgn.Value
ws.Range("f" & lngWriteRow) = txtDOB.Value
ws.Range("g" & lngWriteRow) = txtAppDate.Value
ws.Range("h" & lngWriteRow) = cbCStts.Value
ws.Range("i" & lngWriteRow) = txtDAdmn.Value
ws.Range("j" & lngWriteRow) = txtAdmnNo.Value
ws.Range("k" & lngWriteRow) = cbClss.Value
ws.Range("l" & lngWriteRow) = cbSection.Value
ws.Range("n" & lngWriteRow) = txtAddress.Value
ws.Range("o" & lngWriteRow) = txtContact.Value

If Me.CBShaheed.Value = True Then
ws.Range("p" & lngWriteRow).Value = "Yes"
Else
ws.Range("p" & lngWriteRow).Value = "No"
End If

If OBRtd.Value = True Then
ws.Range("m" & lngWriteRow) = "Retired"
Else
ws.Range("m" & lngWriteRow) = "Serving"
End If
End Sub

[cfinch100]

hi

as far as I know there is no easy way just re write your code with

Please Login or Register  to view this content.

bit more work for you but it works have done some for you
cheers colin

[cfinch100]

hi
nearly complete just got to do option buttons,heres a start

Please Login or Register  to view this content.

cheers

[ImranBhatti]

Finally I have got what I wanted my User form to perform at first stage. It was possible due to your interest in the thread.Thanks a lot to forum members and moderators. Attached is the sheet with the code I was able to write with the help of your suggestions.The thread is SOLVED.But its not my last thread.

Best Regards
Imran Bhatti

[cfinch100]

pleased to help