UserForm linking

[NPK]

This is my first attempt at putting together a form. I've got an idea of what I want it to do, but I am not at all experienced with the VBA code to make it work.

Here is my user form:

I want each of the entries to go to the column of the date that corresponds with the entries.

1. The Date button will prompt a calendar for the user to select from. I think my form needs an additional text box for the date to reside in until all of the fields are filled in.

2. The value entered into each form box should then enter into the column that coincides with the date entered. What would be the VBA commands to make this happen and where should they be placed at?

3. The OK button should send all the information to their individual cells and automatically take the user to a chart in Excel that shows the data and trends that I am trying to represent. The userform shoul also close.

4. The Cancel button should take the user to the excel worksheet where they can manually manipulte the data if so desired, and close this userform.

5. The Help button will prompt a dialog box with instructions on operating the spreadsheet.

The processes don't seem too complicated as all of the calculations and analysis will occur in the worksheet. However, I am just getting my introduction into VBA, so I really don't know what goes where.

Thanks in advance.

 

[NPK]

I have worked on this a bit and have refined my needs . I've still got a long way to go in terms of format and function.

Here is the updated front.

I have also figured how to link the files to specific cells. Using this simple code.

Private Sub CommandButton1_Click()
End Sub
 
Private Sub Calendar1_Click()
End Sub
 
Private Sub CommandButton2_Click()
Range("A2") = Calendar1.Value
    Range("A2").NumberFormat = "mm/dd/yyyy"
Range("b2").Value = TextBox1.Value
Range("c2").Value = TextBox2.Value
Range("d2").Value = TextBox3.Value
Range("e2").Value = TextBox4.Value
Unload Me
End Sub
 
Private Sub CommandButton3_Click()
Range("A2") = Calendar1.Value
    Range("A2").NumberFormat = "mm/dd/yyyy"
Range("b2").Value = TextBox1.Value
Range("c2").Value = TextBox2.Value
Range("d2").Value = TextBox3.Value
Range("e2").Value = TextBox4.Value
End Sub
 
Private Sub CommandButton4_Click()
Unload Me
End Sub

What I need to be able to do is have the date drive the row that the information is placed in. It really needs to be able to perform a look-up function. That is beyond me in VBA, but I understand the premise from excel. The date will be in Column A, with subsequent data in B, C, D, so on (I don't know how many columns or the exact arrangement yet, but I'm estimating 15 columns.)

The date and one additional row of information will be imported into this spreadsheet daily from a network source.

I would also like the command buttons to be able to lead the user to a chart, say Chart1.

Looking forward to some help. Thanks a ton.

 

[MarkBoy]

Will the data always be entered in date order? In other words, will the data you are entering always go in the next available row?

lastrow = Range("A65536").End(xlUp).Row

will give you the lowest row that has data in it

Then
lastrow = lastrow + 1
Range("A" & lastrow) = Calendar1.Value
Range("A" & lastrow).NumberFormat = "mm/dd/yyyy"

etc...

 

[NPK]

Will the data always be entered in date order? In other words, will the data you are entering always go in the next available row?

lastrow = Range("A65536").End(xlUp).Row

will give you the lowest row that has data in it

Then
lastrow = lastrow + 1
Range("A" & lastrow) = Calendar1.Value
Range("A" & lastrow).NumberFormat = "mm/dd/yyyy"

etc...

That is a big help. Thanks.

After putting this in, I got to thinking that I would really like it to place the number in the order of the date. Say it is June 16 and a person needs to enter a record for June 12. I would like the userform to show the values for June 12 if already entered or will enter the value between June 11 and June 13 if there is no June 12 values.

Thanks

 

[NPK]

I've just made a Macro using the sort ascending tool on Excel to order the inputs in terms of date.

What I have found is that the next entry from the input box doesn't go to the true last row, rather it inserts itself immediately after the last row of data entered. This is an issue for data entered out of chronological order. The newest entry will write over the previous data.

What do I need to do with the lastrow command to make it always go to the physical bottom of the data and not the last row entered?