Excel VBA-UserForm control problem

[ceplane]

I'm a slow learner. I have been trying to get a UserForm to enter a
range of dates and times via four textboxes and six buttons.
Start Date
End Date
Start time
End time
plus six buttons annual,sick,etc.
This is a way to keep track of time off and extra time earned at work.
I want to enter the date and times with a catagory using a userform to
enter the data in a multi sheet workbook. Everyone I have asked so far
say its beyond their scope. Any suggestions?

Thanks

 

[Bob Phillips]

Put it all in a table on a worksheet and use Data>Form to maintain it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rune_Daub]

actually its not that difficult to make a userform like that.
What you do is that you first of all set up your worksheet for the wa
you want it to end up.
Then in an empty area on the same worksheet you make a cell with th
formula countv(A:A) or some column that you are sure that will b
filled out for each row of data you add.

Then you open up the userform. Create the table design you need and ad
the following code into your vba

Private Sub cmdSickButton_Click()
Worksheets("Data").Activate
Range("A1").Activate
ActiveCell.Offset(Range("U1").Value, 0).Activate

ActiveCell.Offset(0, 0).Value = Startdate.Value
ActiveCell.Offset(0, 1).Value = Enddate.Value
ActiveCell.Offset(0, 2).Value = Starttime.Value
ActiveCell.Offset(0, 3).Value = Endtime.Value

end sub

Of cause you make sure that your data corresponds.

That way with this code you will have the same function as using th
form. You just have greater control over it.

The code will automaticly fill out the first empty line on th
worksheet with the data that you have entered in the fields and presse
the "Sick button"

Rune Dau

 

[ceplane]

Thanks for the input, however I'm still missing the conection.
I have a table with 365 dates in column A, Time start in column B,Tim
end in column C, etc. I don't understand vb well enough to come u
with the formula to link the userform to the table. Without makin
more than 365 entries in the formula, there must be a range or an "if
combo"statement that will work. I will try to attach an example o
what I am doing.

Again Thank you all for your assistance. I now have a very healthl
resect for those who do this all the time

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=42228