MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Assign a macro to a cell?

  Asked By: Viveka    Date: Dec 06    Category: MS Office    Views: 1448

Currently, I have a macro which needs to user to enter the parameters through an
input box. Since there are many rows in the spreadsheet, the user often makes
mistakes while typing in the info. Is there a way I can assign the macro to the
cell, so that when the user clicks on that cell, it macro takes the information
thats in that cell & generate results?



1 Answer Found

Answer #1    Answered By: Akina Suzuki     Answered On: Dec 06

What you're looking for is one of the worksheet "events".
Right click on the tab and select "View Code".
The top pull-down probably will start out with "general".
Change it to "worksheet".
the default event is the SelectionChange event.
This sub runs whenever the user  selects a cell. This may not be the
best one to use. Look at the others in the pull-down on the right.
You may want to change this to the BeforeDoubleClick event.

Now, whenever the user double-clicks on a cell, it will run this sub
first. the event is passed the range object of the cell  double-
clicked. Target.Value will be the contents of the cell.
Target.row, Target.Column, Target.Address will all refer to the cell

Using "Cancel = true" will disable the "edit in cell" that is
normally the default when you double-click a cell.

The next trick is to make this run only when specific columns are
clicked, or when the cell has contents.
You can use:
If (target.value <> "") then

to execute only if the cell is non-blank.

If (Not Intersect(Range(Target.Address), Range("A1:V59")) _
Is Nothing) Then

will execute only if the cell selected is in the range A1-V59

Combining all of these you get:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim Result
If (Not Intersect(Range(Target.Address), Range("A1:V59")) _
Is Nothing) Then
Cancel = True
If (Target.Value <> "") Then
Result = InputBox("Change Value:", "Modify Cell: " & _
Target.Address, Target.Value)
Result = InputBox("Enter Value:", "New Value in Cell:" & _
End If
Target.Value = Result
End If
End Sub

Didn't find what you were looking for? Find more on Assign a macro to a cell? Or get search suggestion and latest updates.