Assign macro to a cell

[roi santos]

I would like a to activate a form every time I click in a given cell. Does somebody knows how to do this?

 

[Richard Schollar]

Sure - you need to use the Selection_Change event: right click on the tab name and select View Code. Paste the following in to the code window that will open:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
If Target.Address = "$A$1" Then Call YourMacroNameHere  'amend the address to the be cell you want, and YourmAcroNameHere to be the macro you want to activate
End Sub

Note that this does mean that you will have to click out of this cell and click back into it to activate it (as it works on the change).

 

[Weaver]

If you right click on the tab of the worksheet where the cell lives, and choose 'view code', you can create a macro which will activate whenever a specific event occurs on the sheet.

If you select 'worksheet' from the left listbox and either 'beforerightclick' or 'beforedoubleclick' from the right one, it will create a bare subroutine for you to enter the code.

The 'target' parameter is the range of the cell clicked, so if your code was reliant on a1, you could use

if target.address="$A$1" then
  'code here
end if

You can show a userform by calling it's name with a .show on the end

so if your form is called 'userform1' then

userform1.show

HTH

 

[roi santos]

Many thanks for this. It is working fine.

 

[Blackstockc]

If you right click on the tab of the worksheet where the cell lives, and choose 'view code', you can create a macro which will activate whenever a specific event occurs on the sheet.

If you select 'worksheet' from the left listbox and either 'beforerightclick' or 'beforedoubleclick' from the right one, it will create a bare subroutine for you to enter the code.

The 'target' parameter is the range of the cell clicked, so if your code was reliant on a1, you could use

if target.address="$A$1" then
  'code here
end if

You can show a userform by calling it's name with a .show on the end

so if your form is called 'userform1' then

userform1.show

HTH

Hi there,

I have encountered an error when I try and run this Macro. Everytime I complete this code it pops up a macro selection, and asked me to select a marco.

I assume I have to select the marco I'm trying to attach to the cell. after I select it it codes the cell correctly but does not work for any other cells when the cells (in target) are engaged.

Please help!

Thanks

 

[pat211]

Hi,

This solution is part of what I am looking for, but here some additional needs.

my worksheets are increasing while adding data all the time, therefore I have this code increasing as well, as I need to test the cells on each row like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Address = "$E$3" Then
 
    Call View_AMS
  End If
  If Target.Address = "$E$4" Then
  
    Call View_BAR
  End If
If Target.Address = "$E$5" Then
Call View_BRU
End If
If Target.Address = "$E$6" Then
Call View_BSL
End If
If Target.Address = "$E$7" Then
Call View_CRI_1
End If

I would like to "test" the cells for their content like:

Dim site As String
site = ActiveCell

Once I have the variable (site) I would like to use it in my cell's code for following:

1) In replacement of the Target.Adress = "$e$3" <=
2) for the Call to my sub View_AMS <=

Hope to be clear enough with this request.

TYIA
patrick

 

[pat211]

Well,

While trying and testing I found this way which does almost what I need, with one "bug" ;o) it reacts ALL the time on ALL cells from the worksheet.
Is there a way to limit only to 1 column?

Here the code I now have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim site As String
site = ActiveCell
 
Dim pro As String
pro = "view_" + site
Application.Run pro
 
End Sub

 

[FormR]

it reacts ALL the time on ALL cells from the worksheet.
Is there a way to limit only to 1 column?

You could try something like..

If Target.Column = 5 And Target.Count = 1 Then
    'your code
    'your code
End If

 

[pat211]

Perfect thnx.

In the meanwhile I solved this way:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Column = 4 Then
 Dim site As String
 site = ActiveCell
 
 Sheets(site).Select
 End If
 
End Sub

Now my new challenge is to put this code "On Hold" just for the time to add some data to a cell that belongs to column "D" without the code to be executed.

Thank you again for any help
Patrick

 

[FormR]

Thank you again for any help

You could try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 4 And Target.Cells(1, 1).Value <> "" Then
    Sheets(Target.Cells(1, 1).Value).Select
End If
End Sub