Click cell to activate macro

I want to run a macro when the user clicks in a specific cell. In a
personal worksheet I put a button on the page and tied a macro to the
button. In that case there was only one button. In the present case, the
macro needs to do different things depending on what row is clicked.
Because someone else will be maintaining the workbook and because the data
will change frequently (with rows being added and deleted), I don't want to
put a button in each row.

My macro will determine its row, pull data from the same row in another
worksheet, and display the data in a text box. I tried using a hyperlink
from the source page to the data page, but the user didn't like that.

I'm pretty sure I can get the macro to figure out the row of the cell that
was clicked, so I really need to write only one macro. Is there a way I can
fire a macro when a user clicks in a cell?

BTW, I thought about having the user select a cell and then use the menus to
launch the macro, but the user wants something simpler.

Thanks,
Bob

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Row
Case 5: 'do something
Case 9: 'do something else
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob" <[email protected]> wrote in message
news:[email protected]...
> I want to run a macro when the user clicks in a specific cell. In a
> personal worksheet I put a button on the page and tied a macro to the
> button. In that case there was only one button. In the present case, the
> macro needs to do different things depending on what row is clicked.
> Because someone else will be maintaining the workbook and because the data
> will change frequently (with rows being added and deleted), I don't want
to
> put a button in each row.
>
> My macro will determine its row, pull data from the same row in another
> worksheet, and display the data in a text box. I tried using a hyperlink
> from the source page to the data page, but the user didn't like that.
>
> I'm pretty sure I can get the macro to figure out the row of the cell that
> was clicked, so I really need to write only one macro. Is there a way I
can
> fire a macro when a user clicks in a cell?
>
> BTW, I thought about having the user select a cell and then use the menus
to
> launch the macro, but the user wants something simpler.
>
> Thanks,
> Bob
>
>

A button needs only one click. A cell needs two. Put this in worksheet code:


Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Cancel = True
MsgBox (Selection.Address)
End Sub

--
Gary's Student


"Bob" wrote:

> I want to run a macro when the user clicks in a specific cell. In a
> personal worksheet I put a button on the page and tied a macro to the
> button. In that case there was only one button. In the present case, the
> macro needs to do different things depending on what row is clicked.
> Because someone else will be maintaining the workbook and because the data
> will change frequently (with rows being added and deleted), I don't want to
> put a button in each row.
>
> My macro will determine its row, pull data from the same row in another
> worksheet, and display the data in a text box. I tried using a hyperlink
> from the source page to the data page, but the user didn't like that.
>
> I'm pretty sure I can get the macro to figure out the row of the cell that
> was clicked, so I really need to write only one macro. Is there a way I can
> fire a macro when a user clicks in a cell?
>
> BTW, I thought about having the user select a cell and then use the menus to
> launch the macro, but the user wants something simpler.
>
> Thanks,
> Bob
>
>
>

Correction.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
Select Case .Row
Case 5: 'do something
Case 9: 'do something else
'etc.
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob" <[email protected]> wrote in message
news:[email protected]...
> I want to run a macro when the user clicks in a specific cell. In a
> personal worksheet I put a button on the page and tied a macro to the
> button. In that case there was only one button. In the present case, the
> macro needs to do different things depending on what row is clicked.
> Because someone else will be maintaining the workbook and because the data
> will change frequently (with rows being added and deleted), I don't want
to
> put a button in each row.
>
> My macro will determine its row, pull data from the same row in another
> worksheet, and display the data in a text box. I tried using a hyperlink
> from the source page to the data page, but the user didn't like that.
>
> I'm pretty sure I can get the macro to figure out the row of the cell that
> was clicked, so I really need to write only one macro. Is there a way I
can
> fire a macro when a user clicks in a cell?
>
> BTW, I thought about having the user select a cell and then use the menus
to
> launch the macro, but the user wants something simpler.
>
> Thanks,
> Bob
>
>

Bob,

I tried this by putting a message box in each case and found that I had to
double click the cell AND then move to another cell before the message
box poped up
Is there a way to do this without having to move to another cell?



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Row
Case 2: MsgBox "row 2"
Case 3: MsgBox "row 3"
Case 4: MsgBox "row 4"
Case 5: MsgBox "row 5"
Case 6: MsgBox "row 6"
Case 7: MsgBox "row 7"
Case 8: MsgBox "row 8"
Case 9: MsgBox "row 9"

End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
Billy Rogers

Dallas,TX


