Cell runs a command or macro

  • Thread starter Thread starter DR
  • Start date Start date
D

DR

I need to populate a cell to run a command, i.e., clicking on the cell runs
the command (or, failing that, runs a macro). In this instance, the command
I want to run is Go Back (this command is on the Web toolbar; it returns
from a bookmark to the location of the hyperlink that was used to go there;
I think it also returns from any insertion position to the previous one).
Can this be done? Simply using the toolbar for this will not be practical.
Thanks.
 
This macro will run another macro called MyMacro when you click on cell A1

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = Range("A1").Address Then
MyMacro
End If
End Sub

Try to record your macro first, give it a name and replace MyMacro with that
name
and replace A1 with the cell where you want this functionality
 
The OP should be warned that this code has to go in the sheet module, not
where he has his macro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks.
I have the macro RunGoBack, which says:
Sub RunGoBack()
' RunGoBack Macro
' Macro recorded 3/19/2004 by David Rees
' Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = Range("C:C").Address Then
GoBack
End If
End Sub

and which should run the macro GoBack (which works) when any cell in C is
clicked. It is not doing anything. On manual running, I am getting error
424 - object required.
Would appreciate your help.
Also, what is the syntax (I am new at Excel) for saying run RunGoBack only
when a cell in C of a particular sheet is clicked (the macro appears to
exist throughout the file, and I don't want the result from any sheet other
than one).
Thanks very much.
 
To clarify, it is the
If Target.Address = Range("A1").Address Then
line that is highlighted in debug.

Also, though it doesn't seem to be the problem, when I create the macro
GoBack, which is supposed to simply go back to the last cursor position, the
text of the macro supplies the actual anecdotal target, e.g.,
Application.Goto Reference:="'SHEET 2 - PRIMARY DATA'!R10C9"
I have tried this with and without the Reference=... portion, and can't
really tell which is correct, since it's the RunGoBack macro that produces
the error.
 
First of all, I forgot to mention that you have to right click the sheet tab
and select
view code and then paste in the macro. I would assume something like this
would work

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column <> 3 Then Exit Sub
Application.DisplayAlerts = False
RunGoBack
Application.DisplayAlerts = True
End Sub

Assuming your recorded macro is called RunGoBack
 
Maybe you could tell me in babytalk exactly what this reference to the sheet
module means, please. It appears per the VB screen that a copy of both
macros is in each sheet. Is that what you mean? Thanks.

The OP (what does OP mean?)
 
Start from scratch, keep the recorded macro RunGoBack
and delete any trace of the change event macro (to get into the macro editor
press F11). Now in the sheet where you want this to happen right click the
sheet tab (where the sheet name is)
or press Alt + F11 and double click the sheet name in the left hand side
project pane.

Paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column <> 3 Then Exit Sub
Application.DisplayAlerts = False
RunGoBack
Application.DisplayAlerts = True
End Sub


edit it to fit your needs in case the macro name of the recorded macro is
different.
Column 3 is column C <>2 means column B and so on.

Press Alt + Q to close the macro editor and save the workbook and test the
macro by clicking
in column C

OP means very strong vodka in Swedish but here it stands for original
Poster<g>
 
There are a number of different types of code module in VBA. Your macro will
(probably) be in a standard code module, which you create from
Insert>Module.

In addition there are various class modules associated with the workbook,
and with each worksheet. To get at the worksheet code modules, right-click
on the sheet tab, and select View Code from the menu. You can then enter the
code. Event code such as Peo gave you has to go in the workbook code module
or one of the sheet code modules (depending upon the event), else it will
not work.

OP means Original Poster.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
This works. Thanks.

Peo Sjoblom said:
First of all, I forgot to mention that you have to right click the sheet tab
and select
view code and then paste in the macro. I would assume something like this
would work

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column <> 3 Then Exit Sub
Application.DisplayAlerts = False
RunGoBack
Application.DisplayAlerts = True
End Sub

Assuming your recorded macro is called RunGoBack

--

Regards,

Peo Sjoblom


cell
Click to expand...
 
Well on second look it doesn't work. It runs on click but it also runs on
select. Would some syntax run on click only? Thanks.
 
How about double click?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column <> 3 Then Exit Sub
Application.DisplayAlerts = False
RunGoBack
Application.DisplayAlerts = True
End Sub
 
Change it to Worksheet_Change event.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
With "Cancel As Boolean" -- nothing happens
Without -- Procedure description does not match description of event or
procedure having the same name
 
This code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 3 Then Exit Sub
Application.DisplayAlerts = False
GoBack
Application.DisplayAlerts = True
End Sub

does not run the macro GoBack when a cell in column 3 is clicked (or
selected - which would be wrong). The macro GoBack runs manually from
anywhere in the file. The code of the macro does not appear at rightclick
on the sheet tab | view code. Is that relevant?
 
Is double-click okay? If so

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel
As Boolean)
If Target.Column <> 3 Then Exit Sub
Application.DisplayAlerts = False
GoBack
Application.DisplayAlerts = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
That will work. Thanks very much.

Bob Phillips said:
Is double-click okay? If so

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel
As Boolean)
If Target.Column <> 3 Then Exit Sub
Application.DisplayAlerts = False
GoBack
Application.DisplayAlerts = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top