Custom Dynamic Tool Tips Excel Userform

[Guard913]

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.

 

[Akuini]

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:

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

 

[Jaafar Tribak]

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 :

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:

Private Sub UserForm_Initialize()
    Me.CommandButton1.ControlTipText = Sheet1.Range("A1")
End Sub

 

[Guard913]

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.

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:

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

 

[Jaafar Tribak]

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:

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