"Bob Phillips" wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "A1:H10"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> Select Case .Row
> Case 5: 'do something
> Case 9: 'do something else
> 'etc.
> End Select
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Bob" <[email protected]> wrote in message
> news:[email protected]...
> > I want to run a macro when the user clicks in a specific cell. In a
> > personal worksheet I put a button on the page and tied a macro to the
> > button. In that case there was only one button. In the present case, the
> > macro needs to do different things depending on what row is clicked.
> > Because someone else will be maintaining the workbook and because the data
> > will change frequently (with rows being added and deleted), I don't want
> to
> > put a button in each row.
> >
> > My macro will determine its row, pull data from the same row in another
> > worksheet, and display the data in a text box. I tried using a hyperlink
> > from the source page to the data page, but the user didn't like that.
> >
> > I'm pretty sure I can get the macro to figure out the row of the cell that
> > was clicked, so I really need to write only one macro. Is there a way I
> can
> > fire a macro when a user clicks in a cell?
> >
> > BTW, I thought about having the user select a cell and then use the menus
> to
> > launch the macro, but the user wants something simpler.
> >
> > Thanks,
> > Bob
> >
> >
>
>
>

See my other post.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"BillyRogers" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> I tried this by putting a message box in each case and found that I had
to
> double click the cell AND then move to another cell before the
message
> box poped up
> Is there a way to do this without having to move to another cell?
>
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "A1:H10"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> Select Case .Row
> Case 2: MsgBox "row 2"
> Case 3: MsgBox "row 3"
> Case 4: MsgBox "row 4"
> Case 5: MsgBox "row 5"
> Case 6: MsgBox "row 6"
> Case 7: MsgBox "row 7"
> Case 8: MsgBox "row 8"
> Case 9: MsgBox "row 9"
>
> End Select
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
> Billy Rogers
>
> Dallas,TX
>
>
> "Bob Phillips" wrote:
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Const WS_RANGE As String = "A1:H10"
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > With Target
> > Select Case .Row
> > Case 5: 'do something
> > Case 9: 'do something else
> > 'etc.
> > End Select
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Bob" <[email protected]> wrote in message
> > news:[email protected]...
> > > I want to run a macro when the user clicks in a specific cell. In a
> > > personal worksheet I put a button on the page and tied a macro to the
> > > button. In that case there was only one button. In the present case,
the
> > > macro needs to do different things depending on what row is clicked.
> > > Because someone else will be maintaining the workbook and because the
data
> > > will change frequently (with rows being added and deleted), I don't
want
> > to
> > > put a button in each row.
> > >
> > > My macro will determine its row, pull data from the same row in
another
> > > worksheet, and display the data in a text box. I tried using a
hyperlink
> > > from the source page to the data page, but the user didn't like that.
> > >
> > > I'm pretty sure I can get the macro to figure out the row of the cell
that
> > > was clicked, so I really need to write only one macro. Is there a way
I
> > can
> > > fire a macro when a user clicks in a cell?
> > >
> > > BTW, I thought about having the user select a cell and then use the
menus
> > to
> > > launch the macro, but the user wants something simpler.
> > >
> > > Thanks,
> > > Bob
> > >
> > >
> >
> >
> >

thanks bob that's great. Your correction showed up right after i made my post.
--
Billy Rogers

Dallas,TX


"Bob Phillips" wrote:

> See my other post.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "BillyRogers" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> >
> > I tried this by putting a message box in each case and found that I had
> to
> > double click the cell AND then move to another cell before the
> message
> > box poped up
> > Is there a way to do this without having to move to another cell?
> >
> >
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Const WS_RANGE As String = "A1:H10"
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > With Target
> > Select Case .Row
> > Case 2: MsgBox "row 2"
> > Case 3: MsgBox "row 3"
> > Case 4: MsgBox "row 4"
> > Case 5: MsgBox "row 5"
> > Case 6: MsgBox "row 6"
> > Case 7: MsgBox "row 7"
> > Case 8: MsgBox "row 8"
> > Case 9: MsgBox "row 9"
> >
> > End Select
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> > Billy Rogers
> >
> > Dallas,TX
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Const WS_RANGE As String = "A1:H10"
> > >
> > > On Error GoTo ws_exit:
> > > Application.EnableEvents = False
> > > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > > With Target
> > > Select Case .Row
> > > Case 5: 'do something
> > > Case 9: 'do something else
> > > 'etc.
> > > End Select
> > > End With
> > > End If
> > >
> > > ws_exit:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > 'This is worksheet event code, which means that it needs to be
> > > 'placed in the appropriate worksheet code module, not a standard
> > > 'code module. To do this, right-click on the sheet tab, select
> > > 'the View Code option from the menu, and paste the code in.
> > >
> > >
> > >
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Bob" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I want to run a macro when the user clicks in a specific cell. In a
> > > > personal worksheet I put a button on the page and tied a macro to the
> > > > button. In that case there was only one button. In the present case,
> the
> > > > macro needs to do different things depending on what row is clicked.
> > > > Because someone else will be maintaining the workbook and because the
> data
> > > > will change frequently (with rows being added and deleted), I don't
> want
> > > to
> > > > put a button in each row.
> > > >
> > > > My macro will determine its row, pull data from the same row in
> another
> > > > worksheet, and display the data in a text box. I tried using a
> hyperlink
> > > > from the source page to the data page, but the user didn't like that.
> > > >
> > > > I'm pretty sure I can get the macro to figure out the row of the cell
> that
> > > > was clicked, so I really need to write only one macro. Is there a way
> I
> > > can
> > > > fire a macro when a user clicks in a cell?
> > > >
> > > > BTW, I thought about having the user select a cell and then use the
> menus
> > > to
> > > > launch the macro, but the user wants something simpler.
> > > >
> > > > Thanks,
> > > > Bob
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Thanks, Bob and Gary's student, for the help. The worksheet events held the
key and I was able to get the macro working. For the edification of those
following this thread, I'm posting the working code below. Needless to say,
as soon as I had the code working the requirements changed, so I'll start by
describing what the macro does.

