SOLVED

Macro Button of products that adds the selected product into order list

Copper Contributor

Dear Community,

 

I am trying to make an order list within excel. 

In the current version i use a dropdown menu (8 options) for adding products into the order list.
Up to 80 products are requested daily, which takes a lot of time to select via a dropdown menu.

 

Therefore i want to speed up things, by making a macro button. That adds a product always one cell lower than the previous.

 

Example:
Button 1 = Wash plate 1
Button 2 = Wash plate 2

 

First 1 click on wash plate 1 --> wash plate 1 added into list

Then i click on wash plate 2 --> wash plate 2 will be added below wash plate 1

Excel.jpg

i tried using FillDown in combination with Range. Because  Range ("A1:A10"). FillDown --> fills the range A1:A10, based on the contents of cell A1. So I thought this was a good starting point. And i tried to change it by not taking cell A1 as contents but B2 and FillDown that content into range H16 to H80.

What looked like this:
Sub Wasplate 1 ()

Range ("B2", "H16":"H80")

But i can't seem to make it work. And i also don't want it to fill in the entire range from H16 to H80. But one by one as visible in the photo.

 

Could someone help me with this?

 

Thank you very much in advance!

 

 

 

4 Replies

@Mdjong 

Let's say you want to enter Wash Plate 1 or Wash Plate 2 in H16 and down.

Sub WashPlate1()
    Dim r As Row
    r = Range("H82").End(xlUp).Row
    If r >= 80 Then
        MsgBox "Geen ruimte meer!", vbCritical
    Else
        Range("H" & r + 1).Value = "Wash Plate 1"
    End If
End Sub

Sub WashPlate2()
    Dim r As Row
    r = Range("H82").End(xlUp).Row
    If r >= 80 Then
        MsgBox "Geen ruimte meer!", vbCritical
    Else
        Range("H" & r + 1).Value = "Wash Plate 2"
    End If
End Sub

@Hans Vogelaar 
I get the following error: Compile error: User-defined data type not defined

And it will mark the following yellow : Dim r as Row.

If i try a different data type, like Range it will give: objectvariable or blockvariable with not defined.

 

So i tried the following: 
Sub WashPlate1()
Dim r As Range
Set r = Range("H80").End(xlUp)
If r >= 80 Then
MsgBox "Geen ruimte meer!", vbCritical
Else
Range("H" & r + 1).Value = "Wash Plate 1"
End If
End Sub

 

With the code above i get the "Geen ruimte meer" msg. Only nothing will be added, and there is still place left.

 

if i change the code to: 

Sub WashPlate1()
Dim r As Range
Set r = Range("U80").End(xlUp)
If r >= 80 Then
MsgBox "Geen ruimte meer!", vbCritical
Else
Range("U" & r + 1).Value = "Wash Plate 1"
End If
End Sub

 

The code works, probably because column U is completely empty:

But the question now is, How can i make sure this happens within "H". And also if i click again, i will get the msg "Geen ruimte" again and i can't seem to get wash plate 1 in the cell beneath "U1". 

Like this:

Wash plate 1
Wash plate 1

 

Could you help me further?

 

 

best response confirmed by Mdjong (Copper Contributor)
Solution

@Mdjong 

I'm sorry, that was a typo. It should be

 

Dim r As Long

It works!!
Thank you very much!
1 best response

Accepted Solutions
best response confirmed by Mdjong (Copper Contributor)
Solution

@Mdjong 

I'm sorry, that was a typo. It should be

 

Dim r As Long

View solution in original post