Collect Data On Excel Sheet Using Input Box

[nicolenwn]

I have a user form which has a number of buttons on it. Each button represents a vegetable (Eg, Cucumber, Carrot, Tomato)

The purpose of this is to take orders from customers for the vegetable they would like to order and the amount.

When each button is clicked (eg, Carrot) the macro would place the word 'Carrot' on the excel sheet. After which an input box will appear for the user to input the amount needed.

The problem i have is, i want to collate this data but i cannot set the cell to place the word 'Carrot' as it might not be the first thing the user orders.

I need a code that collates these data in any order that the user orders.

This is the code for a button that i currently use (But it only runs to two line before it overwrites itself) :

Private Sub Carrot_Click()

     a = 6

'    Do
    If Cells(a, 3) = 0 Then
    Cells(a, 3).Value = "Carrot"

    Else

    a = a + 1
    Cells(a, 3).Value = "Carrot"

    End If

'   Loop Until Cells(a, 4) = 0

'    Range("C6").Select

    Dim foodAmt As String

    foodAmt = InputBox(Prompt:="Please State Amount", _
    Title:="Please Enter Amount", Default:="Enter Amount Here")

          
    If foodAmt = "Customer Name Here" Or _
    foodAmt = vbNullString Then

    Exit Sub

    Else

                 a = 6
    

                 If Cells(a, 4) = 0 Then
                 Cells(a, 4).Value = foodAmt
    
                    Else
    
                 a = a + 1
                 Cells(a, 4).Value = foodAmt
    
                 End If

End If
End Sub

I need the list to collate.


If there is a better way to do this, i would greatly appreciate it too.

[Guido Geurs]

dear,

Is it possible to attach the XLS file?
so we can talk about the same things.

br,

[kadghar]

well, the code seems fine, the only problem is that each time te button is clicked you get a = 6, so you'll be always writing in the same row.

Starting from the 6th row, you can find the first empty cell by many ways. The first one that comes to my minde is using RANGE().END(-4124), Note -4121 is the value for the constant xlDown (that you dont necesarily have).

But a bit more intuitive way could be with a DO/LOOP :

[CODE=vb]dim a as long
a=6
do
if cells(a,3).valu e = "" then exit do
a = a +1
loop
'now a is the first empty cell starting from the 6th[/CODE]

Im assuming you're using VBA in your Excel. Next time you post a question, make sure to mention your VB version.

HTH

[nicolenwn]

Collect Data On Excel Sheet Using Input Box

Hi GGeu & Kadghar,

Many many thank you for your help!! I really appreciate it

The Version of my VB is Microsoft Visual Basic 6.3. I am not really sure if this is what you mean. Sorry

Also, attached is the file that i use. This is supposed to be an interactive menu to use when taking vegetable orders. Basically i need to collate the orders based on what the user clicks on the menu.

I managed to get it working but using i = 1 and then loop then i = i + 1.

But now i need the Carrot and the value(amount / foodAmt) that the user types in to be linked. So that whatever the user types, it will be placed beside the cell named carrot. So for example, if 'Carrot' is in Cell (A1) then the value or the amount of carrot must be in Cell (B1)

Does this make sense? Sorry to be so vague. Because i've tried the menu and if the user so happens to enter an empty value, the next item on the menu will move up and thus resulting with inaccurate results.

Additionally, i wish to add more functions like if the value (foodAmt) added is a single digit then the output would be in kilograms (kg) [foodAmt & "kg"]

and if the value contains 3 digits or more it would be in grams (g) [foodAmt & "g"]

Let me know your thoughts and opinions as i am a little new in the area and i appreciate all the help once again!

