Custom Dynamic Tool Tips Excel Userform
Results 1 to 5 of 5

Thread: Custom Dynamic Tool Tips Excel Userform
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2016
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Custom Dynamic Tool Tips Excel Userform

    So, I already know how to add a control tip to buttons in userforms, but I need to have it pull info from a particular cell instead, and change when the cell changes. I do not want to have to click the button to show a message box, i already know how to do that. Also side note, any code I will not be able to just copy and paste from here, as the code will need to be manually typed into a 2nd system that is a secured system. So, if you guys can provide a shorter/condense version of the code that would be great. Don't want to be typing 2 pages of info manually. lol.

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,964
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Custom Dynamic Tool Tips Excel Userform

    When the userform is loaded how do you change the cell in question?
    Let's say you have a line of code somewhere (in the userform module) to change the cell value then you can put below it a code like this:

    Code:
    CommandButton1.ControlTipText = Sheets("Sheet1").Range("A1")

  3. #3
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,315
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Custom Dynamic Tool Tips Excel Userform

    This assumes that the Userform is UserForm1, the commandbutton is CommandButton1 and the Target cell is Cell Sheet1!A1 ... Change the latters as required.


    Place the code in the worksheet module :
    Code:
    Option Explicit
    
    Private Const TARGET_CELL As String = "Sheet1!A1"
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Not Intersect(Target, Range(TARGET_CELL)) Is Nothing Then
            If IsFormLoaded("UserForm1") Then
                UserForm1.CommandButton1.ControlTipText = Range(TARGET_CELL)
            End If
        End If
        
    End Sub
    
    
    Private Function IsFormLoaded(ByVal UserFormName As String) As Boolean
    
        Dim oForm As Object
        
        For Each oForm In VBA.UserForms
            If oForm.Name = UserFormName Then
                IsFormLoaded = True
                Exit Function
            End If
        Next
    
    End Function
    And place this in the UserForm Module:

    Code:
    Private Sub UserForm_Initialize()
        Me.CommandButton1.ControlTipText = Sheet1.Range("A1")
    End Sub
    Last edited by Jaafar Tribak; Aug 17th, 2019 at 01:09 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  4. #4
    New Member
    Join Date
    Apr 2016
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom Dynamic Tool Tips Excel Userform

    Actually ended up finding your idea, but changed it a little formname.CommandButton1.ControlTipText = Sheets("Sheet1").Range("A1") and put that in the sheet code overall and for it to run it any time a change occurs on said sheet.

    Quote Originally Posted by Akuini View Post
    When the userform is loaded how do you change the cell in question?
    Let's say you have a line of code somewhere (in the userform module) to change the cell value then you can put below it a code like this:

    Code:
    CommandButton1.ControlTipText = Sheets("Sheet1").Range("A1")

  5. #5
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,315
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Custom Dynamic Tool Tips Excel Userform

    Or you could keep the entire code inside the userform module which I think is a better approach... Something along these lines:

    In the UserForm Module:
    Code:
    Option Explicit
    
    Private WithEvents ws As Worksheet
    Private Const TARGET_CELL As String = "Sheet1!A1"
    
    Private Sub UserForm_Initialize()
        Set ws = Range(TARGET_CELL).Parent
        Me.CommandButton1.ControlTipText = Range(TARGET_CELL)
    End Sub
    
    Private Sub ws_Change(ByVal Target As Range)
        If Not Intersect(Target, Range(TARGET_CELL)) Is Nothing Then
            Me.CommandButton1.ControlTipText = Range(TARGET_CELL)
        End If
    End Sub
    Last edited by Jaafar Tribak; Aug 17th, 2019 at 01:19 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •