How to simplify data entry
Closed
Gofreo
Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017
-
Updated on May 1, 2017 at 10:55 PM
x - May 4, 2017 at 01:53 PM
x - May 4, 2017 at 01:53 PM
Related:
- How to simplify data entry
- Tmobile data check - Guide
- Patient data entry software free download - Download - Organisation and teamwork
- Nokia.mobi/entry/van/main/c7-00 ✓ - Nokia Forum
- Transfer data from one excel worksheet to another automatically - Guide
- Missing or invalid registry/ini entry need for speed most wanted ✓ - Video Games Forum
5 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 2, 2017 at 12:20 AM
May 2, 2017 at 12:20 AM
Hello Gofreo,
Hmmm. Wouldn't a nick-name like GoCollingwood be better? ;-)
Six months of back entries to catch up on! Geez mate, I'd be telling the missus to do it herself! However, if she sees this, she'll probably kill us both! So, let's try and sort this out for you.
By fields, do you actually mean columns? Such as:-
Column A: Date
Column B: Myself or wife
Column C: Daily Purchases
Column D: Daily Card Sales
Column E: Daily Total Takings
with all the relevant data in the rows below the headings?
You then would like the data automatically transferred to a second sheet (set out the same as the first) for your further analysis/tallying?
Let me know if I have understood your query correctly.
Actually, it would be good if you could upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or Sendspace then post the link to your file back here. This would make understanding/solving your query much easier. Use dummy data in the sample.
Cheerio,
vcoolio.
Hmmm. Wouldn't a nick-name like GoCollingwood be better? ;-)
Six months of back entries to catch up on! Geez mate, I'd be telling the missus to do it herself! However, if she sees this, she'll probably kill us both! So, let's try and sort this out for you.
By fields, do you actually mean columns? Such as:-
Column A: Date
Column B: Myself or wife
Column C: Daily Purchases
Column D: Daily Card Sales
Column E: Daily Total Takings
with all the relevant data in the rows below the headings?
You then would like the data automatically transferred to a second sheet (set out the same as the first) for your further analysis/tallying?
Let me know if I have understood your query correctly.
Actually, it would be good if you could upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or Sendspace then post the link to your file back here. This would make understanding/solving your query much easier. Use dummy data in the sample.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 2, 2017 at 09:30 AM
May 2, 2017 at 09:30 AM
Hello GoFreo,
Try the following code:-
It should add the data from the form to sheet1 row by row.
In a standard module, place the following code:-
This will clear the form.
Line 17 of the code will hide the form.
Add the appropriate headings to sheet1 and in another standard module, place the following code:-
then assign it to a button on sheet1. When you click on the button, the form will show up on sheet1.
I hope that this helps.
Cheerio,
vcoolio.
Try the following code:-
Private Sub cmdSend_Click() nr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1 Range("A" & nr).Value = txtDate.Text Range("B" & nr).Value = txtName.Text Range("C" & nr).Value = txtEvent.Text Range("D" & nr).Value = txtNotes.Text Range("E" & nr).Value = txtCoins.Text Range("F" & nr).Value = txtCard.Text Range("G" & nr).Value = txtFuel.Text Range("H" & nr).Value = txtMilk.Text Range("I" & nr).Value = txtOther.Text Unload Me frmdata.Hide End Sub
It should add the data from the form to sheet1 row by row.
In a standard module, place the following code:-
Sub Clear() Unload Me End Sub
This will clear the form.
Line 17 of the code will hide the form.
Add the appropriate headings to sheet1 and in another standard module, place the following code:-
Sub Showfrm() frmdata.Show End Sub
then assign it to a button on sheet1. When you click on the button, the form will show up on sheet1.
I hope that this helps.
Cheerio,
vcoolio.
Gofreo
Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017
May 3, 2017 at 12:05 AM
May 3, 2017 at 12:05 AM
Hi Mate thanks for your help its coming along just to show you were i am at
Private Sub cmdFrom_Click()
End Sub
Private Sub cmdSend_Click()
nr = Sheet1Data.Cells(Rows.Count, 1).End(xlUp).Row + 1
Range("A" & nr).Value = txtDate.Text
Range("B" & nr).Value = txtName.Text
Range("C" & nr).Value = txtVehicle.Text
Range("D" & nr).Value = txtEvent.Text
Range("E" & nr).Value = txtNotes.Text
Range("F" & nr).Value = txtCoins.Text
Range("G" & nr).Value = txtCard.Text
Range("H" & nr).Value = txtFuel.Text
Range("I" & nr).Value = txtMilk.Text
Range("J" & nr).Value = txtOther.Text
Unload Me
frmdata.Hide
End Sub
Private Sub Label1_Click()
End Sub
Private Sub cmdSortByDate_Click()
'
' SortByDate Macro
'
'
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("A2:A10"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:J10")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Private Sub cmdSortByName_Click()
'
' SortByName Macro
'
'
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("B2:B12"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:J12")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Private Sub cmdSortByVehicle_Click()
'
' SortByVehicle Macro
' Sort By Vehicle
'
'
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("C2:C11"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:J11")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Private Sub ctlDate_Click()
End Sub
Private Sub UserForm_Click()
End Sub
the clear for isn't working
and i tried to add a calendar but that didn't work out
and the other thing is the cheese and kisses, still not happy with where i am at. lol Some things wont change ;)
Private Sub cmdFrom_Click()
End Sub
Private Sub cmdSend_Click()
nr = Sheet1Data.Cells(Rows.Count, 1).End(xlUp).Row + 1
Range("A" & nr).Value = txtDate.Text
Range("B" & nr).Value = txtName.Text
Range("C" & nr).Value = txtVehicle.Text
Range("D" & nr).Value = txtEvent.Text
Range("E" & nr).Value = txtNotes.Text
Range("F" & nr).Value = txtCoins.Text
Range("G" & nr).Value = txtCard.Text
Range("H" & nr).Value = txtFuel.Text
Range("I" & nr).Value = txtMilk.Text
Range("J" & nr).Value = txtOther.Text
Unload Me
frmdata.Hide
End Sub
Private Sub Label1_Click()
End Sub
Private Sub cmdSortByDate_Click()
'
' SortByDate Macro
'
'
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("A2:A10"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:J10")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Private Sub cmdSortByName_Click()
'
' SortByName Macro
'
'
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("B2:B12"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:J12")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Private Sub cmdSortByVehicle_Click()
'
' SortByVehicle Macro
' Sort By Vehicle
'
'
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("C2:C11"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:J11")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Private Sub ctlDate_Click()
End Sub
Private Sub UserForm_Click()
End Sub
the clear for isn't working
and i tried to add a calendar but that didn't work out
and the other thing is the cheese and kisses, still not happy with where i am at. lol Some things wont change ;)
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 3, 2017 at 05:50 AM
May 3, 2017 at 05:50 AM
Hello GoFreo,
Good to see that you are having a go.
But I don't quite follow what you're up to! Perhaps Nat Fyfe's possible departure has you all knotted up inside!
Do you feel like giving us a complete run-down on what you're trying to do?
Cheerio,
vcoolio.
Good to see that you are having a go.
But I don't quite follow what you're up to! Perhaps Nat Fyfe's possible departure has you all knotted up inside!
Do you feel like giving us a complete run-down on what you're trying to do?
Cheerio,
vcoolio.
Gofreo
Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017
May 3, 2017 at 06:53 PM
May 3, 2017 at 06:53 PM
Hey Mate lol
Funny,
I heard that all Freo's senior players were off to St Kilda, all but Nat was quitting to take a musical career.
and the other news was that Trump was immigrating to Australia due to a lack of pollies over here ;)
Man FAKE news is every were
well back to the spread sheet
the first part is done and works a treat but when you input the data i thought i could use a pop up calendar to simplify the input
Ive saved it in drop box as well if you wanted to have a look
Scott
aka GoFreo
Funny,
I heard that all Freo's senior players were off to St Kilda, all but Nat was quitting to take a musical career.
and the other news was that Trump was immigrating to Australia due to a lack of pollies over here ;)
Man FAKE news is every were
well back to the spread sheet
the first part is done and works a treat but when you input the data i thought i could use a pop up calendar to simplify the input
Ive saved it in drop box as well if you wanted to have a look
Scott
aka GoFreo
Gofreo
Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017
Updated on May 3, 2017 at 06:59 PM
Updated on May 3, 2017 at 06:59 PM
the main object is to be able to used the data to compare month by month and what activity is best
so Step
1. was to input all the data and then
2. create a work sheet that then shows and compares the data
so we use a small time sheet to recorded the info and that's what has been building up over the last 6 months or so
Thanks for all your help Mate
so Step
1. was to input all the data and then
2. create a work sheet that then shows and compares the data
so we use a small time sheet to recorded the info and that's what has been building up over the last 6 months or so
Thanks for all your help Mate
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Updated on May 6, 2017 at 01:26 AM
Updated on May 6, 2017 at 01:26 AM
G'day Scott,
Yes, the fake news has taken a foot-hold everywhere. I suppose papers need to be sold. I take it all with a pinch of salt.
All the Freo senior players could form a band at St. Kilda perhaps!
Have you considered scrapping the form method of entering data and just directly entering the data to the spreadsheet? Either way, the data has to be entered. I personally don't have much time for forms.
For my own business, I have a large-ish workbook with tabs for each month and a totals tab (plus a Summary sheet just for my Accountant). From the totals tab, at a glance I can analyse/compare each month's performance and then either be happy or go off and cry. The individual month tabs hold all my income and expenses records which are then automatically summarised in the Totals sheet. The income section is automatically populated by macro from an Invoice Register in another workbook. My Accountant loves it (but still charges like a wounded bull!).
Would you be willing to have a look at something similar?
Cheerio,
vcoolio.
P.S. I get the missus to do all the entries for me each month ;-)
Yes, the fake news has taken a foot-hold everywhere. I suppose papers need to be sold. I take it all with a pinch of salt.
All the Freo senior players could form a band at St. Kilda perhaps!
Have you considered scrapping the form method of entering data and just directly entering the data to the spreadsheet? Either way, the data has to be entered. I personally don't have much time for forms.
For my own business, I have a large-ish workbook with tabs for each month and a totals tab (plus a Summary sheet just for my Accountant). From the totals tab, at a glance I can analyse/compare each month's performance and then either be happy or go off and cry. The individual month tabs hold all my income and expenses records which are then automatically summarised in the Totals sheet. The income section is automatically populated by macro from an Invoice Register in another workbook. My Accountant loves it (but still charges like a wounded bull!).
Would you be willing to have a look at something similar?
Cheerio,
vcoolio.
P.S. I get the missus to do all the entries for me each month ;-)
Didn't find the answer you are looking for?
Ask a question
Updated on May 2, 2017 at 03:11 AM
I am even trying to learn VB to get it done but not having much success see below
Private Sub cmdSend_Click()
Range("a2").Select
ActiveCell.End(xlDown).Select
Lastcolumn = ActiveCell.Column
'MsgBox Lastcolumn
Cells(Lastcolumn + 1, 1).Value = txtDate.Text
Cells(Lastcolumn + 1, 2).Value = txtName.Text
Cells(Lastcolumn + 1, 3).Value = txtEvent.Text
Cells(Lastcolumn + 1, 4).Value = txtNotes.Text
Cells(Lastcolumn + 1, 5).Value = txtCoins.Text
Cells(Lastcolumn + 1, 6).Value = txtCard.Text
Cells(Lastcolumn + 1, 7).Value = txtFuel.Text
Cells(Lastcolumn + 1, 8).Value = txtMilk.Text
Cells(Lastcolumn + 1, 9).Value = txtOther.Text
Range("a5").Select
txtDate.Text = ""
txtName.Text = ""
txtEvent.Text = ""
txtNotes.Text = ""
txtCoins.Text = ""
txtCard.Text = ""
txtFuel.Text = ""
txtMilk.Text = ""
txtOther.Text = ""
End Sub
not sure what i am doing wrong but then again i have little to no experience trying to work my way through Youtube
I run a small mobile coffee business and the wife has been onto me to catchup but you know how it is lol
anyway any help would be great (the above has taken 8 hours)
May 2, 2017 at 03:17 AM
May 2, 2017 at 03:22 AM
May 2, 2017 at 03:23 AM