Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-09-2019, 04:16 PM
PrincessApril PrincessApril is offline Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Advanced Beginner
Allow User to Define Tooltip Upon Data Entry
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 12-09-2019, 07:10 PM
Logit Logit is offline Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2007
Expert
 
Join Date: Jan 2017
Posts: 334
Logit will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 12-10-2019, 07:29 AM
p45cal p45cal is online now Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Expert
 
Join Date: Apr 2014
Posts: 411
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

Try entering data into the light blue shaded area in the attached.
It needs a cell to be selected rather than hovered over.
Attached Files
File Type: xlsm msofficeforums44017ToolTip on data entry.xlsm (17.3 KB, 4 views)
Reply With Quote
  #4  
Old 12-17-2019, 09:55 AM
PrincessApril PrincessApril is offline Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Advanced Beginner
Allow User to Define Tooltip Upon Data Entry
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Default

Wow, you guys are awesome. That is so great!
Reply With Quote
  #5  
Old 06-10-2020, 02:00 PM
PrincessApril PrincessApril is offline Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Advanced Beginner
Allow User to Define Tooltip Upon Data Entry
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 06-10-2020, 04:04 PM
p45cal p45cal is online now Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Expert
 
Join Date: Apr 2014
Posts: 411
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

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?
Reply With Quote
  #7  
Old 06-10-2020, 07:40 PM
PrincessApril PrincessApril is offline Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Advanced Beginner
Allow User to Define Tooltip Upon Data Entry
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 06-11-2020, 06:14 AM
p45cal p45cal is online now Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Expert
 
Join Date: Apr 2014
Posts: 411
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

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.
Reply With Quote
  #9  
Old 06-11-2020, 07:11 AM
PrincessApril PrincessApril is offline Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Advanced Beginner
Allow User to Define Tooltip Upon Data Entry
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Red face

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:
  • User enters some number (say 2) and is prompted to enter a note. (works in both iterations so far and should be retained)
  • User enters some note ("blah blah"). (works in both iterations so far and should be retained)
  • User deletes the numeric value and cell becomes blank.
  • The "blah blah" note would be cleared (not only from the visual tooltip but from the textbox itself, the one where the user initially entered the note).
  • User enters some value (say 3), and is prompted for a note, but the textbox is blank (user enters "some text")
  • User changes the 3 to a 4 (user still prompted to enter a note and the "some text" note is still there (works in both iterations so far and should be retained--the only exception is for when a user deletes a cell value and the resulting cell is blank, in which case note should be cleared from both visual tooltip and textbox field).

Thank you again for your kind assistance!
Reply With Quote
  #10  
Old 06-11-2020, 02:18 PM
PrincessApril PrincessApril is offline Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Advanced Beginner
Allow User to Define Tooltip Upon Data Entry
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Default

I tried adding
Code:
.ShowInput = False
after the final instance of
Code:
If .InputMessage = "" Then .Delete
but same issue noted above (I know I'm guessing hehe)

Now I'm thinking maybe
Code:
Selection.Validation.Delete
(but that seems to be geared toward clearing all parts of the validation, and I just want to clear the previous note if the cell becomes blank)

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.
Reply With Quote
  #11  
Old 06-11-2020, 02:24 PM
p45cal p45cal is online now Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Expert
 
Join Date: Apr 2014
Posts: 411
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

Yes, in the code of my last message, try changing the one instance of:
.ShowInput = False
to:
.Delete
Reply With Quote
  #12  
Old 06-11-2020, 02:44 PM
PrincessApril PrincessApril is offline Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Advanced Beginner
Allow User to Define Tooltip Upon Data Entry
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Default

Awesome that did it, thank you!
Reply With Quote
  #13  
Old 06-11-2020, 02:45 PM
PrincessApril PrincessApril is offline Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Advanced Beginner
Allow User to Define Tooltip Upon Data Entry
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Default

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...
Reply With Quote
  #14  
Old 06-11-2020, 03:41 PM
p45cal p45cal is online now Allow User to Define Tooltip Upon Data Entry Windows 10 Allow User to Define Tooltip Upon Data Entry Office 2019
Expert
 
Join Date: Apr 2014
Posts: 411
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

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
Reply With Quote
Reply

Tags
define, multicolumn, tooltip

Thread Tools
Display Modes


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
Allow User to Define Tooltip Upon Data Entry Insert input box into macro to allow user to define search term Hoxton118 Word VBA 3 05-19-2014 02:03 AM
Allow User to Define Tooltip Upon Data Entry 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 01:59 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft