Using Same Subroutine for the numerous command buttons in a sheet

[sumitbansal]

I have a program of inventory management where the user has to calculate the quantities of the item as an option. The Default item is one ( if the quantity is known); however, if the quantity is unknown, then the subroutine kicks in. But for that to happen the user wants that either he is given a command button in every cell of that column next to the column displaying the quantities or he should be able to double click the quantities Cell and a macro should run. The Data entry sheet is about 100 rows, so, either i need 100 command buttons or a macro running at double click. Please help me with this as either command buttons or double clicking would run the same subroutine and I don't know how to do it.

Thanks,

Sumit.

 

[Hui]

Sumit

There is a Double Click event handler in Excel

One of it's parameters is the Target Cells Address

So you can then program that event to react to where you have double clicked

You would first check if it is in a predefined range just in case some one double clicks else where

[pre]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Your code here
End Sub

[/pre]

 

[sumitbansal]

Hui,

Thank you very much... I was so engrossed in looking for/writing the code myself that I just forgot that microsoft has done it already as "before double click" even.... Thanks for reminding it to me again.


The code I thus wrote as like this:

[pre]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("H6:H105")) Is Nothing Then
Application.EnableEvents = False
'mycode comes here
Application.EnableEvents = True
End If
End Sub

[/pre]

Please let me know if it is ok..


Thanks,

Sumit

 

[Hui]

That looks about right

Good luck with the rest of the code

 

[sumitbansal]

Thanks again Hui..


Sumit.