Macro or code to copy a "command button"

[johannordgren]

As the title says, my need is to copy a made command button to another 500 rows.

The code I'm using is this;

Private Sub CommandButton1_Click()
Dim x As Integer
x = Range("A1").Value
Range("A1").Value = x + 1
End Sub

Which makes a count of how many times the button is clicked.

What i would like is to copy this button, pretty much like you would copa down a normal excel cell. I'm talking about the "draging down".

Essentialy i would like the cells in the formula above (A1) to automaticly change, or if you are smarter than me, which you probably are, make a completely different code.

I dont even know if this is possible, but if it is, please help!

 

[My Aswer Is This]

I surely do not think you need 500 Buttons
Tell us what your ultimate goal is?

What will the button do other then keep a log of how many times the button has been clicked.

 

[johannordgren]

The file is an orderlist for materials not linket to the companys VMS system.
Each row is an article.

My goal is to have a button for each article, that you click when you order it. --> counting numer of orders for that specifik item

 

[My Aswer Is This]

OK.
So you order the item on row(5) and you click the button on row(5)
What do you want to happen?
Will you enter some value in column B of row 5 or what.
Explain in detail please

Then I believe I can show you how to do this without needing 500 buttons.

 

[johannordgren]

Lets say the article.no is in A1, and the button is places on B1.
I want the count of the number of clicks to be shown on C1.

So, if that article has been ordered 3 times, and the button for that article has been pressed 3 times, then the number in C1 in this case should be 3.

So all of the articles are seperate.

Are we on the same page?

 

[My Aswer Is This]

Yes I understand that.

But I want to know what else the button does.
How does clicking on the button order the item.

Are you saying that's all the button does?

 

[johannordgren]

Oh, sorry..

Since these articles isn't included in the companys ERP system. Meaning we have no way of tracking the orders med since we have no internal article.no for them.
I simply need this document to keep count of the number of times we order each item. I also have the prices listed here.

But the only thing the button is for is to click when you place an order towards the suplier, but the button doesn't place the order.

That what you wanted to know?

 

[My Aswer Is This]

Try this script and see if it would do what you want.
It requires no button.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you double click on any cell in column "B" the script will do what you want.
The value in column C will be advanced by one.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 4-24-18 9:35 AM EDT
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Cancel = True
Target.Offset(, 1).Value = Target.Offset(, 1).Value + 1
End If
End Sub

 

[johannordgren]

Unfortunatley I have to leave the PC for the day..

But thank you very much! I will get back to you with the results in the morning!

 

[My Aswer Is This]

Unfortunatley I have to leave the PC for the day..

But thank you very much! I will get back to you with the results in the morning!

OK.