Solved: Double click on Cell

[Aussiebear]

Is it possible to double click on a cell and bring up a subform with greater detail than the cell initialy indicated?

For example.

Cell might indicate " Going Fishing with Bob"

Double clicking might bring up a subform which shows;

01/02/06 Rang Tom and arranged booking for boat
05/02/06 Terry rang and said he's good for two days only
07/02/06 Steve wants the double bunk room- (bringing girlfriend)
07/02/06 Trevor is bringing the beer
09/02/06 Bob rang to say motor is installed in boat and sound s good.

[lucas]

This is possible but why not use cell comments?

[Killian]

Well it's certainly possible... you can use the Worksheet_BeforeDoubleClick event to raise the form (the Target argument will provide the cell you clicked).
But where is the information for the subform held (another sheet, hidden rows/cols)?

[Aussiebear]

Steve, That's possible but it could be an ongoing thing. Add one line today, another in two days time. I was thinking more along the concept of an electronic diary but using the cell to simply display the "title"/ brief note and then when the cell has the focus , you could simply double click to bring up a subform in which the greater detail could be stored.

[Aussiebear]

Well it's certainly possible... you can use the Worksheet_BeforeDoubleClick event to raise the form (the Target argument will provide the cell you clicked).
But where is the information for the subform held (another sheet, hidden rows/cols)?

Could it be hidden on another sheet? ( Hide the sheet only).

[lucas]

This is rough but it will give you an idea of whats involved...

[Aussiebear]

Very close Steve, except I'd like the ability to add information to the subform, such as;
"20/03/06, Steve rang to say Motor has been checked over" then five days later add;
"25/03/06, Latest weather report indicates rain. Rang guys and said to postpone for 3 days"

BTW. Could this concept be made appplicable for a range of cells or is it limited to just a particular cell?

Ted

[Aussiebear]

For example, would like the option to add things like this.

[malik641]

Steve, That's possible but it could be an ongoing thing. Add one line today, another in two days time. What's wrong with that? You could double click the cell and the comment could show, then double click again to make the comment hide....and you can add more and more text to comments as well (and size them too).

I like the comment idea, and on a side note if you didn't want the info just delete the cell (not clear contents) and there goes the info with it (or clear comment and start over if you had to), rather than searching for the original info and manually deleting it. It also moves with the comment if the cell is moved.

You could double click and have an input box show up and the user could enter the info and it would be stored with the comment (and add on to existing text)

Also, this saves cell space on worksheets.

Just my 2 pennies. :)

[Aussiebear]

Joseph, Should this concept be made to work, I then wanted to adapt it for the following purpose.

As you will aware I have another thread which Steve greatly assisted with a file called Electrical Work. In which a number of supervisors all have the ability to enter a work request, but only the Maintenance supervisor will have the option of entering the sub form data.

This subform data forms the work history for each work item and needs to be saved as a history of the job should anything need to be re- examined.

Since I've been promoting this website at work as a terrific source of ideas and assistance, I'd rather, that should any of them wander on here, I don't fall foul of their policy of not discussing any work cncepts outside of work, hence the round about method of coming to the solution.

Ted

[Norie]

Ted

Why not just use Access?

[Aussiebear]

I may have to yet.

[malik641]

Joseph, Should this concept be made to work, I then wanted to adapt it for the following purpose.

As you will aware I have another thread which Steve greatly assisted with a file called Electrical Work. In which a number of supervisors all have the ability to enter a work request, but only the Maintenance supervisor will have the option of entering the sub form data.

This subform data forms the work history for each work item and needs to be saved as a history of the job should anything need to be re- examined.

Since I've been promoting this website at work as a terrific source of ideas and assistance, I'd rather, that should any of them wander on here, I don't fall foul of their policy of not discussing any work cncepts outside of work, hence the round about method of coming to the solution.

Ted I'll look into that thread, but maybe you can incorporate the following code for what you want. It's a simple example of what I would do.

And you can always PM me if you need anything :yes So feel free to do so.

Anyways here's the code and an example workbook. Just double click on any cell and you can add the info in there. This is placed in the sheet you are working with (my case, sheet1):

Option Explicit
Public oldRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
Dim rng As Range
Set rng = Target(1, 1)

oldRange.Comment.Visible = False

With rng
If Not .Comment Is Nothing Then
If .Comment.Visible = False Then
.Comment.Visible = True
Else
.Comment.Visible = False
End If
End If
End With

Set oldRange = Target(1, 1)
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
On Error Resume Next
Dim cmtText As String
Dim inputText As String

If Target.Comment Is Nothing Then
cmtText = InputBox("Enter info:", "Comment Info")
If cmtText = "" Then Exit Sub
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True 'Remove if you want to size it yourself
Else
If Target.Comment.Text <> "" Then
inputText = InputBox("Enter info:", "Comment Info")
If inputText = "" Then Exit Sub
cmtText = Target.Comment.Text & Chr(10) & inputText
Else
cmtText = InputBox("Enter info:", "Comment Info")
End If
Target.ClearComments
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True 'Remove if you want to size it yourself
End If

