Macro to Cell

[Guest]

How do I assign a macro to a cell so that when one clicks on it a Listbox
opens or a macro runs?

Thanks

 

[Don Lloyd]

Hi,
You could use the Worksheet_SelectionChange event.

For example, assuming the cell to be D10

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address="$D$10" then
MsgBox Target.Address
End If
End Sub

or using row and column numbers
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 10 And Target.Column = 4 Then
MsgBox Target.Address
End If
End Sub

In place of the MsgBox, you could enter code or call a macro.

Hope that gets you started.
Don

 

[Guest]

Hi,
You could use the Worksheet_SelectionChange event.

For example, assuming the cell to be D10

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address="$D$10" then
MsgBox Target.Address
End If
End Sub

or using row and column numbers
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 10 And Target.Column = 4 Then
MsgBox Target.Address
End If
End Sub

In place of the MsgBox, you could enter code or call a macro.

Hope that gets you started.
Don

I want the click on the cell to call a ListBox. Where does the "Private Sub
Worksheet" go?? In the UserForm/ListBox code, in the sheet or where??

Thanks for your help.

 

[Claud Balls]

For a list box you could use data validation.
Go to: Data > Validation...
Select list from the Allow: box
In the Source: box select the range with the data you need for the
user's selection.
Hit OK

 

[Guest]

For a list box you could use data validation.
Go to: Data > Validation...
Select list from the Allow: box
In the Source: box select the range with the data you need for the
user's selection.
Hit OK

That is a cool function, but it can only ref one column. Second, is it
possible to make it dynamic so that it changes with the list size?>

 

[Don Lloyd]

The Sub goes in the Worksheet Module

Right-click the sheet tab and select View Code.
Select Worksheet in the left column and SelectioChange in the right column

regards,
Don

 

[Bob Phillips]

That is a cool function, but it can only ref one column. Second, is it
possible to make it dynamic so that it changes with the list size?>

If we assume the list is in A1:An, use this formula with the DV list

=OFFSET($A$1,,,COUNTA($A:$A))