Cheers,
Nicole(:

[kadghar]

That should use the same code you have, just use cells(a,5) and (a,6), and a couple of IF/THEN

[Guido Geurs]

Dear,

I hope this is what You want ( see attachment)

br,

[nicolenwn]

Dear ggeu,

This is excellent! Oh my god a million thanks to you(: you've helped alot and i appreciate it!

Have a great day!
Nicole((:

[Guido Geurs]

Dear,

It was a pleasure to help You.
If You have other problems with your project, please let us know.

br,

[nicolenwn]

Hey ggeu,

Thanks for the help again!

I have another question! As you saw in my previous excel workbook, i had a tab named 'Price List'

Because the rates of the vegetables change everyday just like foreign currency do, is there a way i can input the rates in the 'Price List' tab and then using this tab, calculate the price of the vegetables that is generated using the 'Take Order' button?

This is used so that it will be easier for the user to do the weekly P&L for how much vegetables is sold to a customer.

Sorry for asking too much! Once again i appreciate all the help((((:

Cheers & have a great day!
Nicole (:

[nicolenwn]

Sorry, for to add,

I've tried adding a customer name that does not exist in the user list, but nothing happens. I wanted to add a msgbox to inform the user but i do not know where to put it, haha so sorry.

MsgBox "Customer Not Found! Please Add In 'User' Tab"

Thanks guys!
Nicole

[Guido Geurs]

dear,

the users are added in the sheet "Users".
The range (A1:A16) = "Usesr" is known as referency for the code line=

     For Each c In Range("users")
         If StrName = c Then

This is for checking if the value entered in the form, is valide.

If you want to add a user, just add in (A1:A16)
If this is not enough, you have to extend the range "Users" by the menu commands:
[insert] - [Name] - [define]
select "Users" and change the range at the bottom with:

=Users!$A$1:$A$ .. (or select in the sheet)

PS:
I'm checking how i can link the prices with the orders.
I hope I have a result soon.


br,

[Guido Geurs]

Dear,

Attached is a excel which sets the price next to the weight of the products.

I have some questions:
Q1 - are there many products ( roughly) because if so, we have to use a listbox in stead of labels in the form.

Q2 - must the weight x price be calculated and the total cost ?

PS:
I have also extend the range "user" to the full column "A"

br,

[nicolenwn]

Dear,

Attached is a excel which sets the price next to the weight of the products.

I have some questions:
Q1 - are there many products ( roughly) because if so, we have to use a listbox in stead of labels in the form.

Q2 - must the weight x price be calculated and the total cost ?

PS:
I have also extend the range "user" to the full column "A"

br,

Dear ggeu,

Many thanks for your research efforts, i cannot thank you enough!

Q1: Yes there will be many products from different categories (Eg, Vegetables, Poultry, Dried goods). But if a listbox is created will it be user friendly in a way that the user is able to add and remove new items to order in future?

Q2: Yes the weight must be multiplied by the price ($00.00) and then the total cost of the order must also be tabulated.

Additionally, Q3: Will it be possible to also count the number of items the user has ordered and also placed it in the order form at the top left/right? (eg, if the user orders 2kg onions, 3kg garlic, 4kg carrots, 1kg cucumber = total 4 items)

Many thanks again for all your help and attention, your efforts are truly appreciated!!!

Have a great day ahead,
Cheers
Nicole(:

[Guido Geurs]

dear,

attached is a sheet in which you can call the menu with:

Textfeelds for the weight in Kg and gr.
Buttons for selecting the group of products (fruit, fish,...)
A drop-down list with the products of the selected group.
ADD button for adding the "name" - "weight" -"price/kg" - "total price" to the sheet.
END button for closing the command which generate a total of different products and a total of the price.

PS: for adding a product to the list, just add the product and price in the correct group in the sheet "Price_List ".
No need to sort (the program does it at startup)
You can also add different command of products the same day to the same sheet.
Just call the menu and add a new command of products.


I hope this will help you with your project.

br,

[nicolenwn]

dear,

attached is a sheet in which you can call the menu with:

Textfeelds for the weight in Kg and gr.
Buttons for selecting the group of products (fruit, fish,...)
A drop-down list with the products of the selected group.
ADD button for adding the "name" - "weight" -"price/kg" - "total price" to the sheet.
END button for closing the command which generate a total of different products and a total of the price.

PS: for adding a product to the list, just add the product and price in the correct group in the sheet "Price_List ".
No need to sort (the program does it at startup)
You can also add different command of products the same day to the same sheet.
Just call the menu and add a new command of products.


I hope this will help you with your project.

br,

thanks for your efforts, but i'm kinda having an error in
Sub GetCustName()

Error message: Compile error
Can't find object or library.

Cheers,
Nicole