Cancel = True 'Remove this if you want to enter text in the cell after you add the comment
End Sub
I hope this will suit your needs Ted :thumb

EDIT: I added some extra code.

[lucas]

Thats pretty slick Joseph....gonna have to yoink it.

[malik641]

Thats pretty slick Joseph....gonna have to yoink it. :cool: Thanks Steve

BTW, I sent this for a KB submission. Could you reset it to WIP for me? I have a different version (less handling than the one in this post) and would like to change it to the one in this post.

[lucas]

Done Joseph, let me know when you resubmit it in case I don't notice it.

[malik641]

Resubmitted :thumb

Thanks Steve

[Aussiebear]

Joseph, Thanks for the heads up on this one. I'll have a go at patching it into the origonal sheet when I get to work. Computer security at work is such that I can't email this myself, (apparently we have an auto email manager function which rejects any emails with linked files for anyone whose status is less the "God like", but the irony is that they allow me to fool around with vba.

BTW. if the active cell was in column D would I change the line to "Set rng = Target(4,1)" & "Set oldRange = Target(4,1)", and can I enter a message into the inputBox " Enter a Date, then the info" as a method of keeping the hint about the preferred method of entering data.


Ted

[malik641]

BTW. if the active cell was in column D would I change the line to "Set rng = Target(4,1)" & "Set oldRange = Target(4,1)", and can I enter a message into the inputBox " Enter a Date, then the info" as a method of keeping the hint about the preferred method of entering data.


Ted About Target(4,1), that answer is no. You'll find that out when you work with it more. The reason for Target(1,1) is in the case of multiple selections on a sheet. If you selected a range larger than one cell, it would only work with the upper left cell. Now if the selection you choose is let's say A1:D1 then yes, you would do that, but it would only show cell D1's comment (if there was one) but why would you do that? And if you leave out the (1,1) and you select multiple cells that particular show method of comments wouldn't work as expected. Also, the reason I did not put Target(1,1) in the _BeforeDoubleClick event is because you cannot double click more than one cell at a time, so I don't need to put (1,1).

And yes, you could put whatever you want for the prompt of the input box. Whatever you think your users would find easiest to understand.

[Aussiebear]

And if you only wanted this option to be carried out in column C?

[malik641]

And if you only wanted this option to be carried out in column C?
Then just put a restriction on top of each event (or just the _BeforeDoubleClick event).

This is what the beginning of each sub should look like:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
If Target(1, 1).Column <> 4 Then
oldRange.Comment.Visible = False
Exit Sub
End If
And...
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Column <> 4 Then Exit Sub
Is that what you were looking for? :)

[Aussiebear]

:banghead: aaagggghhhh.

So near, and yet so far away.

The comments should be able to disappear once that cell in Column C loses focus, but I've done something wrong here.

I am hoping that comments can only be added after clicking a cell in column C but dont understand the request by Joseph.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
If Target(1,1).Column <>3 Then
oldRange.Comment.Visible = False
Exit Sub
End If


Does the "Set rng = Target(1,1)" that was in the inital code still apply as the next line in the code?

Ted

[malik641]

Ted

I don't have much time to reply, but here's the code in its entirety:

Option Explicit
Public oldRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
If Target(1, 1).Column <> 3 Then
oldRange.Comment.Visible = False
Exit Sub
End If
Dim rng As Range
Set rng = Target(1, 1)

oldRange.Comment.Visible = False

With rng
If Not .Comment Is Nothing Then
If .Comment.Visible = False Then
.Comment.Visible = True
Else
.Comment.Visible = False
End If
End If
End With

Set oldRange = Target(1, 1)
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Column <> 3 Then Exit Sub
On Error Resume Next
Dim cmtText As String
Dim inputText As String

If Target.Comment Is Nothing Then
cmtText = InputBox("Enter info:", "Comment Info")
If cmtText = "" Then Exit Sub
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True 'Remove if you want to size it yourself
Else
If Target.Comment.Text <> "" Then
inputText = InputBox("Enter info:", "Comment Info")
If inputText = "" Then Exit Sub
cmtText = Target.Comment.Text & Chr(10) & inputText
Else
cmtText = InputBox("Enter info:", "Comment Info")
End If
Target.ClearComments
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True 'Remove if you want to size it yourself
End If

Cancel = True 'Remove this if you want to enter text in the cell after you add the comment
End Sub

Hope this works for ya

[mdmackillop]

Just came across this one.
How about using notepad?
For this example add some textfiles Text1.txt, text2.txt etc to a folder C:\AAA. Add the code below to the Worksheet module. The code will open the file in notepad according to the row number. (Check your notepads location if required).


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Column <> 4 Then Exit Sub
Shell "C:\Windows\System32\Notepad.exe C:\AAA\Text" & Target.Row & ".txt", vbNormalNoFocus
Application.SendKeys "{Esc}"
End Sub