#1
|
|||
|
|||
Allow User to Define Tooltip Upon Data Entry
Hi all, I am hoping for some direction rather than a full solution here so that I can try to research it myself. I'm just having trouble with where to look for info. So I have rows full of cells where the user chooses an integer from 1 to 32. These represent work units (each unit = 15 minutes). The user is logging how much time was spent on various tasks each day. The user performs work throughout the day and continually logs the units (2 here, 1 there, 4 there, etc.), working from left to right. I have the daily and monthly totals being autocalculated. However, it is easy to forget which units correspond to work with which clients (every unit is tied to a client). A user might not remember if s/he entered units earlier in the day and accidentally enter those units twice or not at all. It would be wonderful if I could allow the user to be prompted to enter a tooltip where they could enter the client's initials that go with each unit entry. So each cell still displays an integer, but when the user hovers over it, the tooltip shows the client's initials as a memory recall tool. The user would have to be prompted to enter those initials after selecting the integer unit from my existing dropdown I suppose. I could make separate columns of course, but then I will have a whole lot of columns, and I try not to make users scroll horizontally whenever possible. That's why I was thinking of a tooltip. Any guidance on what topics I should be researching? I want to put the effort in myself of course, but I'm just not sure where to start. |
#2
|
|||
|
|||
This resource is one method. Look at example answer #1 / Edit #2 about middle of
page : Display Excel VBA message box when a cell within a range changes AND meets a given criteria - Stack Overflow Let me know if you need assistance. |
#3
|
||||
|
||||
Try entering data into the light blue shaded area in the attached.
It needs a cell to be selected rather than hovered over. |
#4
|
|||
|
|||
Wow, you guys are awesome. That is so great!
|
#5
|
|||
|
|||
Hi all,
Is there a way to add a condition into the VBA so that, if the cell becomes blank, it will remove the previous text that was entered into the tooltip (but not the tooltip validation prompt)? Maybe something like the following, but I'm not sure what to put for the "then" portion to delete the tooltip contents without removing the tooltip validation prompt for the next time the cell changes. Code:
If Target.value = "" Then End If |
#6
|
||||
|
||||
Not clear.
Is this so that when you delete the value in the cell nothing appears anymore when it's selected, but if some new value is entered into the same cell, the old prompt re-appears with the same text as last time? |
#7
|
|||
|
|||
Sorry about that p45cal (and thank you again for your assistance). Your code is working out great by the way, and the way you set it up (to always prompt the user to enter a note upon any change in value to a cell) is quite helpful. I'd like to retain everything you have done but add one condition, as follows:
-always show the prompt to enter a note any time a cell value changes (already in your code) -retain the previous note until user types over it (already in your code or maybe just default) -one exception to both of the above is if the user's update to a cell is to delete a previously entered value (e.g., 2) by hitting the delete key. In that case (i.e., any time a cell update results in a blank cell), it would be helpful if the code would automatically clear any previously entered note as well, but retain the prompt for any further data entry. Our users will find that helpful for reasons that probably won't be clear without a long explanation. It is true that, when the user hits the delete key, they could (with only one more key stroke), hit the delete key again after the prompt and clear off the existing note. However, if you want to delete say, 20 cells of data and all the accompanying notes, you have to go through one by one, delete the cell, and then delete the note. Note sure if I've clarified but hope so. |
#8
|
||||
|
||||
Not sure what you want but try replacing with:
Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim cll As Range Set cellsToProcess = Intersect(Target, Range("B2:J4")) If Not cellsToProcess Is Nothing Then For Each cll In cellsToProcess.Cells With cll.Validation If HasValidation(cll) Then If .Type = xlValidateInputOnly Then If Len(cll.Value) > 0 Then cll.Select .InputMessage = InputBox("Confirm/Edit customer ID for selected cell", , .InputMessage) .ShowInput = True If .InputMessage = "" Then .Delete Else .ShowInput = False End If End If Else If Len(cll.Value) > 0 Then .Add Type:=xlValidateInputOnly cll.Select .InputMessage = InputBox("Enter customer ID for selected cell") If .InputMessage = "" Then .Delete End If End If End With Next cll End If End Sub Last edited by p45cal; 06-11-2020 at 02:26 PM. |
#9
|
|||
|
|||
Thank you again. At first I thought this was what I was after (when I mass delete cells with previous notes, the notes appear to go away, but then after I enter a numeric value again, the old note reappears in the textbox. So I think the current code is removing the previous note from the visual tooltip but not from the textbox itself, at least not completely). I'd like to, if possible, clear off the previous note only if the cell becomes blank. An example of what I'm looking for in the hopes of clarifying:
Thank you again for your kind assistance! |
#10
|
|||
|
|||
I tried adding
Code:
.ShowInput = False Code:
If .InputMessage = "" Then .Delete Now I'm thinking maybe Code:
Selection.Validation.Delete I had been searching for vba related to tool tips and it took me a while to realize that we are borrowing the text from the data validation for this macro Playing a bit on my own as well now that work day is finished. |
#11
|
||||
|
||||
Yes, in the code of my last message, try changing the one instance of:
.ShowInput = False to: .Delete |
#12
|
|||
|
|||
Awesome that did it, thank you!
|
#13
|
|||
|
|||
So to understand this a bit better for future reference, if you don't mind the question...
Before your last message I was beginning to think that I should be dealing more with the cell value rather than the input message. I was looking into maybe checking to see if the active cell was empty and, if so, then clearing the validation text. So now I'm curious why the "if clause" is asking whether the input message is blank rather than if the cell itself is blank? (are we treating the cell value as the input message in a way here?) Just a noob wondering... |
#14
|
||||
|
||||
the
If .InputMessage = "" Then .Delete is only there directly after the user has been asked to enter a new 'tooltip'; if the user removes/deletes/enters a blank tooltip it deletes the entire validation in that cell. The cell is checked earlier in the code for (not) being empty with: If Len(cll.Value) > 0 Then |
Tags |
define, multicolumn, tooltip |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel to be edited by multiple user but relevant user should see only his data | dolphine4u | Excel | 1 | 07-24-2016 12:40 AM |
Date/Time Formula for Entry level XL user | talon1driver | Excel | 2 | 09-18-2014 02:32 PM |
Insert input box into macro to allow user to define search term | Hoxton118 | Word VBA | 3 | 05-19-2014 02:03 AM |
User Form to Automate Entry in report | Panzer | Word VBA | 2 | 08-22-2012 04:17 AM |
define a mail merge data source using unc (universal naming convention) | charlesandrews | Mail Merge | 0 | 08-21-2012 10:33 AM |