User form

[ddnron]

Hi - I'm looking for help to make data entry more productive for the users.

Here is what I have in an excel spreadsheet. Hope it is not to confusing. I tried to post the Excel spreadsheet, but could not figure that out. So I will have to describe what I would like.

In coulmn b, I have listed from row 6 to row 600, the store number, of the stores we deliver to.

We deliver to them on pallets. We track how many pallets we picked up and how many pallets we delivered, we deliver 3 times a week. So from column c to h, we enter how many pallets we delivered and how many we picked up, for the three deliveries that week. See example. I have this same set up for 3 more weeks, because we track and bill every four weeks.



Column

Week 1
B_______ c ____d_____e _____f_____g____h
_______del___P/u___Del____p/u___del___p/u
401 _____3______4____ 3_____4______5____3

I have formulas at the end of the row that calculates this data. It tells me how many we delivered and how many we picked up, for the four week period. We then bill accordingly.

This works fine as long as the users do not mind scrolling down 600 rows to find the store they want. Well, the users do mind, go figure. I tried using the "Find function", but have to close it to enter my data.

So I'm wondering, is it possible to have the users select a button that when selected, a form appears, that has a box for entering the data above. In this form is a box where they enter the store number and when they enter the store number it pulls up the information corresponding to the correct row, which the store is located. The user fills in the form for each delivery, then closes, and the data for the store goes to the correct row.

Using store 401 as an example - they enter 401 on the user form. When it is entered, a form (or the same form) appears and there are 12 boxes labeled delivered and 12 labeled picked up, each box refering to the row location of 401 then each box, would refer to the correct column for the delivery they are entering. Can the form display all of the prior deliveries, when they enter the store number, this way the use will not get confused at which delivery they are entering.

They would have to do this 12 times, in a four week period. The user has to enter about 1000 deliveires a week. They currently enter it daily from the deliveries the day before.

Any suggestions on how I can make this work, or easier, would be grateful.

I no this is long, but like I said early, I could not figure out, how to post my excel sheet. [/img]

 

[BrianB]

Have a look at my userform code here.
I have added a message with button code to find a record.

http://www.mrexcel.com/board2/viewtopic.php?p=1316134#1316134

 

[ddnron]

Hi Brian,

Thanks for your post to help me out. I'm a novice when it comes to this VB code, so please bare with me.

I created my user form, and I created a button on the user form labeled Findbutton, I pasted your code for "find record & put to form" to this button. The windows pops open to find the restaurant number, however when I enter the restaurant number and select ok, it returns a pop up window that displays "Compile error and Syntac error" it then takes me to the vb code line, for that button "with datasheet.range("A:A")". It highlights it.

Am I doing something wrong, am I suppose to assign a range?

I like how the window pops open for the find button. I would like to enter the restaurant # and when I select "ok" it goes and finds the row associated with restaurant number and displays that number. in a box on the form. I would also like it to display the information for that row, through column"m"

Any help is appreciated. Thanks again. Novice ron

 

[BrianB]

1. I suggest you copy all the code to the userform even though you may not want it. Can delete the bits not needed.

2. You will need to change things to relate to your setup. All the relevent suboutines have headings. eg. ..........
a.Presumably your worksheet is called "Database" or you have changed the code to suit.

b. It looks like your ID number is in column B not column A. The idea of this code is to limit the Find range (to make it faster in a big sheet) and notlimit the number of rows - so the data can be added to without changing the code. Try this alternative line :-

With DataSheet.Columns("B").Cells

c. You will need to change Private Sub UpdateForm() and Private Sub UpdateTable() to match your cells. Here it uses columns A to C.

d. If you are using them, change the names of buttons on the form to match the code.