Is it possible to replace a button with something else?

  • Thread starter Thread starter hce
  • Start date Start date
H

hce

Hi Excel Experts

Thank you so much to those who helped me with the data merge the othe
time... it was fascinating and amazing what few simple lines of code
can do...

Now I have another problem. I have created a report which contains som
buttons with macros attached to them. My question is, is it possible t
replace these buttons because there's simply too many buttons. Is i
possible to attach a macro to a specified cell and when the cell i
double clicked or clicked, the macro will run?

I heard of using functions n stuff but being a very lousy person wit
vb, i wasn't able to figure this problem out myself... Once again
would really appreciate any suggestions or help...

Cheer
 
You could have it tied to double clicking a cell.

This code shows the technique

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Not Intersect(Target, Range("A1,B10,C99,D1,H1:M100")) Is Nothing Then
' call your code
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
One way:

You can use the Worksheet_BeforeDoubleClick event to fire your macro
when a cell is double-clicked (you could also use the
Worksheet_SelectionChange event to fire your macro when single clicked,
but it would also fire if your user tabbed, or used cursor keys).

Put this in your worksheet code module (right-click on the worksheet tab
and choose View code):

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
Select Case Target.Address(False, False)
Case "A1"
Cancel = True
MyA1Macro
Case "J10"
Cancel = True
MyJ10Macro
Case "AB275"
Cancel = True
MyAB275Macro
End Select
End Sub

Substitute your cells/macro names.
 
Back
Top