We have a project-tracking workbook whose main sheet was difficult to read
because three columns ("Project Team", "Key Dates", "Key Information"
(columns K-M (11-14))) have large lists in their respective cells. This
means that the rows were very deep to accommodate the lists. The user
wanted to have one- or two-line rows, with the list information accessible
only if needed. My solution was to put the list information in a separate
sheet (named "Info") and "link" to it from the main sheet. The three
information columns on the Info sheet were "C", "D", and "E"; the rows were
the same as on the main sheet. When the user clicks on the appropriate cell
in the main sheet, the related information pops up in a message box.
Because the macro fires for every cell change, the code displays the message
box only if the target cell is in the appropriate column and if the related
information cell is not blank: Here's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strInfoCol As String
Dim strInfo As String
Dim strBoxTitle As String
On Error GoTo ws_exit:
Application.EnableEvents = False
Cancel = True 'Don't rightly know why this is here, but it was in the
example, so I left it!
If Target.Column >= 11 And Target.Column <= 13 Then 'Make sure the user
clicks in one of the three info columns.
Select Case Target.Column
Case 11:
strInfoCol = "c" ' "c" is the related column on the "Info" sheet.
strBoxTitle = "Project Team"
Case 12:
strInfoCol = "d"
strBoxTitle = "Key Project Dates"
Case 13:
strInfoCol = "e"
strBoxTitle = "Project Information" 'Yeah, I know, I should have a
"Case Else". Bad programmer!
End Select
strInfo = Worksheets("Info").Range(strInfoCol & Target.Row).Value
If strInfo <> "" Then Call MsgBox(strInfo, vbOKOnly, strBoxTitle)
'Don't display message box if the info cell is blank.
End If
ws_exit:
Application.EnableEvents = True
End Sub

BTW, I used the following formula in the three "link" columns on the main
sheet (this example for the "Team" column, row 24:

=IF(Info!C24<>"","Team","")

As the Info sheet is updated, "Team", "Date", or "Info" appears on the main
sheet only if there is related data on the Info sheet. This gives the user
a visual cue of where to click (and not click!)

The code above is as not as elegant as it could be, but it's rather simple
and, more importantly, it works! Thanks, again, for the help.

Bob

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Correction.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> With Target
> Select Case .Row
> Case 5: 'do something
> Case 9: 'do something else
> 'etc.
> End Select
> End With
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Bob" <[email protected]> wrote in message
> news:[email protected]...
>> I want to run a macro when the user clicks in a specific cell. In a
>> personal worksheet I put a button on the page and tied a macro to the
>> button. In that case there was only one button. In the present case,
>> the
>> macro needs to do different things depending on what row is clicked.
>> Because someone else will be maintaining the workbook and because the
>> data
>> will change frequently (with rows being added and deleted), I don't want
> to
>> put a button in each row.
>>
>> My macro will determine its row, pull data from the same row in another
>> worksheet, and display the data in a text box. I tried using a hyperlink
>> from the source page to the data page, but the user didn't like that.
>>
>> I'm pretty sure I can get the macro to figure out the row of the cell
>> that
>> was clicked, so I really need to write only one macro. Is there a way I
> can
>> fire a macro when a user clicks in a cell?
>>
>> BTW, I thought about having the user select a cell and then use the menus
> to
>> launch the macro, but the user wants something simpler.
>>
>> Thanks,
>> Bob
>>
>>
>
>