How to Act on a Mouse Click

[dhatul]

I want to write code for picking a default folder where the data file to be imported is stored. This default path is stored in, say, Cell B1. Another similar path for another data file is stored in, say, cell B3. I have written the code for displaying a browse window for picking up the folder as under -

Please Login or Register  to view this content.

Now the help I need is to suggest a code so that when a user mouse clicks on cell B1, the above code is executed and the returned value is stored in Cell B1 and when he clicks on cell B3, the above code is executed and the returned value be stored in cell B3.

It will still be better if the mouse click and an 'Enter' key both can be identified to execute the above code.

[Marcol]

Try this

With the Sheet you want to execute your code.

Right-Click the Sheet Tab > Click View Code
Paste all of this in the resultant pane

Please Login or Register  to view this content.

Delete your original code. (It's included in the above code.)

Close the VBa editor and save the workbook.

Click in cell B1 or B3 to run your code.

Is that what you are after?

[dhatul]

Try this

Is that what you are after?

Thanks Marcol. This worked. Only a few further issues -

1. Since this traps a change event only, it does not take care of the following situation :
A user clicks on the cell and a browse windows pops up. He selects a folder, clicks 'Open/OK' and the returned path is stored in the cell. Suddenly he notices that he picked a wrong folder and clicks the mouse again (he is still in the same cell) to have the browse window again, but this time the window won't open. He will have to go to another cell and then come back to this cell to get the browse/pickup window again.

2. In my worksheet the cells B1,C1 & D1 were merged cells for visual effect. I had to split the merged cells for the function to work.

Any solution to overcome these issues?

[Marcol]

1/. Try this code instead
Change the offset if you have to, but best if it is an adjacent cell

Please Login or Register  to view this content.

2/. Avoid merging cells, merged cells cause more problems than they are worth.
In this case you could possibly use C1 centered, instead of B1, then format B1,C1,D1 to get the desired visual effect.

[dhatul]

1/. Try this code instead
Change the offset if you have to, but best if it is an adjacent cell

Thanks Marcol, It's a good workaround but it fails in case you exit from the folder selection window without selecting a folder i.e. if you select 'cancel'.

Any further suggestion?

[Marcol]

Hmmm....seems to work okay for me.

This modification will clear the cell if there is no folder selected, (might be handy if you need to delete the reference)

Please Login or Register  to view this content.

Have you made any other changes?

[dhatul]

Hmmm....seems to work okay for me.

This modification will clear the cell if there is no folder selected, (might be handy if you need to delete the reference)

Please Login or Register  to view this content.

Have you made any other changes?

Hi Marcol,

1. The cell will already have a default or previously stored path, which I do not want to clear, in case the user did not select a new one. The change suggested by you will clear the cell.

2. No I have not made any change other than that the offset selected is (0,-1).