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

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 requeste...

excel

Macros and VBA

HansVogelaar
Jan 24, 2021
Mdjong

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

Dim r As Long

HansVogelaar

MVP

Jan 24, 2021

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

Mdjong

Copper Contributor

Jan 24, 2021

HansVogelaar
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?

HansVogelaar
MVP
Jan 24, 2021
Mdjong

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

Dim r As Long
- Mdjong
  Copper Contributor
  Jan 24, 2021
  It works!!
  Thank you very much!

Forum Discussion

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

Resources