Userform - coding the button

[jamescatton]

Firstly, thanks to jmthompson who helped me with my previous query. I now have a new query, and wasn't sure whether to repost in that thread, or start a new, specific thread. I hope this is ok.

I am trying to set up an excel spreadsheet to keep track of glasses that I loan out, and to make sure we don't go over our stock.

I have created a userform, for the first time, which allows the user to select a "date out" and a "return date". And then you can select how many trays of the 4 different types of glasses to order.

What I'd like to know now, is how to make excel input this data into the spreadsheet on the click of a button in the userform. Hope this makes sense.

Any help you can provide would be much appreciated.

 

[ChrisM]

You have to enter code in the button that the user clicks to submit the data.

Private Sub Commandbutton_Click()
With Worksheets("MySheet")
     .Range("A1") = Textbox1.Text
     .Range("A2") = Combobox1.Value
     'etc
End With
End Sub

 

[jamescatton]

Hi thanks for your help - I've tried my best, but not having any luck.

I've created a userform where someone can input a date out and a return date, but I'm trying to get the computer to return only the number of items of stock on hand for those dates. I think its something to do with specifying which cells the userform uses as a list to look up, but not sure how to achieve the outcome.

 

[ChrisM]

Sorry, I'm getting a little confused. Your first post said you wanted to write information in the userform to your spreadsheet. The code I posted will do that.

Are you still wanting to do that?

Your latest question, I think, is you want to put some item data and start/stop dates into the userform, and you want the form to tell you whether stock is available on those dates?

If that is what you now want, then the answer depends on how you are storing the item data in the worksheet. Can you describe the structure of your data tables?

 

[rguy84]

Where would the stock on hand number display?

 

[jamescatton]

Hi. Thanks for your help.

At the moment, Ive got a separate worksheet. But I'm not sure if this is the easiest way todo it. I'm really stumbling through the dark with this thing lol.

 

[rguy84]

You need to supply more info James, you are asking for help with multiple things, and not sure whatt you want done

 

[jamescatton]

Right, sorry guys - I'm confusing myself as well. My apologies, its all very new to me.

Basically this is my query -

I've built a userform, where a person selects two dates - one for stock going out, and another for stock coming in. When those two dates are selected, I would like another combo box in the userform to only display the amount of available stock for the glasses that are available between those dates.

The stock on hand, at the moment, is on a separate worksheet.

I think what I'm after, is making the combo box for example "champagane" glasses look up the list of stock available on those dates. I can make it look up a sort of normal list, but I can't specify it to only look up the dates that are specificed and return the lowest result within those dates.

I hope that kinda makes a bit more sense - would it help if I uploaded what I've done so far?

Thanks for your help

 

[rguy84]

What does your spread sheet or workbook look like

 

[jamescatton]

Ok currently it has 3 worksheets...
The first one is just a button that is pressed to launch the userform

The second is "inventory" which has a horizontal list of dates - one day per cell - going across. And then for each day, theres a dummy list of orders for example CHAMPAGNE 3, WINE 4, HIBALL 6 and PINT 2.

For each day, I've set it so that a running total is produced - for example, we have 10 of each type of glasses, so it minuses any orders each day.

The third worksheet is the lookup lists where theres another list of the same dates, which is used in the userform