Forum Discussion

Mdjong's avatar
Mdjong
Copper Contributor
Jan 24, 2021
Solved

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

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!

 

 

 

excel
Macros and VBA

4 Replies

  • HansVogelaar's avatar
    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's avatar
      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?

       

       

Resources