run vba code when a value in a cell is clicked on

Sure. Add this code to the worksheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(ActiveCell, [A1]) Is Nothing Then
        'A1 selected...show something about A1
    End If

End Sub

Change the text "[A1]" to the cell you want to work with.

Kevin

To add VBA code to a worksheet or chart code module in an Excel workbook, right-click on the worksheet or chart tab at the bottom of the window and select View Code. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook. To find a worksheet or chart module when already in the VBE, press CTRL+R to open the VBE project explorer. Find the module in which the code will be placed - each worksheet and chart module is pre-assigned a name such as "Sheet1 (Sheet1)" where the name inside the parenthesis is the tab name. Double-click the desired module and paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

Kevin

what if i dont know which cell they will click on ie column b for depreciation or column c for depreciation
as per the sample below

Global Data Centers            
            
People Costs      (876,244)      (876,244)
            
Repairs & Maintenance      292,776      292,776
Depreciation                           393,885      393,885
Amortization                           0                    0
Software Lease & Maintenance      135,928      135,928
Business Process Outsourcing      48,862      48,862
Operating Supplies      0      0
Equip. Leasing      121,242      121,242
Telecoms - Mobile      0      0
Telecoms - Data      0      0
Admin Expense      233,382      233,382
Environmental Expense      0      0
Professional Consulting Fees      (64,875)      (64,875)
IT Contractor Expense      76,200      76,200
Charge In      (2,144,670)      (2,144,670)
Allocations In      129,377      129,377
Allocations Out      (861,359)      (861,359)
Charges Out      (330,431)      (330,431)
            
Total Other Costs      (1,969,682)      (1,969,682)
            
Global Data Centers      (2,845,926)      (2,845,926)

You need to decide which cells for which you want to display something, include them in the code, and then decide what you want to display when those cells are clicked.

Kevin

is there a mouseover command which could display a message box (a bit like comments)

Well, sort of. You can create a comment in each cell which will display when the mouse hovers.

Kevin

If you have the option to edit in cell turned on, then double-clicking the cell (or pressing f2) automatically highlights the input cells if that helps.

jpdaus,

This is the time of year for accountants to need routines to decide what might add up to a given figure. The attached file was designed to give both matches and close matches. If the macros don't work, try running the Solver manually.

Hope it helps

Patrick
Solver-automation-near-matches-0.xls

You may want to use the RIGHT-CLICK or DOUBLE-CLICK to fire your vba.
Use the BEFORERIGHTCLICK or BEFOREDOUBLECLICK event of the Worksheet.
These events, same as SelectChange event - will fire on EVERY CELL of the worksheet.
You must determine what cells are affected.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'----- rows 3 - 4 -----
If Target.Row > 2 And Target.Row < 5 Then
    '---- column C - D ------
    If Target.Column > 2 And Target.Column < 5 Then
        '--- do your code here
    End If
End If
End Sub

Scott C

I might have tried to do too much here, you know when you hover over a cell with a comment it shows the comment, well i want to do this but the comment will need to be calculated with a list of names and values.
If i ohave my mouse over b10, which has a valu of 100, a comment would put up and look like this

                      3207
52099PP           90
52046PP           10

Is this actually feasible

There is no mouse over event for cells (exposed to VBA anyway), so you would have to populate an actual comment at the time the cell is updated.

can this be done through vba then

Yes. All you need to do is provide the information on how to build the comment. Something like: "given a result cell, the text for the comment is ... from Sheet2!A1, ... from Sheet2!b2, ... "

Kevin

can u have a loop that add 2 lines ie

for 1 to x
if cell a1 <> 0
then cell a2 paste to comment
next line in comment
next for

i have tried this but get an 1004 error

For m = 17 To 28
    For n = 12 To 282
    If Worksheets("HYPBD-A").Cells(n, 6).Value <> 0 Then
        comtext = Worksheets("HYPBD-A").Cells(n, 1).Value & " "
    End If
    Next n

Worksheets("Branch").Cells(m, 2).AddComment comtext
Next m


it does not create a new line text text though

You are replacing the text each time - try:

For m = 17 To 28
    For n = 12 To 282
    If Worksheets("HYPBD-A").Cells(n, 6).Value <> 0 Then
        comtext = comtext & vbCrLf & Worksheets("HYPBD-A").Cells(n, 1).Value
    End If
    Next n

Worksheets("Branch").Cells(m, 2).AddComment comtext
Next m

Select allOpen in new window

I still get an application defined error 1004 is there something i haven't done

Thank you