Macro to Cell
 

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

Thanks

Macro to Cell
 

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

"Ronbo" wrote in message
...
How do I assign a macro to a cell so that when one clicks on it a Listbox
opens or a macro runs?

Thanks

Macro to Cell
 

"Don Lloyd" wrote:

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

"Ronbo" wrote in message
...
How do I assign a macro to a cell so that when one clicks on it a Listbox
opens or a macro runs?

Thanks



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.

Macro to Cell
 

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Macro to Cell
 

"Claud Balls" wrote:

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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?

Macro to Cell
 

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

"Ronbo" wrote in message
...


"Don Lloyd" wrote:

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

"Ronbo" wrote in message
...
How do I assign a macro to a cell so that when one clicks on it a
Listbox
opens or a macro runs?

Thanks



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.

Macro to Cell
 

"Ronbo" wrote in message
...

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