Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Viveka Fischer   on Dec 06 In MS Office Category.

  
Question Answered By: Akina Suzuki   on Dec 06

Absolutely.
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
selected.

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)
Else
Result = InputBox("Enter Value:", "New Value in Cell:" & _
Target.Address)
End If
Target.Value = Result
End If
End Sub

Share: 

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


Tagged: